Array Lookup

[nathansav]

HI,

I am using the following array formula to get all the details from a table for all tasks allocated to a specific person (F1)

However, i wish to check the status also which is in column D

=IFERROR(INDEX($A:$A,SMALL(IF($B:$B=$F$1,ROW($B:$B)),ROWS($A$2:$A15))),"")

I've tried,

=IFERROR(INDEX($A:$A,SMALL(IF(AND($B:$B=$F$1,$D:$D="Open"),ROW($B:$B)),ROWS($A$2:$A15))),"")


But this returns 0's for the row found.

Thanks.

[benishiryo]

hi nathan. the AND doesn't work well here as it returns only a single result. also, don't range up the whole column for the array portion. it will slow down calculations. use:
=IFERROR(INDEX($A:$A,SMALL(IF($B$1:$B$100=$F$1,IF($D$1:$D$100="open",ROW($B$1:$B$100))),ROWS($A$2:$A15))),"")

[XOR LX]

Hi,

The correct syntax would be:

=IFERROR(INDEX($A:$A,SMALL(IF($B:$B=$F$1,IF($D:$D="Open",ROW($B:$B))),ROWS($A$2:$A15))),"")

though I recommend that you don't use entire column referencing ($A:$A, $D:$D, etc.) in array formulas.

Regards

[Izandol]

AND will not work for this array formula - you may try:

=IFERROR(INDEX($A:$A,SMALL(IF($B:$B=$F$1,IF($D:$D="Open",ROW($B:$B))),ROWS($A$2:$A15))),"")

[XOR LX]

Snap, snap, snap!!

[nathansav]

Thanks guys, the complexities of the array formula.

This was a mock up, so hence the column full names, these will be replaced by dynamic named ranges in the full version.

Thanks

[XOR LX]

You're welcome.

By the way, your profile says 2003 but assume you must be using a later version for IFERROR() to work? If so, could you update your profile?

Regards

[kvsrinivasamurthy]

Try this
=IFERROR(INDEX($A:$A,SMALL(IF(($B:$B=$F$1)*($D:$D="Open"),ROW($B:$B)),ROWS($A$2:$A15))),"")