 SOLVED

Highlighted

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

3 Replies
Highlighted
Best Response confirmed by Queenie Lai (Occasional Contributor)
Solution

# Re: Excel formula help

Hi,

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

# Re: Excel formula help

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

Highlighted

# Re: Excel formula help

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)