How to Use the INDEX and MATCH Functions for Dynamic Two-Way Lookups in Excel

The Limitation of Basic Lookups

If you need to find a specific piece of data in an Excel table-such as finding the Salary of an employee based on their ID number-the standard VLOOKUP or the newer XLOOKUP functions work perfectly. They search vertically down a single column to find a match, then return a value from a corresponding column.

However, real-world financial matrices and shipping rate tables are often two-dimensional. What if you need to find a shipping rate where the rows represent the Package Weight and the columns represent the Shipping Zone? You don’t just need a vertical lookup; you need a dynamic, two-way intersection. VLOOKUP cannot easily do this. The most robust, professional way to perform a two-way lookup is by combining the INDEX and MATCH functions.

Understanding the Two Functions

Before combining them, you must understand how they work individually.

1. The INDEX Function (The Map)

INDEX returns the value of a cell inside a specific grid based on its row and column coordinates.

Syntax: =INDEX(Grid_Area, Row_Number, Column_Number)

If you highlight a grid of data (A1:E5), and you tell INDEX to give you the data in Row 3, Column 2, it will output exactly what is in that specific cell.

2. The MATCH Function (The GPS)

MATCH searches for a specific word or number within a single column (or row) and tells you its numerical position (e.g., “That word is the 4th item in the list”).

Syntax: =MATCH(What_To_Find, Where_To_Look, 0)

Note: The 0 at the end is crucial. It forces Excel to find an exact match.

Step-by-Step: Building the Two-Way Lookup

Assume you have a shipping rate table:

• Rows (A2:A5): Weights (1lb, 2lb, 3lb, 4lb)
• Columns (B1:D1): Zones (Zone A, Zone B, Zone C)
• The Grid (B2:D5): The actual dollar amounts for shipping.

You have an input area where a user types the Weight into cell G1 (e.g., “3lb”) and the Zone into cell G2 (e.g., “Zone B”). You want cell G3 to automatically display the correct shipping cost.

Step 1: Write the INDEX Foundation

Click on cell G3 and start the INDEX formula by selecting only the grid containing the dollar amounts (the answers). Do not include the headers.

=INDEX(B2:D5,

Step 2: Use MATCH to find the Row

Now, instead of typing a hard-coded Row Number into the INDEX formula, we will use a MATCH formula to calculate it dynamically based on the Weight.

=INDEX(B2:D5, MATCH(G1, A2:A5, 0),

(Translation: Look for the weight typed in G1 within the weight list in A2:A5, and return that row number).

Step 3: Use MATCH to find the Column

Next, we use a second MATCH formula to dynamically calculate the Column Number based on the Zone.

=INDEX(B2:D5, MATCH(G1, A2:A5, 0), MATCH(G2, B1:D1, 0))

(Translation: Look for the zone typed in G2 within the zone headers in B1:D1, and return that column number).

How It Resolves

When you press Enter, Excel processes the formula from the inside out.

1. The first MATCH looks for “3lb”. It sees that “3lb” is the 3rd item in the weight list. It outputs a 3.
2. The second MATCH looks for “Zone B”. It sees that “Zone B” is the 2nd item in the header list. It outputs a 2.
3. The INDEX function receives those coordinates: =INDEX(B2:D5, 3, 2).
4. INDEX goes to the 3rd row and 2nd column of the grid and returns the exact dollar amount.

If the user changes G1 to “1lb”, the first MATCH instantly recalculates to a 1, and the final shipping cost updates instantly.

Conclusion

While XLOOKUP is the king of single-column searches, INDEX/MATCH remains the undisputed standard for two-dimensional matrices. By nesting MATCH functions inside an INDEX grid, you can build highly dynamic, bulletproof pricing models and dashboards that adapt instantly to user input.