SOLVED
Home

Excel formula help

Queenie Lai
New 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
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)

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies