Text to represent numbers

Darrin Smith

New Member
Joined
Sep 29, 2006
Messages
12
I am wanting to use text inputs to alter numbers in a formula.

i.e. the numerical value of cell E11 is utilised in a formula. This numerical value is required to change depending on the text entered in cell E10.

i.e.
if HJV entered in E10, E11 should be 669.
if YSL entered in E10, E11 should be 656.
if TOE entered in E10, E11 should be 640.
etc, etc

How to do this?

Rgds
Darrin
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

cmhoz

Active Member
Joined
Aug 20, 2006
Messages
268
Assuming you have more than a few possible entries for E10, the easiest thing to use is a VLOOKUP.

Someplace out of the way, create a list like this

HJV 669
YSL 656
TOE 640

Doesn't matter where you put this list, as long as the stuff you want to look up is in a column to the left of what you want to bring back.

Assuming this list is located on Sheet2 in columns A and B your formula in E11 will look like this:

=VLOOKUP(E10, Sheet2!A:B, 2, FALSE)

=vlookup(what you want to find, where you want to find it, column to bring back, FALSE=only find identical match)

You can use the formula wizard (click the equals sign next to your formula bar) to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,955
Messages
5,545,162
Members
410,667
Latest member
Gaexel
Top