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 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
- Nice one Sergei and best wishes for your Excel work Saad!
Cheers
Damien
