Run2 excel index match

Skill cxcscmu/SkillLearnBench/skills/b2-self-feedback-claude-sonnet-4-6/weighted-gdp-calculation/run2_excel-index-match

Use INDEX&MATCH with two conditions (series code + year) to populate data tables in Excel from a source sheet, with proper anchoring for copyable formulas.From its SKILL.md

Install

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

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

SKILL.md

2.3 KB, 772 tokens by cl100k_base, as published. Nobody here has run it

Excel INDEX & MATCH: Two-Condition Lookup (Series Code + Year)

Formula Pattern for This Task

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

Breakdown

Part	Purpose
`Data!$H$21:$M$40`	Source data range (fully anchored — never moves)
`MATCH($D12, Data!$B$21:$B$40, 0)`	Find row by series code; `$D` anchors column D, row 12 moves
`MATCH(H$9, Data!$H$4:$M$4, 0)`	Find column by year; `H` moves across years, `$9` anchors row 9

Dollar Sign Rules (Critical for Copy-Down/Copy-Across)

$D12 — lock column D (series codes), let row vary as you copy down
H$9 — let column vary as you copy right (year moves), lock row 9 (year header)
Data!$H$21:$M$40 — fully lock the data range

Key Observations About the Data Structure

Data sheet rows 21-40: Contains all 3 data types (Exports rows 21-26, gap at 27, Imports rows 28-33, gap at 34, GDP rows 35-40)
Using the FULL range ($B$21:$B$40 and $H$21:$M$40) works for all three sections since series codes are unique across the entire range
No need to specify sub-ranges per data type — one formula works for all of H12:L17, H19:L24, H26:L31

Data Sheet Key Coordinates

Series codes: Data!$B$21:$B$40 (column B)
Year headers: Data!$H$4:$M$4 (row 4, years 2018–2023)
Data values: Data!$H$21:$M$40 (the intersection)

Task Sheet Key Coordinates

Series codes (lookup keys): Column D (D12, D19, D26, etc.)
Year headers (lookup keys): Row 9 (H9=2019, I9=2020, ..., L9=2023)
Yellow cells to fill:
- H12:L17 — Exports
- H19:L24 — Imports
- H26:L31 — GDP

openpyxl Implementation

cols = ['H', 'I', 'J', 'K', 'L']

for row in range(12, 18):  # also 19-24 and 26-31
    for col in cols:
        task[f'{col}{row}'] = (
            f'=INDEX(Data!$H$21:$M$40,'
            f'MATCH($D{row},Data!$B$21:$B$40,0),'
            f'MATCH({col}$9,Data!$H$4:$M$4,0))'
        )

Validation

Verify formula results:

UAE Exports 2019 should be ≈ 404.05 billion (matches Data!I26)
Bahrain GDP 2023 should be ≈ 44.67 billion (matches Data!M35)

What ships with it

Read from the repository

Just SKILL.md. No reference files, no scripts.