Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP displaying "NO ANSWER" instead of "0"
When using this formula everything works great as long as an answer can be
found but when an answer can't be found the answer displays as a "0". What do I have to add to the formula to get it to display a "No Answer" when the answer can't be found? =IF(ISERROR(VLOOKUP(B3,Master!$A$3:$O$4180,2,FALSE )),IF(ISERROR(VLOOKUP(B3,Master!$E$3:$O$4180,2,FAL SE)),IF(ISERROR(VLOOKUP(B3,Master!$I$3:$O$4180,2,F ALSE)),VLOOKUP(B3,Master!$I$3:$O$4180,2,FALSE)),VL OOKUP(B3,Master!$E$3:$O$4180,2,FALSE)),VLOOKUP(B3, Master!$A$3:$O$4180,2,FALSE)) Thanks Ksean |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP displaying "NO ANSWER" instead of "0"
The source cell was totall empty, once something was put in it the formula
returned the "NO ANSWER" just as I wanted it to. "ksean" wrote: When using this formula everything works great as long as an answer can be found but when an answer can't be found the answer displays as a "0". What do I have to add to the formula to get it to display a "No Answer" when the answer can't be found? =IF(ISERROR(VLOOKUP(B3,Master!$A$3:$O$4180,2,FALSE )),IF(ISERROR(VLOOKUP(B3,Master!$E$3:$O$4180,2,FAL SE)),IF(ISERROR(VLOOKUP(B3,Master!$I$3:$O$4180,2,F ALSE)),VLOOKUP(B3,Master!$I$3:$O$4180,2,FALSE)),VL OOKUP(B3,Master!$E$3:$O$4180,2,FALSE)),VLOOKUP(B3, Master!$A$3:$O$4180,2,FALSE)) Thanks Ksean |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP displaying "NO ANSWER" instead of "0"
To display "No answer" when the lookup value is not found, do it this way:
=if(isna(vlookup(...)),"No answer",vlookup(...)) Regards, Fred. "ksean" wrote in message ... When using this formula everything works great as long as an answer can be found but when an answer can't be found the answer displays as a "0". What do I have to add to the formula to get it to display a "No Answer" when the answer can't be found? =IF(ISERROR(VLOOKUP(B3,Master!$A$3:$O$4180,2,FALSE )),IF(ISERROR(VLOOKUP(B3,Master!$E$3:$O$4180,2,FAL SE)),IF(ISERROR(VLOOKUP(B3,Master!$I$3:$O$4180,2,F ALSE)),VLOOKUP(B3,Master!$I$3:$O$4180,2,FALSE)),VL OOKUP(B3,Master!$E$3:$O$4180,2,FALSE)),VLOOKUP(B3, Master!$A$3:$O$4180,2,FALSE)) Thanks Ksean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
correct syntax for nesting "if", "and", and "vlookup"....if possib | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |