Compare one list with another

M

Mary

I need a function, formula, etc. that will let me know if there are any items in list A that also appear on list B. I have used a 'Compare Cell Contents' formula (=OR(EXACT(B2, A2:A4)) in the past; but, this current task is a little different. The matching items may not look exactly the same. For example, in list A, an item may appear as 'acetone', but, in list B, the item may appear as 'Acetone, 98%'. I've looked into the SEARCH and FIND functions, yet, unlike the EXACT function, you cannot search an entire range. You can only search a single cell. Each list as over 1,000 entries, so that is not useful. Any suggestions?
 
F

Frank Kabel

Hi
try a formula like
=IF(COUNTIF($A$2:$A$1000,"*" & B2 & "*")>0,"Match","no match")
 

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

Looking for the correct excel formula 2
Search tab using indirect for number of times in a range 3
help needed with lookup and related functions 3
Compare lists when one column contains multiple entries 3
vlookup column based on user input 5
Combine 2 columns in a dynamic range into one 7
Compare my list to an existing list 1
VLookup IF word in string exists within cell, use the word. 0

Top