Text to represent numbers

[Darrin Smith]

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

 

[cmhoz]

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.

 

[Darrin Smith]

Terrific.... thanks very much