VLOOKUP Error with inventory referencing

New Contributor

I am attempting to set up a vlookup function based on a SKU/MPN code. I have a product list from my supplier that has all the inventory updated on a weekly basis. And I have an exported product list from my web store. I would like to be able to reconcile the inventory of the web list against the list from may supplier, then reimport the updated list back to my web store. I have hundreds of products each with multiple size and color variants so updating one at a time is not a reasonable option. 


=VLOOKUP(G3,_03022023_2[MPN],3,FALSE) this is my code but it is just returning the #REF! error. 


Thank you for any help you can give!



2023-03-05 (1).png2023-03-05 (3).png

3 Replies
best response confirmed by Hans Vogelaar (MVP)


The #REF! is coming from the reference to a single column in the table.  3 is the column index but there's only 1 column in the provided table array.


Are you trying to return the Quantity on Hand?  If so, you could use XLOOKUP:

=XLOOKUP(G3,_03022023_2[MPN],_03022023_2[Quantity On Hand])
Yes! Thank you that worked!
Glad it worked. You're welcome!