VLOOKUP FORMULA EVALUATION NEEDED

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SSJ - 27 Mar 2008 16:46 GMT

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

Reply to this Message

Dave - 27 Mar 2008 22:53 GMT

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.

Reply to this Message

SSJ - 28 Mar 2008 13:23 GMT

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.

Reply to this Message

Dave - 29 Mar 2008 17:15 GMT

You're welcome.

Reply to this Message