Lookup & Reference
Get Column Name from Index & Column Position from Header (INDEX / MATCH)
Retrieve the nth column header label from a table header row with INDEX(A1:E1, 1, k) and find the 1-based column index of any named field with MATCH(name, A1:E1, 0).
Dynamic report headers and schema audits need two-way header indexing!
When a dashboard user picks metric #4 from a selector or when MAX/MIN locates the peak value in column 4 of a quarterly KPI row, you want the human-readable column header ("Q3_Rev"), not the raw number 4! Passing row_num = 1 and col_num = k into INDEX(A1:E1, 1, G2) extracts the exact nth header name from any horizontal header range, guarded cleanly by COLUMNS(A1:E1)!
Need the inverse lookup—finding which 1-based column position holds "Q4_Rev" so VLOOKUP or INDEX knows which column to pull even after someone inserts a new column? Wrap MATCH(G3, A1:E1, 0) in IFNA(..., "Header Not Found")!
| Header Row A1:E1 | Position 1..5 | Lookup Formula | Returned Result |
|---|---|---|---|
| Region | Q1_Rev | Q2_Rev | Q3_Rev | Q4_Rev | Index k = 4 | =INDEX(A1:E1, 1, 4) | "Q3_Rev" |
| Search Header "Q4_Rev" | Exact Match | =MATCH("Q4_Rev", A1:E1, 0) | 5 |
| Out-of-Range Index k = 7 | Exceeds 5 Cols | Bounds Guard IF(k > COLUMNS(...)) | "Column Index Out of Range" |
Get Table Column Header Name from 1-Based Column Index
Validates that column index G2 lies between 1 and COLUMNS(A1:E1) before returning the header text via INDEX.
=IF(OR(G2 < 1, G2 > COLUMNS(A1:E1)), "Column Index Out of Range", INDEX(A1:E1, 1, G2))Get 1-Based Column Index from Header Name
Finds the exact horizontal position of field name G3 inside A1:E1, returning a clear alert if the column is missing.
=IFNA(MATCH(G3, A1:E1, 0), "Header Not Found")Try the example
Use the original values or change them to test your understanding.