Using vlookup across multiple columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2021347%22%20slang%3D%22en-US%22%3EUsing%20vlookup%20across%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2021347%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20files%20that%20I%20need%20to%20compare.%20I%20need%20to%20match%20up%20the%20account%20number%20and%20payment%20amounts%20between%20the%20two%20files%20to%20identify%20what's%20causing%20differences%20between%20the%20two%20files.%20How%20can%20I%20do%20a%20vlookup%20given%20the%20above%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2021347%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2021512%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20vlookup%20across%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2021512%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F913868%22%20target%3D%22_blank%22%3E%40Shenique1690%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20provide%20a%20sample%20dataset%20to%20get%20a%20better%20scope%20of%20the%20project%20(with%20no%20confidential%20data)%20--%20some%20alternatives%20solutions%20would%20be%20to%20use%20INDEX%2FMATCH%20or%20PowerQuery%20since%20VLOOKUP%20is%20case-sensitive%20and%20carries%20some%20risk%20to%20return%20wrong%20figures%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2025100%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20vlookup%20across%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2025100%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F867265%22%20target%3D%22_blank%22%3E%40adversi%3C%2FA%3E%3C%2FP%3E%3CP%3EYes%2C%20I%20think%20my%20vlookup%20was%20giving%20me%20incorrect%20results.%20A%20sample%20file%20is%20attached.%20Thanks%20for%20your%20help.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2025832%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20vlookup%20across%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2025832%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F913868%22%20target%3D%22_blank%22%3E%40Shenique1690%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached%20the%20VLOOKUP%20reference%20in%20Column%20G%20of%20the%20File%201%20tab.%20Any%20%23N%2FAs%20are%20because%20the%20account%20number%20does%20not%20exist%20in%20File%202.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have two files that I need to compare. I need to match up the account number and payment amounts between the two files to identify what's causing differences between the two files. How can I do a vlookup given the above?

4 Replies

@Shenique1690 

Can you provide a sample dataset to get a better scope of the project (with no confidential data) -- some alternatives solutions would be to use INDEX/MATCH or PowerQuery since VLOOKUP carries some risk to return wrong figures

@adversi

Yes, I think my vlookup was giving me incorrect results. A sample file is attached. Thanks for your help. 

@Shenique1690 

See attached the VLOOKUP reference in Column G of the File 1 tab. Any #N/As are because the account number does not exist in File 2.

You can learn in this short video what causes the VLOOKUP to result in errors and how to quickly and accurately fix them.

https://www.youtube.com/watch?v=r0IClYkK3rs