Checking to see if multiple values from one list exist in the other

[makaveeti]

Hi,

In the attached spreadsheet, I require a formula which checks if any of the product names in sheet2 column A exist in sheet1 column A and if there is a match to include the word Yes in column b of sheet 1.

All product names are text only. In Sheet 1 Column A there are some cells which contain multiple product names. I would like the formula to be able to check these cells too so that if the cell contains a product from Sheet 2 Column A, then to mark it as a match.

Thank you in advance.

[TMS]

How is this different to the last time you asked this question?

https://www.excelforum.com/excel-for...ml#post5633802

[kvsrinivasamurthy]

Try any one

=COUNTIF(Sheet2!$A$1:$A$9,A2)>0

Or

=ISNUMBER(MATCH(A2,Sheet2!$A$1:$A$9,0))

[Glenn Kennedy]

The tricky one was sweet potato... ensuring it wasn't recorded as a match wit potato.

=IF(SUMPRODUCT(IFERROR(MATCH((FILTERXML("<A><B>"&SUBSTITUTE(A2,",","</B><B>")&"</B></A>","//B")),Sheet2!$A$1:$A$9,0),""))>0,"Match","")

[makaveeti]

Try any one

=COUNTIF(Sheet2!$A$1:$A$9,A2)>0

Or

=ISNUMBER(MATCH(A2,Sheet2!$A$1:$A$9,0))

Thank you kvsrinivasamurthy, The formulas handle part of the problem however there are a few cells in sheet 1 column which contain multiple product names, which the formula is returning as false however I need the formula to produce a value of true even if there is one product that matches from sheet2.

Example 1 : we know apples are in list sheet 2. There is a cell in sheet 1 which contains the text "potato, apples, pears". All these items appear in the sheet2 therefore I need the formula to return 'True'

Example 2 : There is a cell in sheet 2 which contains the text "beetroot, apples, radish". The item apple appears in sheet 2 therefore I need the formula to return 'True'

Also it is worth mentioning that a comma will not always appear after product for the cells that contain multiple product names.

I have re-attached the spreadsheet with the cells in question.

[makaveeti]

The tricky one was sweet potato... ensuring it wasn't recorded as a match wit potato.

=IF(SUMPRODUCT(IFERROR(MATCH((FILTERXML("<A><B>"&SUBSTITUTE(A2,",","</B><B>")&"</B></A>","//B")),Sheet2!$A$1:$A$9,0),""))>0,"Match","")

Thank you very much Glen. You are on the right lines. I appreciate the point about the sweet potato however for the purpose of the sanity check I will be doing we can record sweet potato as a match.

There is an issue which I overlooked. Not all the cells containing multiple products will be separated by a comma. I've included a revised spreadsheet in my post above.

[Glenn Kennedy]

So we now have not just a , but also:

feat.
and
&


What other delimiters can there be?

[Glenn Kennedy]

See file. Please note that I HAD solved the sweet potato issue in my last post. It is correctly identified as NOT being a match for potato.

=IF(SUMPRODUCT(IFERROR(MATCH((FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"and",","),"&",","),"feat.",","),",","</B><B>")&"</B></A>","//B")),Sheet2!$A$1:$A$9,0),""))>0,"Match","")

[makaveeti]

So we now have not just a , but also:

feat.
and
&


What other delimiters can there be?

Looking at my source data here is what i found:

feat.
ft.
&
,
f/
:

[Glenn Kennedy]

OK. Sufficiently small in number to allow for a few more nested SUBSTITUTEs. Out for a walk. Back in an hour.

[Glenn Kennedy]

Final version:

=IF(SUMPRODUCT(IFERROR(MATCH((FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,":",","),"f/",","),"ft.",","),"and",","),"&",","),"feat.",","),",","</B><B>")&"</B></A>","//B")),Sheet2!$A$1:$A$9,0),""))>0,"Match","")

[makaveeti]

Glenn - you are a magician. Thank you so much.

The spreadsheet I have consists of 162424 rows. The formula you designed has made dealing with updates to this spreadsheet very effective.

[Glenn Kennedy]

You're welcome! Thanks for the feedback.