VLOOKUP from External Access Table

[mcmullenrich]

I am trying to set up a VLOOKUP to a table which is linked to Access. The linked table is a master vendor list which comes directly from our accounting system. I'm using the VLOOKUP in a listing of payments to vendors and I want to be able to only type in the vendor number and have the VLOOKUP provide the name. The formula I used is:

=IFERROR(VLOOKUP(C2,Vendor_List,2,FALSE),"")

When I evaluate the formula, I get an error, as if the vendor number I entered isn't on the list, but it is. If you need more information please let me know.

Also, I did it this way so I could refresh the list periodically to include any new vendors, rather than having to re-export the current list every time.


Rich

 

[Phox]

Is the the first column of Vendor_List sorted? How many columns does Vendor_List contain? What specific error are you getting?

 

[mcmullenrich]

The first column is sorted (numerically, ascending). There are two columns - Column 1 is the vendor number and Column 2 is the vendor name. When I evaluate the formula, I get a #N/A, which doesn't display due to the IFERROR function.

 

[Phox]

Use the formula =TYPE() to test both values you are comparing that you think should be the same. Possibly one is string and the other is numerical while looking the same?

 

[mcmullenrich]

You're right. The vendor number entered returns "1" from the =Type() function, denoting a number and the vendor number from the table returns "2", denoting text. The problem is, I can't get either one to change so they match up. I tried the Format Cell dialog and changing it from general to number and number to text and a couple other changes - nothing worked. Any ideas?

 

[Phox]

Force the vendor number to text by using T() function.

=IFERROR(VLOOKUP(T(C2),Vendor_List,2,FALSE),"")

 

[mcmullenrich]

OK...I understand what you're doing but here's the weird thing. When I evaluate the formula, the vendor number in C2, converts to nothing - more specifically "". WTF?

 

[mcmullenrich]

I changed it to:

=IFERROR(VLOOKUP(TEXT(C2,"General"),Vendor_List,2,FALSE),"")

That seems to work. Very strange, but thanks for all your help. I really appreciate it.

 

[Phox]

Yeah, T() doesn't work like I thought... Instead try TEXT()

=IFERROR(VLOOKUP(TEXT(C2,"###"),Vendor_List,2,FALSE),"")

We'll get there eventually!