use AND in ARRAY formula

[Rakesh Gupta]

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

 

[Mike H]

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

 

[Bernard Liengme]

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(A1>0, B1>1).... is the same as IF((A1>0)*( B1>1).... 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" <[email protected]> wrote in messageI 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>>>

 

[Shane Devenshire]

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.