This one confused me for a long time. But once I figured out how to use ARRAY FORMULA, I’ll never go back – because it’s an instrument of laziness.
It allows you to write a formula once, and apply it to an entire row or column.
No more copy and pasting across a sheet – and when that one arrayformula breaks, you only have one cell to check (instead of 1000 if you’re copy-pasting).
That’s really all there is to ARRAYFORMULA. You write a formula as you normally would (VLOOKUP in this case), rewrite any individual cells (A2) as ranges (A2:A), and wrap the entire thing in ARRAYFORMULA().
I find myself using it most often when I want to populate an entire column.
It allows you to write a formula once, and apply it to an entire row or column.
No more copy and pasting across a sheet – and when that one arrayformula breaks, you only have one cell to check (instead of 1000 if you’re copy-pasting).
How it works
There’s one key to understanding ARRAY FORMULA: everything must be a range. You can’t just run a vlookup on cell A2 – you’ve got to pass the entire array (A2:A, or some section like A2:A6).=ARRAYFORMULA( VLOOKUP( A2:A, data!$A:$C, 3, 0))
That’s really all there is to ARRAYFORMULA. You write a formula as you normally would (VLOOKUP in this case), rewrite any individual cells (A2) as ranges (A2:A), and wrap the entire thing in ARRAYFORMULA().
When to use it
Anytime you want to run the same formula across multiple cells. Think about arrayformula as a replacement for copy-paste within spreadsheets.I find myself using it most often when I want to populate an entire column.
COMMENTS