Return Number based on Text (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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})

Reply | Quote

No Growth any more? Must be the credit crisis…

Reply | Quote

This will do it:

Reply | Quote

Here is mu …
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.

Reply | Quote

> mu
> not be affected my typo’s

Excellent!

Reply | Quote

Point noted
PS: I would have been more “acurate” if the suggestion was worth, say… 5 cents!

Reply | Quote

Thanks Rudi

That’s a good idea.

cheers, francis

Reply | Quote

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

Reply | Quote

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

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

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.

Reply | Quote

Hi Hans,

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

cheers, francis

Reply | Quote

Look at Sheet1 in the workbook attached to post 750,978 (in the “calculating increasing rates” thread).
Cell F12 contains a working LOOKUP formula.

Reply | Quote

That a completely different formula!!

regards, francis

Reply | Quote

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

Reply | Quote

Hi Hans

Saw it. It was in sheet 1 and I look at sheet 3. My apology, it was about 4.00am in the morning and I was struggling to find out
what wrong with my formulas in the other post.

This is exactly what I have placed and copy down but the result is not what I expected as there are also duplicates of the text in column A.
Guess I need to analyise and understand how this lookup formula works.
Nevertheless, thanks and I appreciate you guide as always.

cheers, francis

Reply | Quote

Francis,

The formula that Hans provided in post 750,971:

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

is identical to the formula in the workbook at post 750,978, sheet 1 F12:

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

Regards

Reply | Quote

Hi Nathan

Thank for stepping in.

cheers, francis

Reply | Quote

None…………0
Heat…………1
Melt……….…2
Growth………3
Anneal………4
Cool…………5
E_cool………6

1] Base on Franciz’s provided table, LOOKUP function can work without sorting by :

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

2] And, just for fun, the following formulas, also return the correct number

=MATCH(A1,{“None”,”Heat”,”Melt”,”Growth”,”Anneal”,”Cool”,”E_cool”},0)-1

=FIND(LEFT(A1),”NHMGACE”)-1

Regards
Bosco

Reply | Quote

Hi Bosco

Thanks, this is just excellent!
I will need to find out more about these formulas

cheers, francis

Reply | Quote

=VLOOKUP(A1,{“None”,0;”Heat”,1;”Melt”,2;”Growth”,3;”Anneal”,4;”Cool”,5;”E_cool”,6},2,FALSE)

Reply | Quote

Hi Mike

This is something new, I don’t know that Vlookup can do this.
I will try on this.

Thanks

cheers, francis

Reply | Quote

In formulas that expect a range, you can also specify an array.

An array is enclosed in curly brackets { }.
Elements within the same row are separated by commas, so for example {1,2,3} is an array with 1 row and 3 columns (equivalent to a range such as A1:C1).
Rows are separated by semicolons, so for example {1;2;3} is an array with 3 rows and 1 column (equivalent to a range such as A1:A3).
In Mike’s formula, the second argument is an array with 3 rows and 2 columns (equivalent to a range such as A1:B3).

Note: the row and column separators depend on the system.
If the decimal separator is a point (as in the UK and USA), comma separates elements within a row, and semicolon separates rows.
If the decimal separator is a comma (as in many continental European countries), semicolon separates elements within a row, and backslash separates rows.

Reply | Quote

Thank you for the details explaination, I appreciate it.
something new I learn today.

cheers, francis

Reply | Quote