Learn INDEX, MATCH and XMATCH | Advanced Lookup Systems and Relational Modeling

Swipe to show menu

XLOOKUP is the preferred lookup function for modern Excel models, but many real-world workbooks still rely on INDEX and MATCH. In this chapter, learn how these functions work together and how XMATCH extends the same logic with more flexibility.

MATCH Structure

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value: value being searched;
lookup_array: range being searched;
[match_type]: matching behavior;
0: exact match;
1: largest value less than or equal to the lookup value;
-1: smallest value greater than or equal to the lookup value.

MATCH returns a position, not the actual value.

INDEX Structure

=INDEX(array, row_num, [col_num])

array: dataset being returned from;
row_num: row position;
[col_num]: optional column position.

INDEX retrieves a value based on position.

INDEX and MATCH Combination

=INDEX(Products[Price], MATCH("Keyboard", Products[Product], 0))

MATCH(...): finds the product position;
INDEX(...): returns the value at that position.

This recreates lookup behavior without directional limitations.

XMATCH Structure

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

lookup_value: value being searched;
lookup_array: range being searched;
[match_mode]: matching logic;
[search_mode]: search direction.

XMATCH returns positions just like MATCH, but adds modern search controls similar to XLOOKUP.

Step 1 Test MATCH Independently

Inside the Summary sheet, type:

=MATCH("Laptop", Products[Product], 0)

Confirm that Excel returns the position of Laptop inside the Products table.

Step 2 Test INDEX Independently

Type:

=INDEX(Products[Price], 3)

Confirm that the formula returns the value at position 3.

Step 3 Combine INDEX and MATCH

Type:

=INDEX(Products[Price], MATCH("Keyboard", Products[Product], 0))

The formula now retrieves the matching product price dynamically.

Step 4 Apply Structured References

Replace the fixed lookup value with:

=INDEX(Products[Price], MATCH([@Product], Products[Product], 0))

[@Product]: current row Product value;
Products[Product]: lookup column;
Products[Price]: returned value column.

The formula now works dynamically inside the table structure.

Step 5 Test XMATCH

Type:

=XMATCH("Gaming Chair", Products[Product])

Confirm that Excel returns the matching row position.

Step 6 Build a Two-Dimensional Lookup

Type:

=INDEX(Products, XMATCH("Gaming Chair", Products[Product]), XMATCH("Cost", Products[#Headers]))

First XMATCH(...): retrieves the row position;
Second XMATCH(...): retrieves the column position;
INDEX(...): returns the intersecting value.

This creates a fully dynamic row-column lookup system.

Step 7 Test Dynamic Flexibility

Change both the product and column values inside the formula.

Confirm that the result updates dynamically based on the selected row and column combination.

Everything was clear?

Thanks for your feedback!

Section 3. Chapter 4

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 3. Chapter 4

INDEX, MATCH and XMATCH

MATCH Structure

INDEX Structure

INDEX and MATCH Combination

XMATCH Structure

1. What is the main role of `MATCH` in the `INDEX`/`MATCH` combination?

2. Why is `INDEX` required when using `MATCH`?

3. What is the key advantage of using `INDEX` with `XMATCH` for two-dimensional lookups?

INDEX, MATCH and XMATCH

MATCH Structure

INDEX Structure

INDEX and MATCH Combination

XMATCH Structure

1. What is the main role of MATCH in the INDEX/MATCH combination?

2. Why is INDEX required when using MATCH?

3. What is the key advantage of using INDEX with XMATCH for two-dimensional lookups?

1. What is the main role of `MATCH` in the `INDEX`/`MATCH` combination?

2. Why is `INDEX` required when using `MATCH`?

3. What is the key advantage of using `INDEX` with `XMATCH` for two-dimensional lookups?