Forum Discussion

ms-jaft's avatar
ms-jaft
Copper Contributor
Feb 27, 2021

Find match of column separted values in another table

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

excel
Formulas and Functions
office 365

4 Replies

    • mtarler's avatar
      mtarler
      Silver Contributor
      Mar 03, 2021
      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)
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Mar 03, 2021

        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.

  • mtarler's avatar
    mtarler
    Silver Contributor
    Mar 01, 2021

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

     and the file is attached

Resources