Solved: VLOOKUP now you see it now you don't!

[DJL]

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

[Jacob Hilderbrand]

Moved to Excel Help forum.

[geekgirlau]

Can you post a sanitised version of your workbook?

[Jan Karel Pieterse]

Make sure the looked up categories are an exact match on both the source and the looking up range. Maybe you have an odd trailing space somewhere?

[geekgirlau]

You might also want to check for text values versus numeric.

[DJL]

Make sure the looked up categories are an exact match on both the source and the looking up range. Maybe you have an odd trailing space somewhere?

Thanks for the reply. Your hint, plus others, led me to find that the data was in two different formats and also contained trailing spaces. I don't know if the solution I then used was the most convenient but I used the TRIM function to "standardise" the target cells and the reference range and it appears to be working.

many thanks for taking the time to help

[DJL]

You might also want to check for text values versus numeric.

Thanks for the reply. Your hint, plus others, led me to find that the data was in two different formats and also contained trailing spaces. I don't know if the solution I then used was the most convenient but I used the TRIM function to "standardise" the target cells and the reference range and it appears to be working.

many thanks for taking the time to help