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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pete - 19 Feb 2007 16:09 GMT

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

Reply to this Message

Toppers - 19 Feb 2007 16:16 GMT

=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

> I'm trying to give a word/text a numeric value. High = 3, Medium = 2 and Low
> = 1 etc.
[quoted text clipped - 5 lines]
>
> Pete

Reply to this Message

Ron Coderre - 19 Feb 2007 16:28 GMT

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

> I'm trying to give a word/text a numeric value. High = 3, Medium = 2 and Low
> = 1 etc.
[quoted text clipped - 5 lines]
>
> Pete

Reply to this Message

ShaneDevenshire - 20 Feb 2007 01:32 GMT

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)

Signature

Cheers,
Shane Devenshire

> Typically, you'd use MATCH, VLOOKUP, or LOOKUP to solve your issue....but, if
> the cell may NOT always contain high, medium, or low...
[quoted text clipped - 24 lines]
> >
> > Pete

Reply to this Message