Feb 24 2015
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.
- IFERROR function – instead of just using VLOOKUP, wrap it in an IFERROR function: ARRAYFORMULA(IFERROR(VLOOKUP(search, range, index, false))). This function simply tells Google to check if the result is an error, and if so don’t display anything (displaying nothing is the default; you can also include an optional error message). This works well for cases like the example.
- IF(A:A=””,””,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:
We could use:
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.
- 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