Supress #Ref! error

M

Mike H

The formula below finds the second occurence of a value in a table and reurns
another value and works OK. The problem is that there isn't always a second
occurence and it returns a #REF! error whicj looks a mess. Is there any way
of supressing the error?

=INDEX(Sheet1!$A2:Sheet1!$F50000,SMALL(IF(Sheet1!$A2:Sheet1!$F50000=$A5,ROW(Sheet1!$A2:Sheet1!$F50000)-ROW(Sheet1!$A2)+1,ROW(Sheet1!$F50000)+1),2),2)

Mike
 
V

vezerid

The formula below finds the second occurence of a value in a table and reurns
another value and works OK. The problem is that there isn't always a second
occurence and it returns a #REF! error whicj looks a mess. Is there any way
of supressing the error?

=INDEX(Sheet1!$A2:Sheet1!$F50000,SMALL(IF(Sheet1!$A2:Sheet1!$F50000=$A5,ROW(Sheet1!$A2:Sheet1!$F50000)-ROW(Sheet1!$A2)+1,ROW(Sheet1!$F50000)+1),2),2)

Mike
Click to expand...

Mike,
the offending part is the call to SMALL, which might return an error:
SMALL(IF(Sheet1!$A2:Sheet1!$F50000=$A5,ROW(Sheet1!$A2:Sheet1!$F50000)-
ROW(Sheet1!$A2)+1,ROW(Sheet1!$F50000)+1),2)

You need to trap this expression:
=IF(ISERROR(SMALL(IF(Sheet1!$A2:Sheet1!$F50000=$A5,ROW(Sheet1!
$A2:Sheet1!$F50000)-ROW(Sheet1!$A2)+1,ROW(Sheet1!$F50000)+1),
2)),"",INDEX(Sheet1!$A2:Sheet1!$F50000,SMALL(IF(Sheet1!$A2:Sheet1!
$F50000=$A5,ROW(Sheet1!$A2:Sheet1!$F50000)-ROW(Sheet1!
$A2)+1,ROW(Sheet1!$F50000)+1),2),2))

HTH
Kostis Vezerides
 
B

Bob Umlas

You can select all cells, use Format/Conditional Formatting, change "Cell
Value Is" to "Formula Is", then enter this:
=ISERROR(A1) then click the format button, Font tab, select a white font.
Bob Umlas
Excel MVP
 
H

Harlan Grove

....

While #N/A, #VALUE!, #DIV/0!, #NUM! and #NULL! errors can be annoying,
#REF! and #NAME? errors usually highlight serious logic errors.
Masking these two errors is usually a bad idea.
the offending part is the call to SMALL, which might return an error:
SMALL(IF(Sheet1!$A2:Sheet1!$F50000=$A5,ROW(Sheet1!$A2:Sheet1!$F50000)-
ROW(Sheet1!$A2)+1,ROW(Sheet1!$F50000)+1),2)
Click to expand...

First, while Sheet1!$A2:Sheet1!$F50000 is syntactically valid, it
should be rewritten as a single range reference rather than a range
expression, i.e., as Sheet1!$A2:$F50000.

For the OP: do you mean to search all 6 columns in Sheet1!A2:F50000
for matches against A5 or just Sheet1!A2:A50000?

For vezerid: testing is good. Testing would have shown that this SMALL
call would NEVER return errors because the IF call ALWAYS returns
49999 numeric values. Testing would have shown that the problem occurs
when there are fewer than two values in Sheet1!A2:F50000 equal to A5,
in which case the SMALL call would return ROW(Sheet1!$F50000)+1, or
50001, with is NOT an error value itself but IS greater than the max
row index of Sheet1!A2:F50000.

As I said above, #REF! is almost always due to serious logic errors.
You need to trap this expression:
=IF(ISERROR(SMALL(IF(Sheet1!$A2:Sheet1!$F50000=$A5,ROW(Sheet1!
$A2:Sheet1!$F50000)-ROW(Sheet1!$A2)+1,ROW(Sheet1!$F50000)+1),2)),"",
INDEX(Sheet1!$A2:Sheet1!$F50000,SMALL(IF(Sheet1!$A2:Sheet1!$F50000=$A5,
ROW(Sheet1!$A2:Sheet1!$F50000)-ROW(Sheet1!$A2)+1,
ROW(Sheet1!$F50000)+1),2),2))
Click to expand...

Trapping the cause of the error is necessary, but there are shorter,
narrower, more efficient and (why not?) correct traps.

=IF(COUNTIF(Sheet1!$A2:$F50000,$A5)<2,"",INDEX(Sheet1!$A2:$F50000,
SMALL(IF(Sheet1!$A2:$F50000=$A5,ROW(Sheet1!$A2:Sheet1!$F50000)
-ROW(Sheet1!$A2)+1),2),2))
 
Top