Excel index match

Skill cxcscmu/SkillLearnBench/skills/b1-one-shot-claude-sonnet-4-6/weighted-gdp-calculation/excel-index-match

[COLM'26] SkillLearnBench is the first benchmark for evaluating continual learning methods that automatically generate agent skills.

Install

npx -y skills add cxcscmu/SkillLearnBench --skill excel-index-match

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

What its author says it does

Copied from the file, not written here

Using INDEX&MATCH (single and dual condition) for dynamic lookups across rows and columns in Excel, including cross-sheet references.

SKILL.md

2.7 KB, as published. Nobody here has run it

INDEX & MATCH for Two-Condition Lookups

Overview

INDEX&MATCH is preferred over VLOOKUP/HLOOKUP because it allows:

Lookups by both row AND column simultaneously
Lookups in any direction (not just left-to-right)
Cross-sheet references with ease

Basic Syntax

=INDEX(data_range, MATCH(row_key, row_lookup_range, 0), MATCH(col_key, col_lookup_range, 0))

Two-Dimensional Lookup Pattern

When you need to find a value based on BOTH a row label (e.g., series code) and a column label (e.g., year):

=INDEX(Data!$H$21:$M$40,
       MATCH($D12, Data!$B$21:$B$40, 0),
       MATCH(H$10, Data!$H$4:$M$4, 0))

Explanation:

Data!$H$21:$M$40 — the value table (rows = series, cols = years)
MATCH($D12, Data!$B$21:$B$40, 0) — find the ROW where D12's series code appears in column B
MATCH(H$10, Data!$H$4:$M$4, 0) — find the COLUMN where H10's year appears in row 4
0 = exact match

Dollar Sign Anchoring for Drag-Copying

Use mixed references so formulas can be dragged across rows and columns:

=INDEX(Data!$H$21:$M$40, MATCH($D12, Data!$B$21:$B$40, 0), MATCH(H$10, Data!$H$4:$M$4, 0))

$D12 — column D is fixed (series code column), row shifts when dragged down
H$10 — column H shifts when dragged right, row 10 is fixed (year header row)
Data!$B$21:$B$40 — fully fixed (lookup range never changes)
Data!$H$4:$M$4 — fully fixed (header row never changes)

Cross-Sheet Reference Format

=INDEX(SheetName!$A$1:$Z$100, ...)

Use ! to reference another sheet. Wrap sheet names with spaces in single quotes:

=INDEX('Sheet Name'!$A$1:$Z$100, ...)

XLOOKUP Alternative (Excel 365+)

=XLOOKUP(row_key, row_lookup_range, XLOOKUP(col_key, col_header_range, data_range))

For two-dimensional lookup with XLOOKUP (nested):

=XLOOKUP($D12, Data!$B$21:$B$40, XLOOKUP(H$10, Data!$H$4:$M$4, Data!$H$21:$M$40))

Common Pitfalls

Always use 0 (exact match) as third argument unless intentional approximate match
Ensure row lookup range has same length as first dimension of data_range
Ensure col lookup range has same length as second dimension of data_range
The data_range rows must align with row_lookup_range, and cols with col_lookup_range

Setting in Python (openpyxl)

from openpyxl import load_workbook

wb = load_workbook('file.xlsx')
ws = wb['Task']

# Set formula string directly
ws['H12'] = '=INDEX(Data!$H$21:$M$40,MATCH($D12,Data!$B$21:$B$40,0),MATCH(H$10,Data!$H$4:$M$4,0))'
wb.save('file.xlsx')