Forum Discussion

Queenie Lai's avatar
Queenie Lai
Copper Contributor
Jan 02, 2019

Excel formula help

I am working on the following formula for a report. Could anyone please help figure out why the following formula does not work when I added 'AND' to the formula?

 

The BOLD part is not working:

=IFERROR(INDEX(Mastersheet2018!$C:$C,SMALL(IF(AND(Mastersheet2018!$L:$L=$B$3,Mastersheet2018!$A:$A=$M$3),ROW(Mastersheet2018!$C:$C)-MIN(ROW(Mastersheet2018!$C:$C))+1),ROWS($C$5:C5))),"")

 

However, without 'AND', this formula works perfectly.

=IFERROR(INDEX(Mastersheet2018!$C:$C,SMALL(IF(Mastersheet2018!$L:$L=$B$3,ROW(Mastersheet2018!$C:$C)-MIN(ROW(Mastersheet2018!$C:$C))+1),ROWS($C$5:C10))),"")

 

Please help. Thank you.

  • Hi,

     

    Instead of

    AND(Mastersheet2018!$L:$L=$B$3,Mastersheet2018!$A:$A=$M$3)

    use it numeric equivalent

    (Mastersheet2018!$L:$L=$B$3)*(Mastersheet2018!$A:$A=$M$3)

    and you don't need

    -MIN(ROW(Mastersheet2018!$C:$C))+1

    since first part is always equal to 1, result is always zero.

    Plus in first formula you use ROWS($C$5:C5), in second one ROWS($C$5:C10), perhaps they are copy pasted from different rows.

    And in general you may use non-array variant of the formula

    =IFERROR(INDEX(Mastersheet2018!$C:$C,AGGREGATE(15,6,1/(Mastersheet2018!$L:$L=$B$3)/(Mastersheet2018!$A:$A=$M$3)*ROW(Mastersheet2018!$C:$C),ROWS($C$5:C5))),"")
  • Hi,

     

    Instead of

    AND(Mastersheet2018!$L:$L=$B$3,Mastersheet2018!$A:$A=$M$3)

    use it numeric equivalent

    (Mastersheet2018!$L:$L=$B$3)*(Mastersheet2018!$A:$A=$M$3)

    and you don't need

    -MIN(ROW(Mastersheet2018!$C:$C))+1

    since first part is always equal to 1, result is always zero.

    Plus in first formula you use ROWS($C$5:C5), in second one ROWS($C$5:C10), perhaps they are copy pasted from different rows.

    And in general you may use non-array variant of the formula

    =IFERROR(INDEX(Mastersheet2018!$C:$C,AGGREGATE(15,6,1/(Mastersheet2018!$L:$L=$B$3)/(Mastersheet2018!$A:$A=$M$3)*ROW(Mastersheet2018!$C:$C),ROWS($C$5:C5))),"")
    • Queenie Lai's avatar
      Queenie Lai
      Copper Contributor

      The formula works perfectly for these 2 criteria: Mastersheet2018!$L:$L=$B$3 and Mastersheet2018!$A:$A=$M$3

       

      If I want to add a third criteria (Mastersheet2018!$AB:$AB<>0) can I still use the following formula?

       

      =IFERROR(INDEX(Mastersheet2018!$C:$C,AGGREGATE(15,6,1/(Mastersheet2018!$L:$L=$B$3)/(Mastersheet2018!$A:$A=$M$3)*ROW(Mastersheet2018!$C:$C),ROWS($C$5:C5))),"")

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP

        Yes, you may use as many criteria as you need, like

        1/criteria1/criteria2/criteria3

        If any one of them is FALSE above returns an error (division on zero) and AGGREGATE with second parameter 6 ignores all errors making selection only from elements where all criteria are TRUE (division on 1)

Resources