VLOOKUPS & Formula Errors

**Barcelona** · 10-21-2009, 12:33 PM

I have inherited an excel spreadsheet from our Houston office that uses many VLOOKUPS. In a few cases I have valid formulas but the calculation results in an error: #N/A. Strange because I have the exact same formula in the cell above it and it yields the correct result. And I am using the same formatting but no matter what I do I can't get the #N/A to change to a number.

The function is entered as: VLOOKUP (C60, Y:1:AB:11, 4)*N60 where N60 is a dollar amount. The VLOOKUP refers to a negative percentage value. The net result of the calculation is: #N/A.......

I have tried to re-type it a million times but nothing fixes it.

Looked at Tools-Options to see if "automatic" was checked on the calcuation sheet (and it is checked).

Any ideas or suggestions would be appreciated?????

**zbor** · 10-21-2009, 12:55 PM

What is : in formula?

Try
=VLOOKUP (C60, Y1:AB11, 4)*N60

**teylyn** · 10-21-2009, 04:46 PM

Hi,

when VLOOKUP returns #N/A!, it means that the value you are looking for can not be found in the list you are looking at. Check the contents of C60. Is this value anywhere in Y1:Y11? If you see it there, check that there are no leading or trailing spaces in C60 or the list in column Y.

For VLOOKUP "searchterm" is not the same as "searchterm " (with a blank at the end) and this is one possible reason for a #N/A

hth