Mar 02 2022 07:01 AM
Mar 07 2022 11:32 AM
1) you can keep the in different documents. If you type = in a cell in the first and change window/document and select cells in the other, you will get them linked.
2) First use XMATCH to see if there is a match in the other table.
FILTER the matches that NOT ISERROR
Finally, use XLOOKUP to find the corresponding name.
H2:
=FILTER(Table1[Value];NOT(ISERROR(XMATCH(Table1[Value];Table2[Value]))))
J2
=LET(valuesInBoth;FILTER(Table1[Value];NOT(ISERROR(XMATCH(Table1[Value];Table2[Value]))));
output;XLOOKUP(valuesInBoth;Table1[Value];Table1[Name]);
output
)
3) Assuming Excel 365, You do not need a macro and the output will update as data in the tables changes.