Google Sheets Lesson 3 – ARRAYFORMULA

As you noticed in the last tutorial, you can’t simply copy down a formula and have it apply to new form submissions. This is because the response sheet actually inserts a new row for every submission, breaking up the block of formulas. How then, do we have the formulas automatically apply to new submissions?To solve this problem, we need to use the function ARRAYFORMULA. This is a very powerful function that allows you to create an entire column of formulas with only one cell. The advantages of this are many:

• it allows formulas to apply to new form submissions
• it lets you make a change to a formula in only one place
• it allows you to copy a single cell and modify it for variations

With the new Google Sheets, ARRAYFORMULAs are fairly simple: you use a range (usually A:A, B:B, and so on) instead of a cell reference. In the previous example, we used the formula:

=VLOOKUP(C2,Lookups!A:B,2,false)

All we need to do now is change the Search key from C2 to C2:C, and put the whole thing in an ARRAYFORMULA function:

=ARRAYFORMULA(VLOOKUP(C2:C,Lookups!A:B,2,false))

Go to the example, and Click Form > ‘Go to live form’ and fill it out to see what happens. You should see the formula applied to the new entry.

Google Sheets Lessons 1-9

Click on a lesson below for a written tutorial demonstrating the lesson.

• Lesson 1 – CONCATENATE
• Lesson 2 – VLOOKUP
• Lesson 3 – ARRAYFORMULA
• Lesson 4 – formatting ARRAYFORMULA
• Lesson 5 – QUERY
• Lesson 6 – UNIQUE
• Lesson 7 – INDEX & MATCH
• Lesson 8 – conditional INDEX & MATCH
• Lesson 9 – Autocrat Add-on

Examples / Challenges