Equivalent of Google spreadsheet's arrayformula in Excel

Question

Input

1   0
7   77
6   66

Expected Output

7   66

Solution in Google Spreadsheet

= arrayformula(if(B2:C2,B3:C3,B4:C4))

How to do that in Excel?

Please, add label Spreadsheet -- the if clause is the same with Excel and Google Spreadsheet.

Answer 1

The labels on your rows and columns are missing, but I guess it's this:

$$ \begin{array}{c|r|r} & \mbox{B} & \mbox{C} \\ \hline 2 & 1 & 0 \\ 3 & 7 & 77 \\ 4 & 6 & 66 \\ \end{array} $$

For a given column, the if condition (if(B2:C2,B3:C3,B4:C4)) means: if the value in row 2 is true (or 1) then return the value at row 3 else return the value at row 4.

You can achieve this in Excel by typing (in B6, for example) =IF(B2,B3,B4), selecting B6:C6 and using "Fill right" (or by dragging the fill handle at the bottom right corner of the initial cell when expanding the selection).