Lookup & Reference

VLOOKUP with MATCH: make the column index dynamic when headers move

Replace a hard-coded VLOOKUP column number with MATCH so formulas adapt to selected period headers and inserted columns without breaking.

Why hard-coded column numbers break VLOOKUP

Standard VLOOKUP requires a numeric col_index_num argument, such as 4 for the fourth column of the lookup table. If someone inserts a new column inside the table or you want a report dropdown to switch between FY24, FY25, and FY26 rates, a fixed integer points to the wrong column or forces manual edits.

Nesting MATCH(header_name, header_row, 0) as the third argument of VLOOKUP calculates the 1-based column position from the visible header text at evaluation time.

Cell / Range	Example Value	Role
A1:E1	Zone \| FY24 \| FY25 \| FY26 \| Surcharge	Header row spanning the exact same columns as the VLOOKUP table
A2:E4	Z-North, Z-Central, Z-South freight rates	Lookup table starting in the Zone key column
G2	Z-Central	Row key searched down A2:A4
H2	FY26	Column header searched across A1:E1

Combine VLOOKUP and exact MATCH

Make sure the MATCH header range starts in the exact same leftmost column as the VLOOKUP table_array. Here A1:E1 aligns with A2:E4, so "FY26" in D1 resolves to position 4—the fourth column of A2:E4. For Z-Central, column D holds 135.

Always pass 0 as the third argument of MATCH and FALSE as the fourth argument of VLOOKUP so neither axis silently falls back to approximate matching.

=VLOOKUP(G2, A2:E4, MATCH(H2, A1:E1, 0), FALSE)

Guard both missing headers and missing row keys

If the row key in G2 is missing, VLOOKUP returns #N/A. Checking ISNA(MATCH(H2, A1:E1, 0)) before VLOOKUP and wrapping VLOOKUP in IFNA ensures both a mistyped column header and an unknown zone return a clear message.

=IF(ISNA(MATCH(H2, A1:E1, 0)), "Check zone or column", IFNA(VLOOKUP(G2, A2:E4, MATCH(H2, A1:E1, 0), FALSE), "Check zone or column"))

Try the example

Use the original values or change them to test your understanding.