set value in column based on value in ajoining column

[Guest]

I update a spread sheet with a name in one column that uses letters and
numbers and then manually put in a numeric value in the ajoining column. I'd
like to automate this process. I need to be able to search a list of 35-40
names and then have excel place a predefined number in the next column.

 

[Pete_UK]

Set up a table somewhere in your spreadsheet (assume X1:Y40 on the
same sheet), made up of your list of names in column X and the
corresponding number in column Y. Then, if you enter your name in A2,
put this formula in B2:

=IF(A2="","",VLOOKUP(A2,X$1:Y$40,2,0))

The formula can be copied down column B for as many items as you are
likely to input in column A.

Hope this helps.

Pete

 

[Gord Dibben]

Sounds like you could use a lookup table and some VLOOKUP formulas in a column.

See Debra Dalgleish's site for more on VLOOKUP and Data
Validation lists for entering the choices.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal01.html

Note the section on using DV lists from another worksheet by naming the list.


Gord Dibben MS Excel MVP