Vlookup do not give results

[Chris van der Berg]

Hi Everybody,
I have a peculiar problem when using vlookup in file Invoice 2014.xlms.

When I open the file and click on the scroll button in cell M1 and select a customer with 1 entry in the database (Cells W2:AA5), vlookup do not give a result, however when I select a customer with multiple entries in the database eg. customer 31 it works 100%.


Can someone please help.

Regards

Chris

 

[NARAYANK991]

Hi Chris ,

To debug a VLOOKUP formula , it would be better if the formula were posted , instead of an explanation.

Can you say what are the minimum and maximum values of the scroll button ?

When you say that the VLOOKUP formula does not give a result , what does it return ?

Narayan

 

[Chris van der Berg]

The reply I got from Narayan for your information

Hi Chris

The problem is very straightforward ; never use VLOOKUP or HLOOKUP with the last parameter either TRUE or omitted.

Using the VLOOKUP function as in : =VLOOKUP(T25,$W$2:$AA$5,4,FALSE) is different from using it as in :=VLOOKUP(T25,$W$2:$AA$5,4) or =VLOOKUP(T25,$W$2:$AA$5,4,TRUE)

The latter two actually return an approximate match , whereas the former returns an exact match. If the former does not find an exact match , it will return an error value.

The latter , if it does not find an exact match will return the nearest value , expecting the data to be in sorted order ; if the data is not in sorted order , you will get unexpected results.

Please change all your VLOOKUPs to the former version , where FALSE is the 4th parameter.

Regards ,

Narayan