What is an alternative to nested IF statements?
 

Consider two rows of cells. The cells in the first row have names picked
from a validation drop down list. I'd like each of the names to correspond
to a specific number which needs to populate in the cell below the name. So
if I pick cell A1 to be John, A2 should be 6. If I pick B1 to be Sally, B2
should be 89. I'd like to create a table somewhere that lets me list the
names with their respective values.

I know I can write something like...
If(A1 = "john", 6)
....but how does one accomplish this with 20 or so names?

Thank you!

What is an alternative to nested IF statements?
 

how does one accomplish this with 20 or so names?

The best approach is to create a 2 column table with the names in the left
column and the corresponding number in the right column. Like this:

...........G..........H.....
1......John........6.....
2......Sally........89...
3......Tom........40...
4......Lisa.........33...

Then, you would use a formula like this in A2:

=IF(A1="","",VLOOKUP(A1,$G$1:$H$4,2,0))

Then you can copy the formula across the row as needed.


--
Biff
Microsoft Excel MVP


"Paul" wrote in message
...
Consider two rows of cells. The cells in the first row have names picked
from a validation drop down list. I'd like each of the names to
correspond
to a specific number which needs to populate in the cell below the name.
So
if I pick cell A1 to be John, A2 should be 6. If I pick B1 to be Sally,
B2
should be 89. I'd like to create a table somewhere that lets me list the
names with their respective values.

I know I can write something like...
If(A1 = "john", 6)
...but how does one accomplish this with 20 or so names?

Thank you!