Look for a specific word in a cell, look at an array and return corresponding number

val4215 · Aug 15, 2018

Lookine for a formula that would look at a text cell with many identifiers, look for a specific word then look at an array and give the corresponding number that is associated with the word.
Example:

Blue Ford F-150 V8 auto trans

The formula would look for a specific word like Blue then look at an array such as

Blue 1
Red 2
green 3

and return the value 1

I have been using the IF(ISNUMBER(SEARCH("BLUE", C7),1, IF(ISNUMNER(SEARCH.........

But I am going to have many identifiers and want people to be able to add to the list without having to write more formula.

Thanks for any help

Aladin Akyurek · Aug 15, 2018

=LOOKUP(9.99999999999999E+307,SEARCH(" "&Colors&" "," "&C7&" "),Numbers)

where Colors is the range housing Blue, Red, and Green, and Numbers 1, 2, and 3.

val4215 · Aug 15, 2018

Aladin Akyurek said:
=LOOKUP(9.99999999999999E+307,SEARCH(" "&Colors&" "," "&C7&" "),Numbers)

where Colors is the range housing Blue, Red, and Green, and Numbers 1, 2, and 3.

IT WORKED!!!!!!!!!!!!!!!!!!!!!!!!!!!
What is the reason for the 9.9999999999...E+307?

Thank you SO MUCH!!!!!!!

val4215 · Aug 15, 2018

I looked it up and found the answer....

THANKS AGAIN FOR YOUR HELP!!!!

Aladin Akyurek · Aug 15, 2018

val4215 said:
IT WORKED!!!!!!!!!!!!!!!!!!!!!!!!!!!
What is the reason for the 9.9999999999...E+307?

Thank you SO MUCH!!!!!!!

val4215 said:
I looked it up and found the answer....

THANKS AGAIN FOR YOUR HELP!!!!

You are welcome. Thanks for the update.

val4215 · Sep 10, 2018

OK....what a huge help this has been but as usual, yesterdays miracle is today's quota.

I need to go one step further.....
I need to have the formal look at the cell and if it contains one of a number of options, look up in a specific table.
Like the about formula but with maybe "IF" statements and then give a value from the table that is associated with the statement.

Here's the scenario

Three species of animals are present in a description. The formula would look and see which species is present then go to the table that is associated with that animal and return a number.

Species Table
1 Cat
2 Dog
3 Hamster

Cat Table
1 Persian
2 Calico
3 Manx

Dog Table
1 St Bernard
2 Hound
3 Basset

Hamster Table
1 Golden
2 Chinese
3 Campbell

The cell that the formula looks at would say:
“25lb Manx cat”
The formula would look at first the species table and give “1”
Then it would know to look at the cat table to give “3”

I’ve tried IF statements incorporating the Search formula given but it keeps returning errors.
Thanks for any help

Aladin Akyurek · Sep 10, 2018

What is the end result you require?

val4215 · Sep 10, 2018

speicies

cat

dog

hamster

cat

persian

st bernard

golden

dog

calico

hound

chinese

hamster

manx

basset

campbell

<colgroup><col style="width:48pt" width="64" span="14"> </colgroup><tbody>
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]species[/TD]
[TD="width: 64"]variant[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]

[TD="colspan: 2"]25lb Manx Cat[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]3[/TD]

</tbody>

Aladin Akyurek · Sep 11, 2018

Book1

A

B

C

D

E

F

G

H

1

species

variant

Species

cat

dog

hamster

2

25lb Manx cat

1

3

Cat

Persian

St Bernard

Golden

3

Dog

Calico

Hound

Chinese

4

Hamster

Manx

Basset

Campbell

5

Sheet1

E2:E4 is named species via the Name Box, F2:F4 cat, G2:G4 dog, and H2:H4 hamster.

Additionally, bignum is defined via Formulas | Name Manager as referring to:

=9.99999999999999E+307

In B2 control+shift+enter, not just enter:

=IFERROR(MATCH(bignum,SEARCH(" "&species&" "," "&A2&" ")),"not available")

In C2 control+shift+enter, not just enter:

=IFERROR(MATCH(bignum,SEARCH(" "&INDIRECT(LOOKUP(bignum,SEARCH(" "&species&" "," "&A2&" "),species))&" "," "&A2&" ")),"not available")

val4215 · Sep 11, 2018

Not getting it.

=IFERROR(MATCH(bignum,SEARCH(" "&INDIRECT(LOOKUP(bignum,SEARCH(" "&species&" "," "&A2&" "),species))&" "," "&A2&" ")),"not available")

In each of the search commands I don't see where it is checking to see the first result to determine which of the other categories to look.

I tried inputting the ranges of the other tables but it didn't see the matches.

Could you also explain the significance of the control shift enter function.....the first formula didn't work until I did the C S E. I have experienced other formulas I have used that don't always perform they way the should, sometime they work other times not. I will reset them with the C S E and see if they are consistent.

Thank you again for your help, sorry to be thick if that is the way I am perceived.

Look for a specific word in a cell, look at an array and return corresponding number

val4215

New Member

Excel Facts

Aladin Akyurek

MrExcel MVP

val4215

New Member

val4215

New Member

Aladin Akyurek

MrExcel MVP

val4215

New Member

Aladin Akyurek

MrExcel MVP

val4215

New Member

Aladin Akyurek

MrExcel MVP

val4215

New Member

Similar threads

Forum statistics

Share this page

Look for a specific word in a cell, look at an array and return corresponding number

New Member

Excel Facts

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock