Feb 24 2015

## 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

Feb 24 2015

## Google Sheets Lesson 4 – formatting ARRAYFORMULA

In the previous example we saw how you can use the function ARRAYFORMULA to make a function apply to a whole column, including new form entries that fall into that column. However, in that example there was no header row and there were lots of #N/A errors that looked pretty ugly. Here we’ll learn how to fix those problems.First, the #N/A errors. Those come because we used a formula that depended on lookup data from the C column, but there were no entries in that column after row 4. There are two different ways of fixing this problem.

code) – this is one of my favorites. Instead of checking if there is an error, we check to see if there is an entry in the A column, or some other column. If the A column is “”, then display “”. Otherwise, display the normal function. Here’s how it looks with VLOOKUP: ARRAYFORMULA(IF(A:A=””,””,VLOOKUP(search, range, index, false)))Click on Example 4, make a copy, and try out one of these techniques to clean up the errors.

Now we have a new challenge: how do we get a custom row header. For this, we use the ROW() function. This simple returns the row number of a given cell. It is extremely useful in array formulas. For example, instead of our previous formula:

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

We could use:

=ARRAYFORMULA(IF(ROW(A:A)=1,”Full Name”,VLOOKUP(C:C,Lookups!A:B,2,false)))

In this example, we told Google that if the row of the cell is 1, put in the heading “Full Name”. If the row is other than one, use the regular VLOOKUP formula. To make this work, we also need to move the whole formula from the F2 cell into F1. Note that the Search Key in the VLOOKUP formula also changes from C2:C into C:C. It is important that the starting place of this range matches the row of the start of the ARRAYFORMULA.

Click on Example 4 then make a copy for yourself. Use the two techniques described here to remove all of the error messages AND have a header in the top row.

## Google Sheets Lessons 1-9

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

## Examples / Challenges

By pswanson • Tutorial • 5