QUERY()does not let you reference columns by their header labels
- You can only reference columns by column letter or by relative position within the reference range
This formula searches for a column header name and converts it into the column letter that it’s in, allowing us to continue using the
Check out this interactive sheet for an example implementation.
||The query data range that you’re selecting data from||
||The row that contains the list of column headers you’ll be searching against||
||Header name of the column that you want to reference||
TIP: You’ll notice that the
HEADER NAME parameter in the interactive sheet references a cell with data validation,
$A$1, instead of a string.
Setting the data validation range to be identical to your
HEADER ROW parameter makes for a neat interactive tool without having to edit the underlying formula!
Here’s a step-by-step breakdown of how the nested functions build on each other:
MATCH(...)gives us the column that
HEADER NAMEis found in
ADDRESS(1, MATCH(...))takes the result from above and gives us the full cell address
REGEXEXTRACT(ADDRESS(1, MATCH(...)), "[A-Z]+")takes the result from above and pattern matches to give us just the column letter