VLOOKUP FORMULA EVALUATION NEEDED

[SSJ]

Hello everyone,

The following two formulas are identical. Formula#1 gives the desired result but Formula#2 does not.

1) Formula#1 is stating to lookup the work order number in B4. If there is an error, then put a zero otherwise put the relevant value from column 21
2) Formula#2 should be doing the same as above. If there is an error due to the vlookup put a blank, otherwise put the value from column 8. In the event of an error, instead of putting a blank, it is picking up the text of another work order. The text is exactly the same in all the lines where there is an error.

So, what needs to be changed?

FORMULA#1
=IF(ISERROR(VLOOKUP($B4,'P:\WIPSUM\[WIPSUM8.xls]WIPSUM8'!$B$2:$AJ$2000,21)),"0",VLOOKUP($B4,'P:\WIPSUM\[WIPSUM8.xls]WIPSUM8'!$B$2:$AJ$2000,21))

FORMULA#2
=IF(ISERROR(VLOOKUP($B4,'P:\WIPSUM\[WIPSUM8.xls]WIPSUM8'!$B$2:$AJ$2000,8)),"",VLOOKUP($B4,'P:\WIPSUM\[WIPSUM8.xls]WIPSUM8'!$B$2:$AJ$2000,8))

Thanks
SJ

 

[Dave]

Hi SJ,
VLookUp has a 4th thingy, which if omitted, (as in your case) defaults
to TRUE. This means that if the VLookUp doesn't find the exact thing
it's looking for, it says, "what the hell" and just chooses the
closest thing it can find. This may be causing your problems. In each
of your VLookup's, you need to add FALSE as the fourth thingy.

ie.
=IF(ISERROR(VLOOKUP($B4,'P:\WIPSUM\[WIPSUM8.xls]WIPSUM8'!$B$2:$AJ
$2000,21,FALSE))­,"0",VLOOKUP($B4,'P:\WIPSUM\[WIPSUM8.xls]WIPSUM8'!$B
$2:$AJ$2000,21,FALSE))

=IF(ISERROR(VLOOKUP($B4,'P:\WIPSUM\[WIPSUM8.xls]WIPSUM8'!$B$2:$AJ
$2000,8,FALSE)),­"",VLOOKUP($B4,'P:\WIPSUM\[WIPSUM8.xls]WIPSUM8'!$B
$2:$AJ$2000,8,FALSE))

Regards - Dave.

 

[SSJ]

Thank you Dave.

It worked perfectly. Indeed it was missing 'FALSE' in the formula.

Regards
SJ

Hi SJ,
VLookUp has a 4th thingy, which if omitted, (as in your case) defaults
to TRUE. This means that if the VLookUp doesn't find the exact thing
it's looking for, it says, "what the hell" and just chooses the
closest thing it can find. This may be causing your problems. In each
of your VLookup's, you need to add FALSE as the fourth thingy.

ie.
=IF(ISERROR(VLOOKUP($B4,'P:\WIPSUM\[WIPSUM8.xls]WIPSUM8'!$B$2:$AJ
$2000,21,FALSE))­,"0",VLOOKUP($B4,'P:\WIPSUM\[WIPSUM8.xls]WIPSUM8'!$B
$2:$AJ$2000,21,FALSE))

=IF(ISERROR(VLOOKUP($B4,'P:\WIPSUM\[WIPSUM8.xls]WIPSUM8'!$B$2:$AJ
$2000,8,FALSE)),­"",VLOOKUP($B4,'P:\WIPSUM\[WIPSUM8.xls]WIPSUM8'!$B
$2:$AJ$2000,8,FALSE))

Regards - Dave.

 

[Dave]

You're welcome.