excelexcel-formulaexcel-2016

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

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.

Solution

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

    `