help with array formula

[p4nny]

Hi

I would like to add a second "logical test" to the IF statement to add YELLOW. so IF(OR(RED=b3:b10, YELLOW = b3:b10

I've tried SMALL(IF(OR but couldn't get that too work

=INDEX($C$3:$C$10, SMALL(IF("Red"=$B$3:$B$10, ROW($B$3:$B$10)-MIN(ROW($B$3:$B$10))+1, ""), ROW(A2)))

Much appreciated
Anthony

 

[Dave Patton]

Excel 2010
	A	B	C	D
1			TRUE	TRUE
2
4a

Cell Formulas
Range	Formula
D1	=SUM(COUNTIF(B3:B10,{"Red","Yellow"}))>0
C1	{=OR(B3:B10="Yellow",B3:B10="Red")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[p4nny]

Hi, appreciate your help. I'm looking to return corresponding values in C3:C10 hence the INDEX. So the formula I posted works, but I'm looking to add a second condition.

Many thanks

 

[Jonmo1]

This is just personal preference, It seems wrong to me (though I know it's not really)
when I see x=range..
To me it should be range=x

Anyway, that's just opinion. But I've reversed it in the formula..

Try this.
=INDEX($C$3:$C$10, SMALL(IF(($B$3:$B$10="Red")+($B$3:$B$10="Yellow"), ROW($B$3:$B$10)-MIN(ROW($B$3:$B$10))+1, ""), ROW(A2)))

 

[p4nny]

works great, thank you

 

[Jonmo1]

You're welcome.