In worksheet 1: I have a fairly short list (~75) of values (text strings) and would like to find all matches of these strings with values from a column in another worksheet (worksheet 2)... these values might exist anywhere within a much longer string/passage in that known column.

Further, I'd like to be able generate a third worksheet with rows equal to the matched values... building a row for each, but also containing some of the other columns from worksheet 2.

Worksheet 1

ABC
DEF
GHI
...
XYZ

Worksheet 2

Column1 Column2 Column3
blah1   blahA   Chemical DEF is a compound
blah2   blahB   Compound 24231 (also known as GHI)...

Worksheet 3

Col1  Col2    Col3
DEF   blah1   blahA
GHI   blah2   blahB

Can this be done with Excel?

  • 1
    You can do it, but using a database with indexes is probably a more efficient method. What have you tried? – Excellll Jul 9 '13 at 17:43
  • Thank you for the fast response. Just starting. I am not, obviously, a big Excel user. So I am looking at MATCH, and LOOKUP. Just poking around trying to understand where to start, try out. – user236853 Jul 9 '13 at 17:50
  • This may be helpful: stackoverflow.com/q/8313919/657668 – Excellll Jul 17 '13 at 16:34

First, this will definitely take some time to be done on an excel sheet with 52k lines, and since the column containing the passage in Worksheet 2 is after the things you want in Worksheet 3, I think that using MATCH() is better.

In Worksheet 3, copy and paste the text strings you're looking for in Worksheet 2. You will then remove the ones not found with a filter later on (in this case, you will remove ABC and XYZ later on).

In column 2 of Worksheet 3, use the formula (this assumes you're using headers which are in the first row in every sheet, otherwise, change A2 to A1):

=INDEX('Worksheet 2'!A:B,MATCH("*"&'Worksheet 3'!A2&"*",'Worksheet 2'!C:C,0),1)

And in column 3 of Worksheet 3, use the formula:

=INDEX('Worksheet 2'!A:B,MATCH("*"&'Worksheet 3'!A2&"*",'Worksheet 2'!C:C,0),2)

Drag those two formulas to the end of the list and wait until the calculations are done. After that, copy the data and paste as values (Paste Special > Paste Values. This will speed things up onwards) and apply a filter so you can remove all the #N/A, if any, from the table. Sort your table to remove spaces in between rows.

  • Excellent. Many thanks. Will work on this today. Thanks again. – user236853 Jul 10 '13 at 13:24

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.