The Basics – Array formulas

Array formulas are all about efficiency. In previous posts, I told you to simply copy and paste a formula from one row to an other in order to replicate it. I didn’t want to complicate things then, but technically, I was giving you an inefficient way of doing something.

In order to explain how an Array formula works, I am going to pick up where we left off on the subject of VLOOKUPs. I suggest that you read this post first.

Applying our formula to every row of the document

Here’s what our document looked like when we finished creating the VLOOKUP:

The formula in cell E2 read:

=IFERROR ( VLOOKUP ( D2:D , Positions!$A$2:$B$8 , 2 , FALSE ) )

Instead of copy/pasting this formula into the cells below, we are going to wrap the VLOOKUP function into an ARRAYFORMULA function. For the sake of simplicity, I am going to remove the IFERROR:

=VLOOKUP ( D2 , Positions!$A$2:$B$8 , 2 , FALSE )

The syntax for ARRAYFORMULA is as simple as it can be. It’s just “ARRAYFORMULA(formula)”. So for us, this means:

=ARRAYFORMULA ( VLOOKUP ( D2 , Positions!$A$2:$B$8 , 2 , FALSE ) )

If you press enter, you’ll notice that nothing has changed. That’s because our VLOOKUP’s search key is only one cell. We need to change that to an array (think of an array as a range of cells).

=ARRAYFORMULA ( VLOOKUP ( D2:D , Positions!$A$2:$B$8 , 2 , FALSE ) )

This time, the formula replicates itself, and the search key is dynamically adjusted based on the row. Now let’s add or IFERROR back in to avoid #N/A errors with empty rows:

=IFERROR( ARRAYFORMULA ( VLOOKUP ( D2:D , Positions!$A$2:$B$8 , 2 , FALSE ) ) )

That’s it! Remember that an array formula only works with an array. Quick example:
You have number in columns A and B. You want to total them in column C so that:

C1 = A1 + B1
C2 = A2 + B2
C3 = A3 + B3
etc...

The best way to achieve this is to write the following formula in cell C1:

=ARRAYFORMULA ( A:A + B:B)

Final note: If you use an array formula in some sort of log, you might want to put that formula in a hidden row right below your header. Why? Because if you are like me, and prefer to add a row at the top of your table for each new entry rather than scrolling to the bottom, you’ll want to new row to be part of the array. If your array formula is under the new row, then things don’t work anymore!