VLookUp Between Two Workbooks (No VBA)

0
votes

I have two workbooks - let's call them workbook1 & 2. In workbook1 I want Column A to be autofilled with information stored in workbook2. However, I have come into two problems.

  • First, the formula returns a #N/A error in cell A2 of workbook1, despite being the same formula used in cells A3 & A4.
  • Second, the formula returns the wrong value for A4 despite the formula being written as approximate match (True), and the text in column B being identical.

The formula in question is:

  • (A2) =VLOOKUP([@[Vendor Name]],'Vendor Name-Code Cross Ref.xlsx'!Table1[#All],1)
  • (A4) =VLOOKUP([@[Vendor Name]],'Vendor Name-Code Cross Ref.xlsx'!Table1[#All],1)

The workbooks are below in numerical order.

Workbook1

Workbook2

I apologize for the simplicity of this question. I have tried a couple of things, I found online including this answer here and here. And I can't get it to work. I am happy to try another formula if there is a better tool for the job. I have been away from Excel for a few months and can't recall how to set this up...sorry for the easy question. Also, as I am not all too familiar with VBA I have not tried a VBA solution and that is why I listed that in the title as such.

Thanks for your help.

1
excelexcel-formulavlookup
Use INDEX/MATCH and make sure the match type is 0 for exact match. VLOOKUP can't return a value from a column to the left of the lookup column. - BigBen
@BigBen, if I were to switch the column order in Workbook2 would that work too? Or is Index/Match just better all around in this case? - J Crosby
I prefer INDEX/MATCH or Excel's new function XLOOKUP (currently available to Office Insiders), but if you switch the column order, VLOOKUP (with exact match) should work. - BigBen
@BigBen, thanks - if you want to write that as answer. I will mark this as answered as with your confirmation. I was able to get this to work. I appreciate your help. - J Crosby

1 Answers

2
votes

Couple options:

  1. INDEX/MATCH: =INDEX('Vendor Name-Code Cross Ref.xlsx'!Table1[Vendor Code],MATCH([@[Vendor Name]],'Vendor Name-Code Cross Ref.xlsx'!Table1[Vendor Name],0))

  2. Switch the column order in Workbook 2 and continue using VLOOKUP, but with exact match.

  3. If you have access to the new Excel functions, use XLOOKUP.