Modify to eliminate #NUM! error
 

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))

Modify to eliminate #NUM! error
 

=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))

Modify to eliminate #NUM! error
 

The usual way of avoiding such errors being displayed is
=IF(ISERROR(yourformula),"",yourformula), hence try
=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)))
--
David Biddulph

"Gotroots" wrote in message
...

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))

Modify to eliminate #NUM! error
 

Depends on what you have in ColA and colC

If ColC is having numerics...try
=IF(COUNT($C$1:$C$500)<ROWS($IV$1:IV1),"",(INDEX(I NDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C$500,ROW S($IV$1:IV1)),$C$1:$C$500,0))))

'try if colC will have both text and numerics
=IF(COUNTA($C$1:$C$500)<ROWS($IV$1:IV1),"",(INDEX( INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C$500,RO WS($IV$1:IV1)),$C$1:$C$500,0))))


--
Jacob


"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))

Modify to eliminate #NUM! error
 

That is one heck of a formula! It does what it says on the tin :)

Thank you (Ms-Exl-Learner)

"Ms-Exl-Learner" wrote:

=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))

Modify to eliminate #NUM! error
 

Incase you are using XL2007 checkout help on the function IFERROR()

--
Jacob


"Jacob Skaria" wrote:

Depends on what you have in ColA and colC

If ColC is having numerics...try
=IF(COUNT($C$1:$C$500)<ROWS($IV$1:IV1),"",(INDEX(I NDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C$500,ROW S($IV$1:IV1)),$C$1:$C$500,0))))

'try if colC will have both text and numerics
=IF(COUNTA($C$1:$C$500)<ROWS($IV$1:IV1),"",(INDEX( INDIRECT("$a$1:$a$500"),MATCH(SMALL($C$1:$C$500,RO WS($IV$1:IV1)),$C$1:$C$500,0))))


--
Jacob


"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))

Modify to eliminate #NUM! error
 

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))