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

[vantilian]

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

 

[NBVC]

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]

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]

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]

What does

=LEN(E2)

and

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

return, where Mx is the cell that should match E2

 

[vantilian]

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]

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]

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]

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.

 

[vantilian]

Thanks everyone for all the education on this issue, and the solutions.

Russell