Problem

Solution

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 QUERY() function.

FORMULA
=QUERY(DATA RANGE, “SELECT “&REGEXEXTRACT(ADDRESS(1, MATCH(HEADER NAME, HEADER ROW, 0)), “[A-Z]+“)&” LABEL “&REGEXEXTRACT(ADDRESS(1, MATCH(HEADER NAME, HEADER ROW, 0)), “[A-Z]+“)&” “”)

Example

Check out this interactive sheet for an example implementation.

PARAMETER DESCRIPTION EXAMPLE
DATA RANGE The query data range that you’re selecting data from Data!$A:$H
HEADER ROW The row that contains the list of column headers you’ll be searching against Data!1:1
HEADER NAME Header name of the column that you want to reference "D90"

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!

Explanation

Here’s a step-by-step breakdown of how the nested functions build on each other:

  1. MATCH(...) gives us the column that HEADER NAME is found in HEADER ROW
  2. ADDRESS(1, MATCH(...)) takes the result from above and gives us the full cell address
  3. REGEXEXTRACT(ADDRESS(1, MATCH(...)), "[A-Z]+") takes the result from above and pattern matches to give us just the column letter