Excel INDEX MATCH MATCH: Master 3D Lookups for Complex Data

You have a pricing table with products in rows and regions in columns. Your boss asks: "What's the price for Product X in Region Y?"

With VLOOKUP, you're stuck. It only looks vertically. With INDEX MATCH, you can do one-dimensional lookups. But with INDEX MATCH MATCH, you unlock two-dimensional lookups—finding values at the intersection of any row and column.

This is the formula that transforms you from an Excel user into an Excel power user.

What is INDEX MATCH MATCH?

INDEX MATCH MATCH combines three Excel functions to perform "two-way lookups" (also called "matrix lookups" or "2D lookups"):

• INDEX: Returns a value from a specific position in a range
• MATCH #1: Finds the row position
• MATCH #2: Finds the column position

The formula structure:

1=INDEX(data_range, MATCH(row_value, row_range, 0), MATCH(col_value, col_range, 0))

Translation: "In my data table, find the value at the intersection of [this row] and [this column]."

Why Not VLOOKUP?

VLOOKUP limitations:

• Only searches first column (can't look left)
• Returns values to the right only
• Column numbers break when you insert columns
• Can't handle two-dimensional lookups

INDEX MATCH MATCH advantages:

• Looks in any direction (left, right, up, down)
• Returns any column, regardless of position
• Flexible (won't break if you add/remove columns)
• Handles complex two-dimensional data structures

Understanding the Components

Component 1: INDEX Function

Purpose: Returns the value at a specific row and column intersection

Syntax:

1=INDEX(array, row_number, [column_number])

Example:

1=INDEX(A1:C10, 5, 2)

Returns the value in row 5, column 2 of the range A1:C10.

Key insight: INDEX doesn't search—it just retrieves. We use MATCH to tell it where to look.

Component 2: MATCH Function (Row Finder)

Purpose: Finds the position of a value in a range

Syntax:

1=MATCH(lookup_value, lookup_array, [match_type])

Match types:

• 0 = Exact match (use this 99% of the time)
• 1 = Less than or equal to (requires sorted data)
• -1 = Greater than or equal to (requires sorted data)

Example:

1=MATCH("Apple", A1:A10, 0)

Returns 3 if "Apple" is in the 3rd row.

Component 3: MATCH Function (Column Finder)

Same function, but used to find the column position:

Example:

1=MATCH("Q1", B1:E1, 0)

Returns 2 if "Q1" is in the 2nd column of the header range.

Putting It Together

Complete formula:

1=INDEX(B2:E10, MATCH("Apple", A2:A10, 0), MATCH("Q1", B1:E1, 0))

How it works:

1. MATCH("Apple", A2:A10, 0) → Finds "Apple" in column A → Returns row position (e.g., 3)
2. MATCH("Q1", B1:E1, 0) → Finds "Q1" in row 1 → Returns column position (e.g., 2)
3. INDEX(B2:E10, 3, 2) → Returns value at row 3, column 2 of the data range

Real-World Example 1: Regional Pricing Table

The Scenario

You manage a company selling products across multiple regions with different pricing:

Your data in Excel:

• A1: "Product"
• B1:E1: Region names (East, West, South, North)
• A2:A6: Product names
• B2:E6: Prices

The Task

Create a lookup tool where you enter:

• Cell G2: Product name
• Cell H2: Region name
• Cell I2: Returns the price

The Solution

In cell I2:

1=INDEX($B$2:$E$6, MATCH(G2,$A$2:$A$6,0), MATCH(H2,$B$1:$E$1,0))

Formula breakdown:

• $B$2:$E$6 = Data range (prices) – absolute reference
• MATCH(G2,$A$2:$A$6,0) = Find product in column A
• MATCH(H2,$B$1:$E$1,0) = Find region in row 1
• $ = Lock references so formula works when copied

Test it:

• G2: "Widget B"
• H2: "West"
• I2: Returns $16 ✓

Change inputs, price updates automatically!

Add Error Handling

Prevent errors when product or region not found:

1=IFERROR(INDEX($B$2:$E$6, MATCH(G2,$A$2:$A$6,0), MATCH(H2,$B$1:$E$1,0)), "Not Found")

Now if you enter an invalid product/region, you get "Not Found" instead of #N/A.

Real-World Example 2: Employee Schedule Matrix

The Scenario

Track which employees work which shifts:

The Task

Create a lookup: "What shift does [Employee] work on [Day]?"

The Solution

Setup:

• A1: "Employee"
• B1:F1: Days of week
• A2:A5: Employee names
• B2:F5: Shift assignments

Lookup formula (G4):

1=INDEX($B$2:$F$5, MATCH(G2,$A$2:$A$5,0), MATCH(H2,$B$1:$F$1,0))

Usage:

• G2: "Mike"
• H2: "Thursday"
• Result: "Morning"

Add Conditional Formatting

Highlight the found cell:

1. Select B2:F5
2. Conditional Formatting → New Rule
3. "Use a formula to determine which cells to format"
4. Formula: =AND($A2=$G$2, B$1=$H$2)
5. Format: Yellow fill

Now when you look up "Mike" + "Thursday", that cell highlights!

Real-World Example 3: Sales Dashboard by Product & Quarter

The Scenario

Multi-quarter sales data by product:

The Task

Dashboard with dropdowns to select product and quarter, displaying sales.

The Solution

Step 1: Create dropdown lists

In cells G2 and H2:

• Data → Data Validation → List
• G2 source: Product names
• H2 source: Quarter names

Step 2: INDEX MATCH MATCH formula (I2):

1=INDEX($B$2:$F$4, MATCH(G2,$A$2:$A$4,0), MATCH(H2,$B$1:$F$1,0))

Step 3: Format result as currency

• Select I2
• Format → Currency → $0,000

Step 4: Add visual indicator

Show growth vs previous quarter:

1=I2 - INDEX($B$2:$F$4, MATCH(G2,$A$2:$A$4,0), MATCH(H2,$B$1:$F$1,0)-1)

Bonus: Add sparklines

• Select J2
• Insert → Sparklines → Line
• Data range: The entire row for selected product

Now you have a dynamic dashboard that updates based on dropdown selections!

Advanced Technique 1: Approximate Match

Find the closest match when exact match doesn't exist.

Use Case: Commission Tiers

Sales reps earn commission based on sales tiers:

Formula to find commission rate:

1=INDEX($B$2:$B$6, MATCH(E2,$A$2:$A$6,1))

Key difference: MATCH uses 1 (less than or equal) instead of 0 (exact).

Requirements:

• Lookup column must be sorted ascending
• Returns largest value less than or equal to lookup value

Example:

• E2: $37,500 (sales amount)
• Result: 10% (matches $25,000 tier)

Advanced Technique 2: Dynamic Named Ranges

Make formulas more readable using named ranges.

Instead of:

1=INDEX($B$2:$F$6, MATCH(G2,$A$2:$A$6,0), MATCH(H2,$B$1:$F$1,0))

Define names (Formulas → Name Manager):

• PriceTable = $B$2:$F$6
• Products = $A$2:$A$6
• Regions = $B$1:$F$1

Use names:

1=INDEX(PriceTable, MATCH(G2,Products,0), MATCH(H2,Regions,0))

Benefits:

• Much more readable
• Self-documenting
• Easier to maintain
• Reduces errors

Advanced Technique 3: Multiple Criteria Lookup

Combine criteria to create unique lookup keys.

Use Case: Product + Size + Color

Find price based on three criteria:

Helper column approach:

Column E (helper):

1=A2&B2&C2

Result: "ShirtSRed", "ShirtMRed", etc.

Lookup formula:

1=INDEX($D$2:$D$10, MATCH(G2&H2&I2, $E$2:$E$10, 0))

Where:

• G2: Product input
• H2: Size input
• I2: Color input

Array formula approach (no helper column needed):

1=INDEX($D$2:$D$10, MATCH(1, ($A$2:$A$10=G2)*($B$2:$B$10=H2)*($C$2:$C$10=I2), 0))

Enter with: Ctrl+Shift+Enter (array formula)

How it works:

• Each condition returns TRUE (1) or FALSE (0)
• Multiplying creates: 1×1×1=1 only for matching row
• MATCH finds the row with value=1

Advanced Technique 4: Return Multiple Values

Create a formula that returns all matches, not just the first.

Use case: Find all employees scheduled for "Morning" shift on Monday.

Formula (requires Excel 365):

1=FILTER($A$2:$A$5, $B$2:$B$5="Morning")

Alternative with INDEX MATCH for older Excel: Use helper column with incrementing counter, then INDEX/MATCH each occurrence.

Troubleshooting Common Errors

Error 1: #N/A

Meaning: MATCH couldn't find the value

Solutions:

1. Check spelling (exact match required)
2. Check for extra spaces: Use TRIM function
3. Verify value exists in lookup range
4. Add IFERROR wrapper

1=IFERROR(INDEX(...), "Value not found")

Error 2: #REF!

Meaning: Reference is invalid

Solutions:

1. Check that ranges haven't been deleted
2. Verify INDEX range is larger than MATCH return value
3. Check column/row references align with data

Error 3: Wrong Results

Causes:

1. Forgot absolute references: $ missing, ranges shift when copied
2. Misaligned ranges: Row headers don't align with data rows
3. Hidden rows/columns: MATCH counts hidden cells
4. Data type mismatch: Text vs. numbers

Fix: Lock references with $ and verify range alignment.

Error 4: Slow Performance

Cause: Complex formulas in large datasets recalculate often

Solutions:

1. Use tables (Format as Table) for structured references
2. Reduce volatile functions (NOW, TODAY, RAND)
3. Consider Power Query for large data transformations
4. Use manual calculation: Formulas → Calculation Options → Manual

INDEX MATCH MATCH vs XLOOKUP

Excel 365 introduced XLOOKUP, which can replace some INDEX MATCH scenarios.

XLOOKUP for 2D Lookup

Syntax:

1=XLOOKUP(lookup_value, lookup_array, return_array)

For two-way lookup, nest XLOOKUP:

1=XLOOKUP(H2, $B$1:$F$1, XLOOKUP(G2, $A$2:$A$6, $B$2:$F$6))

Comparison

Recommendation:

• Excel 365: Learn XLOOKUP (simpler)
• Excel 2019/older: Master INDEX MATCH MATCH
• Compatibility: Use INDEX MATCH MATCH (works everywhere)

Practice Exercises

Exercise 1: Product Inventory Lookup

Data:

• Products in column A
• Warehouses (Regions) in row 1
• Stock quantities in B2:E6

Task: Create lookup returning stock quantity for any product/warehouse combination.

Answer:

1=INDEX($B$2:$E$6, MATCH(G2,$A$2:$A$6,0), MATCH(H2,$B$1:$E$1,0))

Exercise 2: Exam Scores Lookup

Data:

• Students in column A
• Subjects in row 1
• Scores in B2:F10

Task: Create grade lookup showing letter grade based on percentage score.

Hint: Combine INDEX MATCH MATCH with IF or IFS for grade conversion.

Answer:

1=IFS(
2  INDEX($B$2:$F$10, MATCH(G2,$A$2:$A$10,0), MATCH(H2,$B$1:$F$1,0))>=90, "A",
3  INDEX($B$2:$F$10, MATCH(G2,$A$2:$A$10,0), MATCH(H2,$B$1:$F$1,0))>=80, "B",
4  INDEX($B$2:$F$10, MATCH(G2,$A$2:$A$10,0), MATCH(H2,$B$1:$F$1,0))>=70, "C",
5  TRUE, "F"
6)

Exercise 3: Multi-Criteria Product Finder

Data:

• Products with Category, Size, Color, Price in columns A:D

Task: Find price matching all three criteria.

Answer (array formula):

1=INDEX($D$2:$D$50, MATCH(1, ($A$2:$A$50=G2)*($B$2:$B$50=H2)*($C$2:$C$50=I2), 0))

Best Practices

1. Always use absolute references for data ranges

1✓ $B$2:$F$6
2✗ B2:F6

2. Use IFERROR for user-friendly errors

1=IFERROR([formula], "Not Found")

3. Name your ranges

1=INDEX(SalesData, MATCH(Product, ProductList, 0), MATCH(Quarter, QuarterList, 0))

4. Verify data alignment

• Headers in row 1
• Row labels in column A
• Data starts at B2

5. Test with known values

• Input values you can manually verify
• Check edge cases (first row, last column)

6. Document complex formulas

• Add comments (Review → New Comment)
• Use named ranges for clarity
• Keep a formula reference sheet

Frequently Asked Questions

Can INDEX MATCH MATCH work with text and numbers?

Yes, but ensure data types match:

• Text "100" ≠ Number 100
• Use VALUE() to convert text to numbers
• Use TEXT() to convert numbers to text

What's the maximum size for INDEX MATCH MATCH?

Excel limits:

• Rows: 1,048,576
• Columns: 16,384
• Formula length: 8,192 characters

Practically, INDEX MATCH handles millions of cells efficiently.

How do I look up values in multiple sheets?

Use 3D references or INDIRECT:

1=INDEX(INDIRECT("'"&G3&"'!$B$2:$E$6"), MATCH(G2,$A$2:$A$6,0), MATCH(H2,$B$1:$E$1,0))

Where G3 contains sheet name.

Can I return the entire row or column instead of one value?

Yes, omit the row or column number:

Return entire row:

1=INDEX($B$2:$F$6, MATCH(G2,$A$2:$A$6,0), 0)

Return entire column:

1=INDEX($B$2:$F$6, 0, MATCH(H2,$B$1:$F$1,0))

(Note: Returns array, works differently in different Excel versions)

How do I debug complex INDEX MATCH formulas?

Step-by-step approach:

1. Test each MATCH separately: =MATCH(G2,$A$2:$A$6,0)
2. Verify it returns expected row/column number
3. Test INDEX with hardcoded numbers: =INDEX($B$2:$F$6, 3, 2)
4. Combine once each part works

Is INDEX MATCH MATCH faster than VLOOKUP?

Yes, especially in large datasets:

• VLOOKUP: Scans entire lookup column every time
• INDEX MATCH: Direct position lookup (faster)
• Difference negligible in small data (<1000 rows)
• Significant in large data (>10,000 rows)

Conclusion

INDEX MATCH MATCH is the Excel power user's secret weapon for two-dimensional lookups. While VLOOKUP forces you into rigid, one-directional lookups, INDEX MATCH MATCH gives you complete flexibility to find values at the intersection of any row and column.

Key takeaways:

• INDEX retrieves values by position
• MATCH (×2) finds row and column positions
• Combine for powerful two-way lookups
• Works in all Excel versions (unlike XLOOKUP)
• More flexible and robust than VLOOKUP

Master this formula, and you'll handle complex Excel lookups that stump most users. Your coworkers will think you're an Excel wizard (and you'll be right).

Next steps:

1. Practice with the exercises above
2. Apply to your real-world data
3. Combine with other functions (IF, IFERROR, SUMIFS)
4. Explore XLOOKUP if you have Excel 365

Time to master: 2-3 hours of practice
Career value: Immense (advanced Excel = higher pay)

Related articles: XLOOKUP Excel Tutorial: Replace VLOOKUP for Good, Excel Power Query Transformations That Eliminate Data Cleaning