run2_excel-index-match
INDEX&MATCH for two-condition lookups in Excel, with correct absolute/mixed references for fill-down/fill-right
- License 许可
- MIT
- 最近更新
- 2026-10-02
- Stars
- ★ 92
- Forks
- 7
01 前言 Front Matter
- name
- run2_excel-index-match
- description
- INDEX&MATCH for two-condition lookups in Excel, with correct absolute/mixed references for fill-down/fill-right
03 SKILL.md 正文
INDEX & MATCH Two-Condition Lookup
Formula Pattern
=INDEX(data_range, MATCH(row_criteria, row_lookup_range, 0), MATCH(col_criteria, col_lookup_range, 0))
Two-Condition Lookup (series code + year)
Given:
- Data range:
Data!$H$21:$M$40(all values, absolute) - Row lookup:
Data!$B$21:$B$40(series codes, absolute) - Col lookup:
Data!$H$4:$M$4(years, absolute) - Row criteria:
$D12(series code in Task sheet - lock column, allow row to change) - Col criteria:
H$9(year in Task sheet - lock row, allow column to change)
=INDEX(Data!$H$21:$M$40, MATCH($D12, Data!$B$21:$B$40, 0), MATCH(H$9, Data!$H$4:$M$4, 0))
Reference Locking Strategy
| Reference | Lock | Why |
|---|---|---|
$D12 |
Column locked | Series code always in col D; row changes per country |
H$9 |
Row locked | Year always in row 9; column changes per year |
| Data ranges | Fully absolute | Never changes |
openpyxl Implementation
from openpyxl import load_workbook
wb = load_workbook('file.xlsx')
ws = wb['Task']
# Fill H12:L17 (and similar ranges) with lookup formulas
for row in range(12, 18):
for col in ['H', 'I', 'J', 'K', 'L']:
ws[f'{col}{row}'] = f'=INDEX(Data!$H$21:$M$40,MATCH($D{row},Data!$B$21:$B$40,0),MATCH({col}$9,Data!$H$4:$M$4,0))'
Important Notes
- Skip gap rows between sections (e.g., row 18, 25 if ranges are 12-17, 19-24, 26-31)
- MATCH with 0 = exact match; returns #N/A if not found
- Verify series codes in Task!D column exactly match Data!B column
04 同仓库更多 Skills
object_counter
Count occurrences of an object in the image using computer vision algorithm.
travel-planning-workflow
Invoke this skill when performing travel planning. It defines a structured workflow for generating itineraries. Following this workflow is essential to ensure the final plan satisfies user requirements.
bundled-dataset-search-tool
invoke this skill when you need to perform database search for travel planning. This skill provides some useful pre-packaged tools to look up accommodations, attractions, cities, driving distance, flights, and restaurants from the bundled dataset.
glm-output
Output-processing guidance for GLM tasks. Especially useful after glm-basics and glm-calibration when you need verifier-matching metrics from output.nc and a final /root/metrics.json.
glm-calibration
Calibration guidance for GLM tasks. Often most effective after glm-basics has clarified the setup; glm-output is the companion skill for exact final metric computation.
glm-basics
A good starting point for GLM calibration tasks. Use to inspect glm3.nml, confirm how GLM runs, and identify the relevant files before moving on to calibration and output evaluation.