CODEWITHSUNDEEP
arraysgoogle-sheetsmatchgoogle-sheets-formulaindex-match
Canovice
Canovice

Reputation: 10163

In Google Sheets, link to column by name rather than column number

=index(nfl_an_raw!$1:$808, match(J8, nfl_an_raw!$AH:$AH, 0), H$4)

Here is an index-match we are doing in Google Sheets. Our main problem with this is nfl_an_raw!$AH:$AH. The column ordering in nfl_an_raw are subject to change, and we would much rather be explicitly grabbing this column by its column name game_key, rather than by its column index $AH:$AH.

Is this possible in Google Sheets? Seems like it would be straightforward but I haven't found a solution yet.

Edit: the name of the column is game_key because the cell AH1 is game_key.

https://docs.google.com/spreadsheets/d/1GVEFVNEs78bhL61WbDXT0o_8Z7ToQaxpTU1woEAB-sY/edit?usp=sharing - see cell B3

Upvotes: 1

Views: 485

Answers (1)

player0
player0

Reputation: 1

try:

=INDEX(nfl_an_raw!$1:$808, MATCH(B1, INDIRECT("nfl_an_raw!"&
 ADDRESS(1, MATCH("game_key", nfl_an_raw!$1:$1, 0), 4)&":"&SUBSTITUTE(
 ADDRESS(1, MATCH("game_key", nfl_an_raw!$1:$1, 0), 4), 1, )), 0), B2)

Upvotes: 1

Related Questions