On May 2, 4:42 pm, Mike H wrote:
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!$F5000 0)-ROW(Sheet1!$A2)+1,ROW(Sheet1!$F50000)+1),2),2)

Mike

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(Sh eet1!$A2:Sheet1!
$F50000=$A5,ROW(Sheet1!$A2:Sheet1!$F50000)-ROW(Sheet1!
$A2)+1,ROW(Sheet1!$F50000)+1),2),2))

HTH
Kostis Vezerides