Macro and V/SLOOKUP

[lenzwagner105]

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 have to copy the two tables into two different tabs in one document?
2) I should compare the two tables (number) and then if the number is found in both tables, the name associated with the number, spit out. Which command do I use for this? S/VLOOKUP?

3) how do I record the macro. Just let it run and run it once with the appropriate command?

Thanks for your help

[#2]

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.