it returns #NUM! when there are no more records to display.

If the formula returns #NUM! it can only come from this:

SMALL(C1:C500,ROWS(IV$1:IV1))

So, you only need to trap that portion of the formula.

--
Biff
Microsoft Excel MVP


"Ms-Exl-Learner" wrote in message
...
=IF(ISERROR(INDEX(INDIRECT("$a$1:$a$500"),MATCH(SM ALL($C$1:$C$500,ROWS($IV$1:IV1)),$C$1:$C$500,0))), "",INDEX(INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1: $C$500,ROWS($IV$1:IV1)),$C$1:$C$500,0)))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Gotroots" wrote:


The following formula works fine however it returns #NUM! when there are
no
more records to display. I do not want the error to return.

=INDEX(INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C $500,ROWS($IV$1:IV1)),$C$1:$C$500,0))