run2_multi-condition-lookups

Multi-Condition Lookups: INDEX/MATCH in Excel

When to Use

Use INDEX/MATCH when you need to:

• Look up data based on TWO OR MORE criteria (e.g., series code AND year)
• Work with data in any row/column order (unlike VLOOKUP/HLOOKUP which require specific ordering)
• Reference cells/ranges from other sheets in the same workbook

Cross-Sheet Reference Syntax

Critical: Use ! for Sheet References (Not .)

Excel and LibreOffice Calc require different syntax for cross-sheet references:

• Excel syntax: Sheet!Cell or Sheet!Range
• LibreOffice Calc: Also supports Sheet!Range (Excel compatible)
• DO NOT use: Sheet.Cell or Sheet.$H$21 (these cause #NAME? errors in LibreOffice)

Correct: =INDEX(Data!$H$21:$M$40, MATCH(...), MATCH(...)) Wrong: =INDEX(Data.$H$21:$M$40, MATCH(...), MATCH(...))

Two-Condition INDEX/MATCH Formula

Structure

=INDEX(data_range,
  MATCH(row_criteria, row_lookup_range, 0),
  MATCH(col_criteria, col_lookup_range, 0)
)

Parameters

• data_range: The 2D range containing the values to return (e.g., Data!$H$21:$M$40)
• row_criteria: The value to match in rows (e.g., $D12 for series code)
• row_lookup_range: Column containing row criteria (e.g., Data!$B$21:$B$40)
• col_criteria: The value to match in columns (e.g., H$9 for year)
• col_lookup_range: Row containing column criteria (e.g., Data!$H$4:$M$4)
• 0: Exact match (required for lookups)

Example: Lookup Exports by Series Code and Year

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

Cell Reference Anchoring

Absolute vs. Mixed References

• $column$row: Absolute - never changes when copying
• column$row: Column relative, row absolute - column changes when copying right
• $columnrow: Column absolute, row relative - row changes when copying down
• columnrow: Both relative - both change when copying

Pattern for 2D Data Lookups

=INDEX(Data!$H$21:$M$40,        # Absolute data range
  MATCH($D12, Data!$B$21:$B$40, 0),   # Row criteria absolute, lookup range absolute
  MATCH(H$9, Data!$H$4:$M$4, 0)      # Column criteria absolute, lookup range absolute
)

When copying:

• Down: Row number in criteria changes ($D12 → $D13)
• Right: Column letter in criteria changes (H$9 → I$9)

Common Errors and Fixes

Error	Cause	Fix
#NAME?	Wrong sheet reference syntax (using .)	Change Sheet.Cell to Sheet!Cell
#REF!	Invalid range reference	Verify sheet name and ranges exist
#N/A	Criteria not found in lookup range	Check criteria value matches exactly
#VALUE!	Wrong data type in criteria	Ensure criteria matches lookup data type

Alternative Functions

• XLOOKUP (Excel 365+): Simpler syntax, more flexible
• VLOOKUP/HLOOKUP: Simpler but requires data in specific order
• LOOKUP: Less flexible but works across both rows and columns