The array formula is a wonderful thing in Google Sheets, in most cases providing the opportunity to enter just the one formula and have it automatically fill down as new entries appear from elsewhere.
A clever trick though is to create an array within an array, which allows for header rows to be included.
Here is a normal array formula, which you would normally have to put in row 2, and write in a header above it.
|
1 |
=arrayformula(iferror(vlookup(A2:A,lists!$A$2:$E$201,5*sign(row(A2:A)),FALSE))) |
However if you put everything in curly braces, and a text at the beginning, you can put the formula in row 1, and a header magically appears with the arrayformula below it.
|
1 |
={"Supervisor";arrayformula(iferror(vlookup(A2:A,lists!$A$2:$E$201,5*sign(row(A2:A)),FALSE)))} |
Even better, if you work with awesome tables, and need that second header row to define column filters, you can add that too
|
1 |
={"Supervisor"; "CategoryFilter"; arrayformula(iferror(vlookup(A2:A,lists!$A$2:$E$201,5*sign(row(A2:A)),FALSE)))} |