VLOOKUP in Excel 2000

Posted by Rob Horvath on December 03, 2001 4:31 PM

I am having a serious issue. I have an amortization table on a worksheet. The table itself is referenced by a named range (i.e. Condo65 = 65CondoAM!A1:F61). Everything functions properly, except when I try a VLOOKUP. I am trying to match the period number (in column 1), with the principal balance in column 6. The problem is, unless I use say, "11" (with quotes) for the value to look up, I get #N/A returned. This is burdensome, because I calculate the month previously and wish to simply use a cell reference to return the value I want.

The formula I am using is: =VLOOKUP(F20,Condo65,6,FALSE)

Which doesn't work. If however I use: =VLOOKUP("11",Condo65,6,FALSE) it returns exactly what I want.

I have made sure the format for all cells involved is general (tried numeric also) to no avail.

Anyone have ANY suggestions?????

Posted by IML on December 03, 2001 4:45 PM

One option would be to go your table, highlight the first row your are looking up, and go to data - text to columns and hit finish.

Alternatively, you could change your formula to
=VLOOKUP(text(F20,"#"),Condo65,6,0)

good luck

Posted by Rob on December 03, 2001 4:58 PM

THANKS!!! It works. Now only if I could figure out WHY the documentation on ANY site doesn't list this restriction...