Remove #NUM! ISERROR when using array formulas

[Struggling in Sheffield]

Hi all,
I’m using the following array formula to copy certain rows of data from a
master table into smaller tables located below the master:

{etc}

{=INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C913,ROW($C$3:$C$902)),3),COLUMN()))}

{=INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C913,ROW($C$3:$C$902)),2),COLUMN()))}

{=INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C913,ROW($C$3:$C$902)),1),COLUMN()))}

I can then do various calculations using the smaller tables.

However where the smaller tables aren’t completely filled with data I’m
getting a #NUM! error, which unfortunately plays havoc with some of my
calculations.

Normally I would just use something like the formula below to leave any
error cells blank:

=IF(ISERROR(FORMULA),â€â€,FORMULA)

However I’m not sure how to marry the two together when using an array
formula.

Would appreciate any help to break the impasse.

Cheers,
Steve.

 

[T. Valko]

The portion of the formula that will generate the #NUM! error is:

LARGE(IF($C$3:$C$902=C913,ROW($C$3:$C$902)),n)

Where n = instance number. If there isn't an nth instance then you get
#NUM!.

So, you can just trap that portion like this (still array entered):

=IF(ISERROR(LARGE(IF($C$3:$C$902=C913,ROW($C$3:$C$902)),n)),"",INDIRECT(ADDRESS(LARGE(IF($C$3:$C$902=C913,ROW($C$3:$C$902)),n),COLUMN())))

 

[Struggling in Sheffield]

Hi Biff,
Thanks very much for that, tried many variations on the theme but just
couldn't hit the bullseye.
All the best.
Steve.

 

[T. Valko]

You're welcome. Thanks for the feedback!