Forum Discussion
Queenie Lai
Jan 02, 2019Copper Contributor
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 LaiCopper 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))),"")
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)