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

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

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