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:
=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.
- 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
5 thoughts on “Google Sheets Lesson 4 – formatting ARRAYFORMULA”
Hello,
I work for a non-profit and have been trying to get a formula working in Google Sheets for over a year. I saw your website and wondered if you could advise me a bit…
I have setup a simplified example of what I am trying to do (mostly to reduce confusion). It can be accessed here and I have editing avaiable:
https://docs.google.com/spreadsheets/d/1WMJeC5JrHiRJR-fZPKvD_G1kMsbKmeXV-Tpy-SLP5OM/edit?usp=sharing
Basically I am trying to get the formula that is in B2 to apply itself all the way down the column as long as there is data in A2. So everytime there is a new entry in A2, B2 will get a new entry.
Just a note, the formula is also referencing Sheet 2 which converts a numerical value into a date. If you can think of a better way to be doing any of this, I’m open! The only problem I have is that, on the front end of all of this all of my dates that are entered are in a numerical value so I have no way to not do the Sheet 2 conversion table.
I think this is an easy fix, but I’m just overwhelmed at trying. I appreciate any thoughts you have!
Justin
Paste the following formula into cell B1 of Sheet1 and it should work:
=ARRAYFORMULA(IF(ROW(A:A)=1,”End Date”,IF(A:A=””,””,VLOOKUP(A:A,Sheet2!A:B,2,False))))
Feel free to modify the formula to fit the needs of your sheet. I would have made the changes on the sheet you sent me, but you only gave me View access, so I made an example of the formula use here.
Let me know if it works!
Dear Paul, I fortunately ran into your tutorials. They are marvellous! Thank you from Germany & a happy new year!
Joachim
These tutorials are incredible! Very clear. If you had a donation button I would have transferred some money for the efforts, but for now I will just say thank you so much.
Hi,
I’m wanting to use the following formula on sheet 1 however I really need to remove the errors if the cell is empty.
Is it possible to add the IFERROR option into this and if so, where is the best place to add it, or what would the best formula be to use if a different one would be necessary?
=arrayformula(if(row(C:C)=1,”Column Title”, VLOOKUP(B:B,Sheet2!A:B,2, FALSE)))