Feb 27 2021 11:40 AM
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
Mar 01 2021 12:51 PM
@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
Mar 03 2021 06:51 AM
Mar 03 2021 07:09 AM
Mar 03 2021 11:36 AM
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.