SOLVED

Excel formula help

Copper Contributor

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.

3 Replies
best response confirmed by Queenie Lai (Copper Contributor)
Solution

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))),"")

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))),"")

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)

1 best response

Accepted Solutions
best response confirmed by Queenie Lai (Copper Contributor)
Solution

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))),"")

View solution in original post