ISERROR partially working

Column F starts off with ISNA tests and then changes to ISERROR in F4 ????????????
Surely these both can't be correct?

Originally Posted by jimbokeep

The ISERROR solution I tried to use removes the #VALUE, if I enter a number in the blank cell, and then delete that number. But it won't work straight away if there is a blank cell.

Your formula is:

Please Login or Register  to view this content.

First, VLOOKUP always succeeds because obviously A8 can be found in the first column of the lookup range, which is column A; namely, at least in row 8. So ISERROR always returns FALSE.

Second, the problem is: the cells that appear blank in columns C and E contain the null string ("") due the formula of the form:

=IF(ISNA(RANK(B8,$B$2:$B$14,0)),"",RANK(B8,$B$2:$B$14,0))

Thus, the formula is effectively ="" - "". That results in a #VALUE error because we cannot use non-numeric text in arithmetic expressions.

If you are saving the file as ".xlsx" (not ".xls"), you can write the following:

Please Login or Register  to view this content.

Alternatively (works in a ".xls" file as well):

Please Login or Register  to view this content.

Alternatively, I wonder if the following works:

=IF(OR(C8="", E8=""), "", C8 - E8)

It is unclear to me why you are using VLOOKUP, which ostensibly matches the same row as the lookup value (A8) -- unless the value in A8 might also occur in an earlier row, and your intent is to find the first row with that value. [PS.... But I assume that is not likely, since you are searching a pivot table.]

Aside.... Note the subtle change from A:C to $A$2:$C$1000. Generally, it is imprudent to use whole-column references, especially if the lookup might fail, because that might cause Excel to process all 1+ million rows in a ".xlsx" file. Arguably, it is okay in this particular case because, as noted, VLOOKUP always succeeds. But it is a bad habit. Instead, choose a limited range that allows for reasonable growth in the data. I assume you will never have more than 1000 rows of data. You might prefer 10,000 or 100,000 or anything in between or beyond. But I suspect that 1+ million is unlikely.