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 us...
excel
Formulas and Functions
office 365
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