Find match of column separted values in another table

Copper Contributor

Hi

I have in Excel two tables Tab1 and Tab2 (see image).

I would like to get the result of Table1.Result column by finding all values of Table1.ID in Table2.ID. How can I get this result without using VBA? I use Excel in O365

Thanks

4 Replies

@ms-jaft I think you're asking how many times the ID in table 2 is found in table 1 like this:

mtarler_0-1614631865467.png

 

 

here is the equation I used:

=LET(    idList,  TEXTJOIN(",",,Table2[id]),
        ListLen,   LEN(idList),
      Listcount,   LEN(idList)-LEN(SUBSTITUTE(idList,",",""))+1,
        modList, SUBSTITUTE(idList,",",REPT(" ",ListLen)),
         stnums, SEQUENCE(Listcount,,0,ListLen)+1,
        arrayIDs,   TRIM(MID(modList,stnums,ListLen)),
             ans,   SUMPRODUCT(--(VALUE(arrayIDs)=[@id])),
             ans)

 and the file is attached

@ms-jaft 

As variant

image.png

with

=COUNTIF(Table2[result],"*"&[@Result]&"*")

 

I was so focused on how to make the id column work i didn't think how the city name in the result column was an easier find (assuming the city names are unique)

@mtarler 

You are right, in names could be misprints or wrong spelling, from this point of view your formula for counting based on IDs is more reliable.