formula Finding text and returning certian word

[Anandpersad]

Hi All,

I am looking for a formula for the following:
I have a list of word, (FOV1, FOV2, FOV3, FOV4, FOV5, FOV6, FOV7, FOV8 and FOV9), which I would to find in a range of text in column A and have the searched text (FOV1 or FOV2 or FOV3 or FOV4 orFOV5 or FOV6 or FOV7, FOV8 or FOV9), returned in column B.
Like in “column A” I have the range of text. In column B I would like to put a formula, which will search for the 9 words as mentioned above, and give me which of the FOV are in text in column A. The text which I am looking for in column A, can be anywhere in the text range. There is also not a recurrent word or character which I use to find my texts.

I tried the following formula:
=IF(ISNUMBER(SEARCH("FOV1",A2)),"FOV1",IF(ISNUMBER(SEARCH("FOV2",A2)),"FOV2",IF(ISNUMBER(SEARCH("FOV3",A2)),"FOV3",IF(ISNUMBER(SEARCH("FOV4",A2)),"FOV4",IF(ISNUMBER(SEARCH("FOV5",A2)),"FOV5",IF(ISNUMBER(SEARCH("FOV",A2)),"FOV6","FOV7"))))))

As you this formula is restricted to max 7. By FOV7, I get the error message that more levels of nesting are used than allowed (I have more than 7 FOV).

Can you help me with a formula, if possible?

Thanks and Regards,

Anand

 

[AlKey]

If you using Excel version 2007 or higher and it is not in compatibility mode you should not experience this type of problem. Here is a formula that should work for you.

enter formula in B2 and copy down.

=IFERROR(LOOKUP(10^308,SEARCH({"FOV1","FOV2","FOV3","FOV4","FOV5","FOV6","FOV7","FOV8","FOV9"},A2),{"FOV1","FOV2","FOV3","FOV4","FOV5","FOV6","FOV7","FOV8","FOV9"}),"")

 

[Anandpersad]

Hi Alkey,

Thank you very much for your quick reply and help.
Formula works perfectly for me.

Anand

If you using Excel version 2007 or higher and it is not in compatibility mode you should not experience this type of problem. Here is a formula that should work for you.

enter formula in B2 and copy down.

=IFERROR(LOOKUP(10^308,SEARCH({"FOV1","FOV2","FOV3","FOV4","FOV5","FOV6","FOV7","FOV8","FOV9"},A2),{"FOV1","FOV2","FOV3","FOV4","FOV5","FOV6","FOV7","FOV8","FOV9"}),"")

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307,SEARCH(" FOV"&{1,2,3,4,5,6,7,8,9}&" "," "&A2&" "),"FOV"&{1,2,3,4,5,6,7,8,9})

Note. See http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html for the big look up value.