Forum Discussion

lenzwagner105's avatar
lenzwagner105
Copper Contributor
Mar 02, 2022

Macro and V/SLOOKUP

Hello, I need your help. I am supposed to shoot a macro for work and have a few questions: 1) I have to compare 2 different tables from two different documents. Is that possible at all, or do I hav...
excel
bosinander's avatar
bosinander
Iron Contributor
Mar 07, 2022

Hi lenzwagner105 

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.

Resources