excel-two-way-lookup

name	excel-two-way-lookup
description	Perform a two-dimensional lookup in Excel to retrieve data based on both a row criteria (e.g., series code) and a column criteria (e.g., year).

To retrieve data from a table based on two variables, you can use the INDEX and MATCH combination or nested XLOOKUP.

INDEX and MATCH (Two-Way)

This is the most robust method for older and newer versions of Excel. =INDEX(data_range, MATCH(row_criteria, row_header_range, 0), MATCH(column_criteria, column_header_range, 0))

• data_range: The grid containing the values you want to extract.
• row_criteria: The specific value to look for in the first column (e.g., Series Code).
• MATCH(..., 0): Finds the exact position of the criteria.

XLOOKUP (Nested)

If using modern Excel, you can nest XLOOKUPs: =XLOOKUP(row_criteria, row_header_range, XLOOKUP(column_criteria, column_header_range, data_range))

Absolute Referencing

When dragging formulas across a range, ensure you use $ to lock the source data ranges (e.g., $D$21:$D$40) while allowing the criteria references (like year or country) to shift appropriately.