Removing #NUM error when using array formula

[LckySevan]

I have the following array forumula:


=INDEX($A$1:$B$251, SMALL(IF($A$1:$A$251="amy",ROW($A$1:$A$251)),ROW(4:4)),2)


I want to remove the #NUM! errors from my list, I've attempted to construct a IF(ISERROR) condition but have been unsuccessful. Any advice would be appreciated.

 

[mercatog]

Hi


For Excel 2007 and later, try this array formula(Validate by Ctrl Shift Enter)

=IFERROR(INDEX($A$1:$B$251, SMALL(IF($A$1:$A$251="amy",ROW($A$1:$A$251)),ROW(4:4)),2),"")

Regards

 

[LckySevan]

Sorry, I forgot to mention that I have excel 2003. I still tried the formula above just in case I was lucky, but it didn't work.

 

[SirJB7]

Hi, LckySevan!


Try this:

=IF(ISERROR(INDEX($A$1:$B$251, SMALL(IF($A$1:$A$251="amy",ROW($A$1:$A$251)),ROW(4:4)),2)),"",INDEX($A$1:$B$251, SMALL(IF($A$1:$A$251="amy",ROW($A$1:$A$251)),ROW(4:4)),2))


Still array formula so enter it with Ctrl-Shift-Enter instead of Enter.


Regards!

 

[LckySevan]

That worked SirJB7! Thanks!

 

[SirJB7]

Hi, LckySevan!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!