Macro and V/SLOOKUP

%3CLINGO-SUB%20id%3D%22lingo-sub-3241493%22%20slang%3D%22de-DE%22%3EMacro%20and%20V%2FSLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3241493%22%20slang%3D%22de-DE%22%3EHello%2C%20I%20need%20your%20help.%20I%20am%20supposed%20to%20shoot%20a%20macro%20for%20work%20and%20have%20a%20few%20questions%3A%3CBR%20%2F%3E%3CBR%20%2F%3E1)%20I%20have%20to%20compare%202%20different%20tables%20from%20two%20different%20documents.%20Is%20that%20possible%20at%20all%2C%20or%20do%20I%20have%20to%20copy%20the%20two%20tables%20into%20two%20different%20tabs%20in%20one%20document%3F%3CBR%20%2F%3E%202)%20I%20should%20compare%20the%20two%20tables%20(number)%20and%20then%20if%20the%20number%20is%20found%20in%20both%20tables%2C%20the%20name%20associated%20with%20the%20number%2C%20spit%20out.%20Which%20command%20do%20I%20use%20for%20this%3F%20S%2FVLOOKUP%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%203)%20how%20do%20I%20record%20the%20macro.%20Just%20let%20it%20run%20and%20run%20it%20once%20with%20the%20appropriate%20command%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20your%20help%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3241493%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor
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
1 Reply

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.

bosinander_0-1646681155934.png

 

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.