Partial match in excel, multiple values in match criteria to match against list

Question

I have a list of ID's that have multiple countries in a string.

ID  Countries
1   DK, US
2   PL, UK
3   CN, RU, DE
4   SE, FI
5   US, FI, DE
6   DK

Then I have a list I want to check if any country exist in this list.

Country Country Code
Sweden  SE
Denmark DK
Finland FI

So for example, DK, US should return TRUE since DK exist in Sheet1 list.

I have tried:

=INDEX(Sheet1!A:A;MATCH("*"&Sheet2!B2&"*";Sheet1!B:B;0))

But this dosn't work since I'm trying to match DK, US against DK or SE.

I would like to avoid using VBA or splitting the countries to separate columns. I think help/dummy columns is totally fine but not optimal

I would have put each country code in a separate column for that id or even multiple id entries 1 for each country then searched a range of cells. — Solar Mike, Jul 28 '20 at 11:44
I would like to avoid that if possible (it's extraction from database). 75k rows and in some cells I have 10-20 countries. It will become heavy and messy in this already large workbook :(. — Wizhi, Jul 28 '20 at 11:48
Notable similarities to (but not a duplicate of) [this question](https://stackoverflow.com/questions/62777715/formula-for-comparing-2-columns-for-containing-data-and-counting-their-occurence/62778597) — Chronocidal, Jul 28 '20 at 13:23

score 2 · Accepted Answer · answered Jul 28 '20 at 11:58

2

I'd use:

Formula in C2:

=SUMPRODUCT(--(FILTERXML("<t><s>"&SUBSTITUTE(B2,", ","</s><s>")&"</s></t>","//s")=TRANSPOSE(F$2:F$4)))>0

Or:

=SUMPRODUCT(--ISNUMBER(FIND(", "&$F$2:$F$4&", ",", "&B2&", ")))>0

Drag down.

answered Jul 28 '20 at 11:58

JvdV

1

This is so amazing!!! I wish I could give you a double-accept :)!!! Thank you, saves a lot of time!! :) – Wizhi Jul 28 '20 at 12:23
2

@Wizhi, glad it helped. These sort of (well formulated) questions are fun to work on (but been done on SO before I'm sure). If you are interested in what `FILTERXML` can do for you, have a look [here](https://stackoverflow.com/q/61837696/9758194) – JvdV Jul 28 '20 at 12:23

1 Answers1