SMALL Function Problem

[as9178]

Hi all,

I got given a formula a while back on here which solved a problem but another one has come up.

The formula was this

=IFERROR(INDEX(Trialists!$D$2:$D$500, SMALL(IF(E$2=Trialists!$B$2:$B$500, ROW(Trialists!$D$2:$D$500)-1,""), ROW()-2)),"")

My problem is the SMALL function above

SMALL(IF(E$2=Trialists!$B$2:$B$500

This provides the vertical list when the condition of U19M is met from the column on the other tab.

However my problem is now that I need this to meet two conditions.

One for what squad there are in now like the U19M and the other being whether they are paying a standing order which is listed as Yes in column on the other tab

Thanks

Andrew

[Pete_UK]

You need to change your formula in E3 to this:

=IFERROR(INDEX(Trialists!$D$2:$D$500, SMALL(IF((E$2=Trialists!$B$2:$B$500)*(Trialists!$A$2:$A$500="Yes"), ROW(Trialists!$D$2:$D$500)-1,""), ROW()-2)),"")

(changes in red).

Remember that this is an array formula, so you need to commit it using Ctrl-Shift-Enter, instead of the usual Enter.

Then you can copy it down.

Hope this helps.

Pete

[as9178]

Hi Pete,

=IFERROR(INDEX(Trialists!$D$2:$D$500, SMALL(IF(E$2=Trialists!$B$2:$B$500*(Trialists!$A$2:$A$500="Yes"), ROW(Trialists!$D$2:$D$500)-1,""), ROW()-2)),"")

Is the formula I have, however it has now returned a blank cell?

I have reattached the file if you can help again?

[Pete_UK]

Put brackets around the first term in the IF (see my red colours).

Pete

[as9178]

Splendid,thanks