Google Sheets – Does ArrayFormula Help Inside a Query?

Without Apps Script

One cannot make a new function without Apps Script. The only way to streamline the process is to put more cells somewhere and reference those.

For example: under each header such as 'Weight', enter the command

=regexextract(address(1, column()), "[A-Z]+")

This will put the column letter(s) under the headers. Include the row with letters in the Headers named range. In the query string, use

hlookup("Weight", Headers, 2, 0)

which is easier on the eyes than

SUBSTITUTE(ADDRESS(1,MATCH("Weight", Headers, 0),4), "1", "")

With Apps Script

Passing a range (named or otherwise) into a custom function passes in the values, with no information on their location in the sheet. But I found a solution: infer the location of headers from the first argument of query. No need to include "Headers", which was itself repetitive.

Version 1: headers included in query range

I prefer to include the header rows in the range passed to query, and specify the number of header rows as the 3rd argument of query. This avoids misinterpretation of data as headers or vice versa. For example:

=QUERY(A1:F13, "select "&GetHeader("Type")&" where ("&GetHeader("Version")&" = 'Version 1') and ("&GetHeader("Type")&" <> 'Type')", 2) 

Here is the custom function to use with the above query:

function getHeader(name) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/\w+:\w+(?=[ ,])/);
  var range = sheet.getRange(args[0]);
  var firstRow = range.offset(0, 0, 1, range.getWidth());
  var headers = firstRow.getValues();
  for (var i = 0; i < headers[0].length; i++) {
    if (headers[0][i] == name) {
      var notation = range.getCell(1, i+1).getA1Notation();
      var column = notation.replace(/\d/, '');
      return column;
    }
  }
  return 'Not found';
}

The function obtains the formula from the cell from which it was called. It extracts the first range argument of formula with a regular expression. Then it looks through the first row of this range, assuming it's the header row, in search of the given string. It obtains the A1 notation of the cell with the string, removes the row part of it, and returns the result.

Version 2: headers taken from Row 1

An alternative version, where the headers are taken from the first row of the sheet, regardless of the rows in the query argument. Just replace

  var firstRow = range.offset(0, 0, 1, range.getWidth());

with

  var firstRow = sheet.getRange(1, range.getColumn(), 1, range.getWidth());

With this version you can use

=QUERY(A3:F13, "select "&GetHeader("Type")&" where ("&GetHeader("Version")&" = 'Version 1') and ("&GetHeader("Type")&" <> 'Type')")  

It's difficult to envision why you would merge entire columns A:B, since that would essentially just create one column; so I'm guessing that only the header columns are merged. If that is the case, there is a simple solution: include at least five blank columns from Data! in your QUERY.

For instance, if you only had data in Data!A:E (i.e., F and following are blank), you could structure your QUERY in Query!A1 like this:

=QUERY(Data!A:J,"Select A, F, B, G, C, H, D, I, E, J Limit 100")

Notice that the Select calls filled column, empty column, filled column, empty column, etc.

However... if you were merging header columns in Query! and trying to fill the left-hand column under each merged header while leaving the right-hand column under each header free to add new data, this approach will not work, since you can't add manual data anywhere inside the scope of a QUERY-filled range.

In that case, the simple solution is simply to place five separate QUERY calls:

In A1: =QUERY(Data!A:A,"Select * Limit 100")

In C1: =QUERY(Data!B:B,"Select * Limit 100")

In E1: =QUERY(Data!C:C,"Select * Limit 100")

In G1: =QUERY(Data!D:D,"Select * Limit 100")

In I1: =QUERY(Data!E:E,"Select * Limit 100")