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

 

[Ronald Moore]

To me, the only explanation consistent with all of the information you've presented is that, in spite of your efforts, column M in Spreadsheet2 contains numbers formatted as text, and that your lookup value in E2 is numeric. Your reply on the LEN results shows that there were no spaces in E2. TRIM(E2) was not removing any spaces, it was just coercing a numeric value to text and that text value was successfully matched in Spreadsheet2.

Although the formatting for a range may be "general", it does not necessarily follow that every cell in that range which appears to contain a number is numeric. To see this, start with a new worksheet and format column A as text. In A1, enter 1. It will be left aligned, indicating a text value. Now change the column A format to General. A1 is still left aligned, indicating text. Now double click in A1, as if to edit, and immediately hit Enter. This forces Excel to reread the data and at that point it will apply general formatting to the data, so it becomes numeric and displays right-aligned.

I don't remember the version of Excel in which this started -- Excel 2002 and 2003 contain a background error checking feature which flags (if options are chosen properly) cells with numeric values formatted as text. Under Tools> Options, on the Error Checking tab, under Settings check "Enable background error checking", and under Rules check "Number stored as text". Cells with numbers stored as text will be flagged with an error indicator in the upper left corner. If this feature is available in your version of Excel, I'll bet you see error indicators in M6:M4000 of your second spreadsheet. If you want to convert these text values to numbers, select M6, then the whole range. Click the error button (with exclamation point) which appears, then click "Convert to Number". This, incidentally, will properly convert numbers stored as text with leading and/or trailing spaces. If this feature is not available to you, search your Help information for "convert text to numbers" to see how to use Paste Special to do this.

 

[vantilian]

Thanks Ronald. I appreciate this information greatly.

Russell