VLookUp Between Two Workbooks (No VBA)

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.

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.

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.