Search code examples
excelstring-matching

excel partial match in reverse

I have two columns. One has values like:

0008347_abcd
2008756_abgr
0008746_gss1
.......

and a second column with 4 digit numbers that some of the above column entries will partially match i.e. 

8347
8746
...

I want to find which of the first column entries have a partially matching entry in the second column. It can return anything (true false, 0 1), i just want to find them. so in the above example it would flag the first and third values. Moreover there might be multiple entries in column 1 that match one entry in column 2 and I would like to flag them all.

The first column has 3346 entries and the second 334. Can you help me do this in excel?

Solution

  • This will give you 1s and 0s:

    =SUMPRODUCT(1*(ISNUMBER(SEARCH($C$1:$C$2,A1))))

    enter image description here

    For TRUE/FALSE:

    =SUMPRODUCT(1*(ISNUMBER(SEARCH($C$1:$C$2,A1))))>0