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 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
excel

1 Reply

  • 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