Same forumula returns both 0 & N/A

[If_Excel]

Here is the formula I am using. It's virutally the same except for th
logical test at the beginning of IF

=IF(B2=$O$1,((VLOOKUP(A2,Reference!$A$16:$C$35,(IF(VLOOKUP($A$1,Reference!$A$2:$C$13,3,FALSE)=1,2,3)),FALSE))/52*(VLOOKUP($A$1,Reference!$A$2:$B$13,2,FALSE))*C2),0

In this case is returns 0 when B2 is empty.

=IF(B2<>$O$1,((VLOOKUP(A2,Reference!$A$16:$C$35,(IF(VLOOKUP($A$1,Reference!$A$2:$C$13,3,FALSE)=1,2,3)),FALSE))/52*(VLOOKUP($A$1,Reference!$A$2:$B$13,2,FALSE))*E2),0

In this case, when B2 is empty it results in N/A. Ideally it woul
return 0 or - (without custom field formatting) or Hiding Error results

$O$1 is text: Book Medi

Any ideas

 

[Ron Rosenfeld]

Here is the formula I am using. It's virutally the same except for the
logical test at the beginning of IF.

=IF(B2=$O$1,((VLOOKUP(A2,Reference!$A$16:$C$35,(IF(VLOOKUP($A$1,Reference!$A$2:$C$13,3,FALSE)=1,2,3)),FALSE))/52*(VLOOKUP($A$1,Reference!$A$2:$B$13,2,FALSE))*C2),0)

In this case is returns 0 when B2 is empty.

But what does it return if B2=$O$1 ? If it returns N/A, you have the answer to your problem. There is no match for the VLOOKUP.

=IF(B2<>$O$1,((VLOOKUP(A2,Reference!$A$16:$C$35,(IF(VLOOKUP($A$1,Reference!$A$2:$C$13,3,FALSE)=1,2,3)),FALSE))/52*(VLOOKUP($A$1,Reference!$A$2:$B$13,2,FALSE))*E2),0)

In this case, when B2 is empty it results in N/A. Ideally it would
return 0 or - (without custom field formatting) or Hiding Error results.

$O$1 is text: Book Media

Test each clause of the IF statement separately to see where you error lies. If the error is in the VLOOKUP, you will need to test for or hide the error.

 

[If_Excel]

Test each clause of the IF statement separately to see where you erro
lies. If the error is in the VLOOKUP, you will need to test for or hid
the error

Yep, good advice. All my vlookups worked with values, but I finall
figured out why it was failing. Appreciate your response