excel - using if function with multiple possibilities

Hi guys

I have a cell, say A1, where I can have various values. I need to place a value on say A2 depending on the value of A1.
I can have multiple values, say up to 4. How can I handle this without using VBA (not permitted) ?

Example:
A1 "red" then A2 "choice 1"
A1 "blue" then A2 "choice 2"
A1 "green" then A2 "choice 3"
A1 "white" then A2 "choice 4"
A1 "not a color" then A2 "not applicable"

How would I do that ? I could use either a function or some sort of conditional input  (not sure this exists)...

Thanks all in advance.
LVL 2
ferraristaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
By function do you mean a worksheet function or a user-defined function?

If it's the latter then it would mean using VBA.
0
ferraristaAuthor Commented:
Sorry I mean a worksheet function, you know, an excel function, where you enter the equal sign and then the function you want to use, like a vlookup, a sum and so on......

Thanks.
0
Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
You could use a bunch of if functions:
=if(a1="red","choice 1",if(a1="blue","choice2", etc...

To make a more clean function, set up a table with your colours in the first column and choices in the second. Use a vlookup function to refer to the table.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ferraristaAuthor Commented:
Got it. Can I ask something more.

What if I want to combine the input of two cells ?

Example
A1 RED, A2 GREEN --> A3 RG
A1 BLUE, A2 GREEN --> A3 BG

How could I do that ?

Thanks so much for your help!
0
NorieVBA ExpertCommented:
For that you could use this.

=LEFT(A1)&LEFT(A2)
0
ferraristaAuthor Commented:
Sorrie Norie I don't get it. Where do you write the output ?
0
Garfield SamuelsProject ManagerCommented:
If you want anything other that the colors listed to be "not applicable" you can use:
=IF(A1="red", "choice 1",  IF(A1="blue", "choice2", IF(A1="green", "choice3",  IF(A1="white", "choice 4",  "not applicable"))))

if you want exactly "not a color" to result in "not applicable" then use:

=IF(A1="red", "choice 1",  IF(A1="blue", "choice2", IF(A1="green", "choice3",  IF(A1="white", "choice 4", IF(A1="not a color", "choice 4",  "not applicable")))))

Hope this helps.
0
ferraristaAuthor Commented:
Great, thanks!
0
Dustin SaundersDirector of OperationsCommented:
Yes, nested IF statements will handle that without VBA.  It can get real....  hard to manage...  in that tiny unformatted formula box....

What you might do is create another sheet that will be your statement builder so it's easier to manage.  Heres an example of how to set something like that up to generate the text for you to copy and get this:
IF(A1="red","choice1",IF(A1="blue","choice2",IF(A1="green","choice3",IF(A1="white","choice4","not applicable"))))

Open in new window

ifGenerator.xlsx
0
Garfield SamuelsProject ManagerCommented:
If you want exactly "not a color" to result in "not applicable" then use:

=IF(A1="red", "choice 1",  IF(A1="blue", "choice2", IF(A1="green", "choice3",  IF(A1="white", "choice 4", IF(A1="not a color", "not applicable","")))))
0
ferraristaAuthor Commented:
Thanks Garfield and Dustin, and Norie.
0
ShumsDistinguished Expert - 2017Commented:
Hi,

As per your quote:
What if I want to combine the input of two cells ?

Example
A1 RED, A2 GREEN --> A3 RG
A1 BLUE, A2 GREEN --> A3 BG

How could I do that ?

Try below in A3, you can add 7 more combination you like:
=IF(AND(A1="Red",A2="Green"),"RG",IF(AND(A1="Blue",A2="Green"),"BG","Not Applicable"))

Open in new window

0
Zeth LarssonCommented:
You can use the VLOOKUP - function

Let's say you have your colors in cell A1, and want the result i cell B1
Then you need to set up a matrix like this somewhere
As example I use this range of cells.
K10 = red, L10 = Choice 1
K11 = blue, L11 = Choice 2
----
Kxx = Not a color Lxx = Not applicable

Then in cell B2 you set the formula
=VLOOKUP(A1,K10:Lxx,FALSE)

Then I hope you have what you need!

Look at this video https://wus-streaming-video-rt-microsoft-com.akamaized.net/9c620fe5-d456-4416-aa80-b15d5171ad26/868190a7-8b9d-44df-8ceb-86170dc5_1024x576_2990.mp4

Regards /Zeth
0
Rob HensonFinance AnalystCommented:
Zeth,

You missed a parameter from the VLOOKUP formula; the column offset value. It should be:

=VLOOKUP(A1,K10:Lxx,2,FALSE)
0
Zeth LarssonCommented:
Thanks Rob
There you earn the points :< )
I can not copy & paste formulas from my XL.
This function i swedish is =LETARAD(A1;K10:Lxx;2;FALSKT)   which can be translated as SEARCHROW(...
But by cowork I suppose ferrarista now got what he needs.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.