# Thread: Return Number based on Text (Excel 2003)

1. ## Return Number based on Text (Excel 2003)

Hi

I have a range of text in Col A and want to return a code number based on it.
I have tries the Lookup but doesn't give the result. Here is the formula I am using

=LOOKUP(A1,{"None","Heat","Melt","Growth","Anneal" ,"Cool","E_cool"},{6,5,4,3,2,1,0})

TIA

regards, francis

2. ## Re: Return Number based on Text (Excel 2003)

I'd create a lookup table in a sheet and use VLOOKUP.

3. ## Re: Return Number based on Text (Excel 2003)

This will do it:

4. ## Re: Return Number based on Text (Excel 2003)

Here is mu <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> ...
You could even improve it more by adding list validation to cell A9, so that one could use the in-cell drop-down list to select the text value. So doing the lookup value will not be affected my typo's or spelling.

5. ## Re: Return Number based on Text (Excel 2003)

> mu <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
> not be affected my typo's

Excellent! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

6. ## Re: Return Number based on Text (Excel 2003)

Thanks Rudi

That's a good idea.

cheers, francis

7. ## Re: Return Number based on Text (Excel 2003)

Hans and Jerry

Thanks for suggesting this. I wanted to use the Lookup function but not sure why its doesn't works.
Any idea?

TIA

cheers, francis

8. ## Re: Return Number based on Text (Excel 2003)

The built-in help for the LOOKUP function states explicitly that the values in the second argument must be sorted in ascending order, otherwise LOOKUP won't work correctly. You could have used
<code>
=LOOKUP(A1,{"Anneal","Cool","E_cool","Growth","Hea t","Melt","None"},{2,1,0,3,5,4,6})
</code>
But still, this formula is difficult to maintain as values are added, removed or changed. It's much better to use a lookup table in a sheet.

9. ## Re: Return Number based on Text (Excel 2003)

The first array in LOOKUP has to be sorted ascending. Try

=LOOKUP(A1,{"Anneal","Cool","E_cool","Heat","Melt" ,"None"},{3,1,0,4,5,6})

10. ## Re: Return Number based on Text (Excel 2003)

No Growth any more? Must be the credit crisis... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

11. ## Re: Return Number based on Text (Excel 2003)

Hi Hans,

Thanks but its still don't works after having sorted the data.

cheers, francis

12. ## Re: Return Number based on Text (Excel 2003)

Look at Sheet1 in the workbook attached to <post:=750,978>post 750,978</post:> (in the "calculating increasing rates" thread).
Cell F12 contains a working LOOKUP formula.

13. ## Re: Return Number based on Text (Excel 2003)

That a completely different formula!!

regards, francis

14. ## Re: Return Number based on Text (Excel 2003)

I'm very sorry, I give up. It's exactly the same formula.

15. ## Re: Return Number based on Text (Excel 2003)

Francis,

The formula that Hans provided in <post#=750,971>post 750,971</post#>:

=LOOKUP(A1,{"Anneal","Cool","E_cool","Growth","Hea t","Melt","None"},{2,1,0,3,5,4,6})

is identical to the <working> formula in the workbook at <post#=750,978>post 750,978</post#>, sheet 1 F12:

=LOOKUP(A1,{"Anneal","Cool","E_cool","Growth","Hea t","Melt","None"},{2,1,0,3,5,4,6})

Regards

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•