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]

Have you done the definitions as asked for?

Control+shift+enter is required for the array-processing formulas (See e.g. http://www.cpearson.com/Excel/ArrayFormulas.aspx.)

Control+shift+enter: Press the control and the shift keys at the same time while you hit the enter key. If done correctly, Excel puts a pair of { and } around the formula in recognition.

 

[val4215]

I found the reason for the Control Shift Enter....very interesting.

 

[val4215]

Sorry I didnt see your reply until I posted the previous post.

In the first variable of the search command I placed the array for the species in the second I placed each of the other arrays, repeating until all three variant arrays were included. I did include the bignum replacement as well.

Thanks for putting up with me.

 

[Aladin Akyurek]

Not sure I understand what you are saying. There is no need to modify the formulas. If the string in A2 contains at least a species, the formula will pick that out. The formula in C2 will pick out the a variant the identified species points to.

 

[Joe4]

Your image attempt failed. If you want to post an image, please use on of the tools mentioned in section B here: https://www.mrexcel.com/forum/board-...forum-use.html​

 

[val4215]

{=IFERROR(MATCH(9.99999999999999E+307,SEARCH(" "&INDIRECT(LOOKUP(9.99999999999999E+307,SEARCH(" "&species&" "," "&A2&" "),species))&" "," "&A2&" ")),"not available")}

Is what was placed in C2 to locate the variant and return not available


Im sorry am I missing some sort of declaration in the sheet that determines which of the three variants are looked at when the species is identified in B2?

 

[val4215]

http://s67.photobucket.com/user/val4215/media/formula_zpsb7dcc4c0.png.html?filters[user]=21497224&filters[recent]=1&sort=1&o=0

 

[val4215]

SO with my limited resources i would like the formula to read

if the formula recognizes the value 1 in C2 it will search the CAT variants and return a corresponding number
if the formula recognizes the value 2 in C2 it will search the DOG variants and return a corresponding number
same for the hamster.

 

[Aladin Akyurek]

{=IFERROR(MATCH(9.99999999999999E+307,SEARCH(" "&INDIRECT(LOOKUP(9.99999999999999E+307,SEARCH(" "&species&" "," "&A2&" "),species))&" "," "&A2&" ")),"not available")}

Is what was placed in C2 to locate the variant and return not available


Im sorry am I missing some sort of declaration in the sheet that determines which of the three variants are looked at when the species is identified in B2?

Post #9 describes the set up to your specs.


A. "25lb Manx cat" contains a species, that is cat. Cat is the first item on the species list, hence 1. This result is calculated in B2 as shown in post #9 .
B. "25lb Manx cat" contains a cat item i.e. manx that is the third item on the cat list, hence 2. This result is calculated in C2 as shown in post #9 .


Recipe of [A] is: Give the position of the species which can be found if at all in A2. Cat found in A2 >> position of cat on the species list >> 1.

Recipe of is. Search for the items of the species located in A2 in A2: Cat found in A2 >> look for cat items >> manx found in A2 >> position of manx on the cat list >> 3.

 

[val4215]

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.