DJL
04-25-2006, 07:16 AM
Hi
I've been using the following VLOOKUP function for a while on a sales data spreadsheet and everything has been working great.
VLOOKUP($C1,Prices,2,FALSE)
Column C contains product codes
Prices is a named range with 2 cols; Product Code and Product Price on a separate worksheet.
However, after I recently updated the Prices array with a number of new rows I am now finding that the formula returns #NA for a number of instances, even those that relate to previously acceptable values in column C.
The problem appears to be with the exact match argument, as when I remove the FALSE clause the function does return a value. Unfortunately I need it to be an exact match.
The Prices array parameters appear fine (ie the region encompasses all data). Is there something I have forgotten to do?
I'm away for a few days so if anyone gets a chance to give me some advice, please don't think I'm being ignorant if I don't respond immediately.
thanks
:banghead:
I've been using the following VLOOKUP function for a while on a sales data spreadsheet and everything has been working great.
VLOOKUP($C1,Prices,2,FALSE)
Column C contains product codes
Prices is a named range with 2 cols; Product Code and Product Price on a separate worksheet.
However, after I recently updated the Prices array with a number of new rows I am now finding that the formula returns #NA for a number of instances, even those that relate to previously acceptable values in column C.
The problem appears to be with the exact match argument, as when I remove the FALSE clause the function does return a value. Unfortunately I need it to be an exact match.
The Prices array parameters appear fine (ie the region encompasses all data). Is there something I have forgotten to do?
I'm away for a few days so if anyone gets a chance to give me some advice, please don't think I'm being ignorant if I don't respond immediately.
thanks
:banghead: