Same forumula returns both 0 & N/A
 

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.

=IF(B2<$O$1,((VLOOKUP(A2,Reference!$A$16:$C$35,(I F(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

Any ideas?

Same forumula returns both 0 & N/A
 

On Thu, 4 Apr 2013 17:03:50 +0000, If_Excel wrote:


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,(I F(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.

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