Find specific text from a list of possiblities
 

I have a list of various test and number values in about 14000 lines. there
are about 20 potetial text words that i would like to check each line and see
if they contain any of the 20, if so return that value. The example below
shows the words i want to test against and the list of values which need to
be tested. the top and left line denote the excel column and row

EX

LIST OF WORDS TO LOOKUP
A B
1 COM
2 PUT
3 CALL
4 DELETED


LIST TO TEST
VALUES TO BE TESTED RESULT OF FORMULA
A B
11 ABCDE FG HI CALL Q 123 CALL
12 ABCDECALLQ123 CALL
13 ABCDEFG PUTLMNOP PUT
14 ACOM BCDEFG123 COM

Find specific text from a list of possiblities
 

Hi,

try using this array formula (insert using ctrl+shift+enter)

=INDEX($B$10:$B$13,MATCH(FALSE,ISERROR(FIND($B$10: $B$13,B18,1)),0))

assuming that test criteria are in b10:b13, values to test in b18 and
further

Regards,

Ivan

Find specific text from a list of possiblities
 

Perfect!

Thank you

-DC

" wrote:

Hi,

try using this array formula (insert using ctrl+shift+enter)

=INDEX($B$10:$B$13,MATCH(FALSE,ISERROR(FIND($B$10: $B$13,B18,1)),0))

assuming that test criteria are in b10:b13, values to test in b18 and
further

Regards,

Ivan