Forum Discussion
Solved
Excel formula help
I have the following formula which works fine {=IFERROR(INDEX(Pay!$A$51:$C$54,SMALL(IF((Pay!$A$51:$A$54=$A$6)*(Pay!$C$51:$C$54="M"),ROW(Pay!$A$51:$A$54)-50),ROWS($A$7:A7)),2),"")} How to modify ...
Here OR will always return TRUE,
Pay!$C$51:$C$54={"M","B"}shall work.
For that sample
array formula is
=IFERROR(INDEX($A$51:$C$54,SMALL(IF(($A$51:$A$54=$A$6)*($C$51:$C$54={"M","B"}),ROW($A$51:$A$54)-50),ROWS($A$7:A7)),2),"")or even better regular one
=IFERROR(INDEX($A$51:$C$54,AGGREGATE(15,6,1/($A$51:$A$54=$A$6)/($C$51:$C$54={"M","B"})*(ROW($A$51:$A$54)-50),ROWS($A$7:A7)),2),"")Attached.
Here OR will always return TRUE,
Pay!$C$51:$C$54={"M","B"}
shall work.
For that sample
array formula is
=IFERROR(INDEX($A$51:$C$54,SMALL(IF(($A$51:$A$54=$A$6)*($C$51:$C$54={"M","B"}),ROW($A$51:$A$54)-50),ROWS($A$7:A7)),2),"")
or even better regular one
=IFERROR(INDEX($A$51:$C$54,AGGREGATE(15,6,1/($A$51:$A$54=$A$6)/($C$51:$C$54={"M","B"})*(ROW($A$51:$A$54)-50),ROWS($A$7:A7)),2),"")
Attached.
Thanks you both