I need help expanding the =If function capability...maybe

[rangeralex]

Hello All,
I’m trying to do something more complicated, but this is essentially what I am stuck on. Let’s say we have Four cells where A1=100 (Option 1); B1=200 (Option 2); C1=300 (Option 3); D1=400 (Option 4). In a different cell; let’s call it A2, I have a drop down list with “Option 1, Option 2, Option 3, and Option 4” listed. Finally, I have a cell that is used for what I hope to be the “result” cell, let’s dub this one B2. What I am trying to accomplish is by selecting “Option 3” from the drop down menu in cell A2, the result of 300 will populate automatically in the results cell, B2. Make sense?

I have tried using =vlookup and the =if function, but the problem I am having is there is no way to tell excel to try a different option if the one given doesn’t work. Any suggestions?

Thanks

 

[MistaCy]

Cell A1 = 1
Cell A2 = 2
Cell A3 = 3
Cell A4 = 4

Cell B1 = 100
Cell B2 = 200
Cell B3 = 300
Cell B4 = 400

Create a combo box and for the input range, choose A1:A4 and Cell Link should be D2

In cell E2 place this:

=LOOKUP(D2,A1:A4,B1:B4)

When you select a number, it will reference the cell next to it.

There is also a way to make the numbers appear with the word "Option" in front of it if you change the cell formatting around a little bit.

 

[dermie_72]

in B2 try:
=if(A2="Option 1",A1,if(A2="Option 2",B1,if(A2="Option 3",C1,D1)))

That way if it doesn't meet any criteria, it will default to option 4.

 

[MistaCy]

Or, you can do it with an if statement.

=If(A2="Option 1",100,If(A2="Option 2",200,If(A2="Option 3",300,If(A2="Option 4",400,))))

 

[Teeroy]

You could try:

B2: =LOOKUP(A2,{"Option 1",100;"Option 2",200;"Option 3",300;"Option 4",400})

 

[mikerickson]

or
=INDEX(A1:D1, 1, Right(A2,1))