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.
Hi Saad
I hope I have understood, but is this what you were looking for?
=IFERROR(INDEX(Pay!$A$51:$C$54,SMALL(IF((Pay!$A$51:$A$54=$A$6)*(OR(Pay!$C$51:$C$54="M", Pay!$C$51:$C$54="B")),ROW(Pay!$A$51:$A$54)-50),ROWS($A$7:A7)),2),"")
If not, can you please post a sample for us to see what you are after?
Cheers
Damien
Hi Damien,
Thanks for your reply. In fact I have tried this syntax, but it does not give the required result. It retrieves all rows. A sample Array I am using is:
Position Name Morning Barista Andrew B Barista Emmanuel N Barista Eddie Barista Aya M Barista What I want to achieve is retrieving the rows which contain "B" or "M" in column C.
Looking forward to your reply.
Thanks
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
