Find match of column separted values in another table

Occasional Contributor


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


4 Replies

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




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])),

 and the file is attached


As variant





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)


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.