Forum Discussion

EllaRoRo's avatar
EllaRoRo
Copper Contributor
Apr 18, 2023

Vlookup formula not working

I have this two sheets Im working from, I need to match the Code (B2) from DARIRY DEL MAY to Sheet1 column G, and have the value returned as the matching number but from column F (UPC if applicable) ...
Excel
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Apr 18, 2023

EllaRoRo The VLOOKUP formula searches for a match of what's in B2 in the first column of the lookup range (i.e. column F) and then returns the value from the 2nd column (i.e. column G). Your intention is to find B2 in G and return F. That's where it goes wrong.

 

Now, you can make VLOOKUP to 'look left' by using the CHOOSE function, but I find that overly complicated. Better to use INDEX/MATCH or XLOOKUP if your Excel version supports it.

 

Then the formula would look like this:

=IFERROR(INDEX(Sheet2!F:F,MATCH(B2,Sheet2!G:G,0)),"not found")

or

=XLOOKUP(B2,Sheet2!G:G,Sheet2!F:F,"not found")

 

Alternatively, and if you control the lay-out of the data, swap columns F and G, Then your original formula shall work as well.

Resources