Vlookup returns #N/A even though matches, cell formats ok

vantilian

New Member
Joined
Mar 3, 2002
Messages
44
I've used vlookup many times and I have a very simple one giving me fits. Usually when I've had this problem it turned out being an issue with cell formats between the fields being matched. I've exhausted that as far as I know, and still no matches even though I manually checked and they are there. Using Excel 2002.

I have 8-digit Invoice Numbers in Spreadsheet 1 column E, and I'm trying to match them in Spreadsheet 2 column M. My vlookup formula is in Spreadsheet 1 cell F2 (row 1 is a header), and formula is as follows: =vlookup(E2,'[Spreadsheet 2.xls]Invoices',$M$6:$M$4000,1,false). Note: rows 1 through 5 in Spreadsheet 2 are headers.

Both columns in each spreadsheet are formatted "General". I've tried switching those columns to various formats such as "Number" with 0 decimals, etc., etc. Still can't get it to match. Any one ever run across this that may have some suggestions? I appreciate the help.

Russell
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Not sure if this is a typo in this post or if your formula is mistyped in your sheet, but there should be an exclamation point after the sheet name as:

=vlookup(E2,'[Spreadsheet 2.xls]Invoices'!,$M$6:$M$4000,1,false).

If your simply looking to see if a match exists then maybe Match() will work for you:

=Match(E2,'[Spreadsheet 2.xls]Invoices'!,$M$6:$M$4000,0)
 

vantilian

New Member
Joined
Mar 3, 2002
Messages
44
Yes, sorry but that is a typo in translating it over to this. I do have the "!" in the original formula: =vlookup(E2,'[Spreadsheet 2.xls]Invoices'!,$M$6:$M$4000,1,false).

Thank you for the suggestion on using the Match function. I tried it and apparently the same problem affecting the Vlookup also affects Match. This is blowing my mind. For instance the first invoice number in cell E2 is 18851201 is Spreadsheet 1. If I go over to Spreadsheet 2, invoice number 18851201 is sitting there in cell M1290. I have to think it has something to do with the formatting behind the cells, but as I've mentioned, I've exhausted everything I know to do with the formatting, to make sure both columns are in synch. Thanks for taking the time to review this. I appreciate the suggestion.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
If it is an #N/A error, then Excel thinks that the match is not made...therefore it is a formatting problem or there may be spaces in front or behind one to th cells:

Perhaps: Try one of these:

=vlookup(Value(E2),'[Spreadsheet 2.xls]Invoices'!,$M$6:$M$4000,1,false).
=vlookup(Trim(E2),'[Spreadsheet 2.xls]Invoices'!,$M$6:$M$4000,1,false).
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

What does

=LEN(E2)

and

=LEN('[Spreadsheet 2.xls]Invoices'!,$M$x)

return, where Mx is the cell that should match E2
 

vantilian

New Member
Joined
Mar 3, 2002
Messages
44
To just_jon:

=LEN(E2) returns 8

and

=LEN('[Spreadsheet 2.xls]Invoices'!,$M$x) also returns 8


To NBVC:

=vlookup(Value(E2),'[Spreadsheet 2.xls]Invoices'!,$M$6:$M$4000,1,false) still returned #N/A.

=vlookup(Trim(E2),'[Spreadsheet 2.xls]Invoices'!,$M$6:$M$4000,1,false) returned 18851201 -- it worked!

Can you elaborate on what was transpiring here so I'll know for future reference? Also, any suggestions for cleaning up the formats on those fields? Thanks to you both for your help.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

From Excel's help file on the Trim(text) function:

"Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing."

So basically the formula I proposed, trims your E2 entry to make sure there are no abnormal spaces, then it compares that trimmed E2 to the table values. It is obvious that there were some unmatched spaces in your data.

It's hard to suggest the best way. You can go through each and delete unnecessary spaces, as long as the error is consistent, you can go to Data|Text to Columns and delimit by spaces. This will add "empty" columns that you can delete later or you can insert a column and enter =Trim(E2) and copy that down. Then you can hide the original column or copy and Paste Special|Value the new column and then delete the original.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
If

=ISNUMBER(MATCH(E2,'[Spreadsheet 2.xls]Invoices'!$M$6:$M$4000,0))+0

which is faster than an equivalent VLOOKUP formula, does not return 1 for a match that you expect, download ASAP Utilities and try to clean up the M-range for invisible chars.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
You had data such as

xxx{space}

or

{space}xxx

which will not be matches for

xxx

TRIM deletes begining/trailing spaces.

BTW, that's why I asked you to 'LEN' those 2 cells - to see if the actual length matched the number of characters you could see.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,455
Messages
5,572,223
Members
412,448
Latest member
ManuW
Top