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

[val4215]

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]

I am sorry that I wasnt getting what you had instructed in post #9

I have never worked with setting up Name Ranges before and your instructions, to my uninformed eyes looked like as if you were restating what the example I had given in the previous post. After searching for tutorial on Name Ranges it all came together.


Thank you for the guidance and your patience.

You are welcome. Thanks for the update.