Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
VLOOKUP FORMULA EVALUATION NEEDED
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:\WI PSUM\[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:\WIPS UM\[WIPSUM8.xls]WIPSUM8'!$B$2:$AJ$2000,8)) Thanks SJ |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
VLOOKUP FORMULA EVALUATION NEEDED
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
VLOOKUP FORMULA EVALUATION NEEDED
Thank you Dave.
It worked perfectly. Indeed it was missing 'FALSE' in the formula. Regards SJ "Dave" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
VLOOKUP FORMULA EVALUATION NEEDED
You're welcome.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup evaluation | Excel Worksheet Functions | |||
VLOOKUP formula needed | Excel Discussion (Misc queries) | |||
Vlookup formula needed? | Excel Worksheet Functions | |||
IF formula - evaluation shows it should work but... | Excel Worksheet Functions | |||
Vlookup using a substring for evaluation? | Excel Worksheet Functions |