| name | excel-index-match |
| description | How to use INDEX&MATCH for two-dimensional lookups in Excel via openpyxl. Use this skill whenever building Excel formulas that look up values by matching both a row criterion and a column criterion from a data table — e.g., matching a series code (row) and a year (column). Trigger when the user mentions VLOOKUP&MATCH, HLOOKUP&MATCH, XLOOKUP&MATCH, INDEX&MATCH, or two-condition lookups.
|
INDEX & MATCH Two-Dimensional Lookup in Excel
When to Use
Use INDEX(range, MATCH(...), MATCH(...)) when you need to retrieve a value from
a rectangular data range using two criteria — one to identify the row and one to
identify the column.
Formula Pattern
=INDEX(data_range, MATCH(row_criterion, row_lookup_range, 0), MATCH(col_criterion, col_lookup_range, 0))
Components
| Part | Purpose |
|---|
data_range | The rectangular block of values (e.g., Data!H21:M40) |
row_criterion | The value to match in the row dimension (e.g., a series code in $D12) |
row_lookup_range | A single column containing the row keys (e.g., Data!$B$21:$B$40) |
col_criterion | The value to match in the column dimension (e.g., a year in H$9) |
col_lookup_range | A single row containing the column keys (e.g., Data!$H$4:$M$4) |
0 | Exact match |
Locking References
- Lock the row criterion column with
$D12 (absolute column, relative row) so it adjusts when copied down but not across.
- Lock the col criterion row with
H$9 (relative column, absolute row) so it adjusts when copied across but not down.
- Lock lookup ranges with
$ on both dimensions since they never move.
Example in openpyxl
from openpyxl import load_workbook
wb = load_workbook('file.xlsx')
ws = wb['Task']
for row in range(12, 18):
for col_idx, col_letter in enumerate(['H','I','J','K','L']):
formula = (
f"=INDEX(Data!$H$21:$M$40,"
f"MATCH($D{row},Data!$B$21:$B$40,0),"
f"MATCH({col_letter}$9,Data!$H$4:$M$4,0))"
)
ws[f'{col_letter}{row}'] = formula
wb.save('file.xlsx')
Common Pitfalls
- Ensure the data_range, row_lookup_range, and col_lookup_range all span the same rows/columns of the source data.
- Use absolute references (
$) on ranges that should not shift when the formula is copied.
- The third argument
0 in MATCH means exact match — omitting it defaults to approximate match, which requires sorted data.
