How can I give text (A,B...) a number value in Excel (A=1, B=2..)

[Pete]

I'm trying to give a word/text a numeric value. High = 3, Medium = 2 and Low
= 1 etc.

I thought this could be done with a lookup table? If anyone has any
information on how to do this I'd be most appreciative.

Cheers,

Pete

 

[Toppers]

=VLOOKUP(text,A:B,2,0)

Where text is your word (or cell address of text)

columns A & B contain your word to number table

A B
HIGH 3
MEDIUM 2
LOW 1

etc

HTH

 

[Ron Coderre]

Typically, you'd use MATCH, VLOOKUP, or LOOKUP to solve your issue....but, if
the cell may NOT always contain high, medium, or low...

This is durable against that situation and returns zero:
=SUM(COUNTIF(A1,{"Low","Medium","High"})*{1,2,3})

and it's shorter than something like this (which does the same thing):
=IF(ISNA(MATCH(A10,{"Low","Medium","High"},0)),0,MATCH(A10,{"Low","Medium","High"},0))

Note: you could also list "Low","Medium","High" in a range and reference
that instead.

Does that give you something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[ShaneDevenshire]

Hi Pete,

You can use VLOOKUP and still deal with blank cells. Create the lookup
table and set it up as follows:

A B
0 0
High 3
Low 2
Mid 1

Assume that the first 0 is in cell A1, To be pretty, name the range A1:B4 T.
Assume your first value to lookup is in E1, then your formula becomes:

=VLOOKUP(E1,T,2)

If E1 is blank this formula returns 0. Note that the table is sorted
Ascending on the first column.

Of course you don't need to use a range name:

=VLOOKUP(E1,A$1:B$4,2)