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

rangeralex

New Member
Joined
Jan 3, 2013
Messages
20
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


 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

MistaCy

New Member
Joined
Jan 9, 2014
Messages
10
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

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
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

New Member
Joined
Jan 9, 2014
Messages
10
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

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
You could try:

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

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top