Find specific text from a list of possiblities

G

Guest

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
 
I

ivan.raiminius

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I want to insert pictures from from file 2
Custom function to search a string of text for certain values occuring within it 21
Extract Data from Text Files 6
Copy/Paste from Sheet to Browser; Then Copy/Paste from Browser toSheet 11
Having trouble formatting paragraph in text box of form 2
Importing Specific Rows from a text file into Excel 1
Important message from Dixons Carphone 1
using find with a combo box 1

Top