Forum Discussion

saad soliman's avatar
saad soliman
Copper Contributor
Jun 18, 2018
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 the BOLD condition to select the Rows which include the value "M" OR "B"?

 

Thanks for your help

Formulas & Functions
Need Help
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 19, 2018

    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.

5 Replies

Sort By
  • Damien_Rosario's avatar
    Damien_Rosario
    Silver Contributor
    Jun 19, 2018
    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
    • saad soliman's avatar
      saad soliman
      Copper Contributor
      Jun 19, 2018

      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:

       

      PositionNameMorning
      BaristaAndrewB
      BaristaEmmanuelN
      BaristaEddie 
      BaristaAyaM
      Barista  

       

      What I want to achieve is retrieving the rows which contain "B" or "M" in column C.

       

      Looking forward to your reply.

       

      Thanks

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Jun 19, 2018

        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.

Resources