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!

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!