How can I control column number in Excel INDEX function based on criteria?

Question

I have a table like this:

A	B	C	D
apple	banana	0	0
boat		0	0
	orange	0	1
car	lemon	1	1
cat		0	0
	dog	0	0
goat		1	0
red		1	1
blue	green	0	0
lamp		0	0
	garden	0	0

Using an array formula, I want to list each row where both C and D are 0. If A has a value I want to use that, but if A is blank I want to use B.

I've managed to google my way as far as this:

{=IFERROR(INDEX($A$1:$B$11;SMALL(IFERROR(IF(($C$1:$C$11+$D$1:$D$11=0);ROW($A$2:$A$12)-1);FALSE);ROW(1:1));1);"")}

This lists the correct rows, but I can't get it to work with the value in B when A is missing. In my mind it should be possible to just change the column number argument in the INDEX function from a 1 to a 2 based on whether A is blank or not, but I can't get it to work. This for instance does nothing:

{=IFERROR(INDEX($A$1:$B$11;SMALL(IFERROR(IF(($C$1:$C$11+$D$1:$D$11=0);ROW($A$2:$A$12)-1);FALSE);ROW(1:1));IF(ISBLANK($A1);2;1));"")}

I understand there's something fundamental I'm missing here. Any help is appreciated! If there are better approaches to this I'm all ears as well.

Answer 1

Use an IF in the INDEX to return the correct array of values, then I prefer AGGREGATE to SMALL.

This may require the use of Ctrl-Shift-Enter instead of just Enter when confirming the formula:

=IFERROR(INDEX(IF($A$1:$A$11="",$B$1:$B$11,$A$1:$A$11),AGGREGATE(15,7,ROW($C$1:$C$11)/(($C$1:$C$11=0)*($D$1:$D$11=0)),ROW(A1))),"")

Answer 2

You can try this formula:

=TRANSPOSE(TOROW(IF((C1:C11+D1:D11)=0,IF(A1:A11=0,B1:B11,A1:A11),1/0),3))

If TOROW not avail then

=INDEX(IF(A1:A11<>"",A1:A11,B1:B11),SMALL(IF((C$1:C$11)+(D$1:D$11)=0,ROW(C1:C11),999),ROW(C1:C11)))

NOTE: if more than 999 lines are in use than increase this value to more than the max. number of rows in the array. At then end of the result the empty values will have #REF error, which can be eliminated with another IF statement.