use AND in ARRAY formula
 

I am using the below formula and it is working fine.

{=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$F$3,ROW($B $1:$B$10)),1))}

Now I want to check two conditions in this array formula.

for e.g.
IF($A$1:$A$10=$F$3
and
IF($C$1:$C$10=$E$3

Please Help

use AND in ARRAY formula
 

Hi,

Does this do what you want?

=INDEX($B$1:$B$10,MATCH(1,($A$1:$A$10=$F$3)*($C$1: $C$10=$E$3),0))

Mike

"Rakesh Gupta" wrote:

I am using the below formula and it is working fine.

{=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$F$3,ROW($B $1:$B$10)),1))}

Now I want to check two conditions in this array formula.

for e.g.
IF($A$1:$A$10=$F$3
and
IF($C$1:$C$10=$E$3

Please Help

use AND in ARRAY formula
 

Use cannot use logical functions (AND, OR, NOT) within an array formula
But AND can be replaced by multiplication and OR by addition
Example IF(AND(A10, B11).... is the same as IF((A10)*( B11).... because
you will get either 1 (TRUE) or 0 (FALSE)

So I would expect this to work:
{=INDEX($B$1:$B$10,SMALL(IF(($A$1:$A$10=$F$3)*(($C $1:$C$10=$E$3),ROW($B$1:$B$10)),1))}best wsihes--Bernard V LiengmeMicrosoft Excel MVPhttp://people.stfx.ca/bliengmeremove caps from email"Rakesh Gupta" wrote in ... I am using the below formula and it is working fine. {=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$F$3,ROW($B $1:$B$10)),1))} Now I want to check two conditions in this array formula. for e.g. IF($A$1:$A$10=$F$3 and IF($C$1:$C$10=$E$3 Please Help

use AND in ARRAY formula
 

Hi,

Here is another version

=INDEX(B1:B10,MIN(IF((A1:A10=F3)*(C1:C10=E3),ROW(1 :10))))

If you are not copying the formula then you don't need the absolute cell
references. Note also that SMALL(x,1) is the same as MIN at least in your
example.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rakesh Gupta" wrote:

I am using the below formula and it is working fine.

{=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$F$3,ROW($B $1:$B$10)),1))}

Now I want to check two conditions in this array formula.

for e.g.
IF($A$1:$A$10=$F$3
and
IF($C$1:$C$10=$E$3

Please Help