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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=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
 
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
 
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)
 
Back
Top