Two-Way Lookups With INDEX-MATCH-MATCH

Imagine a grid of monthly sales where regions run down the left and months run across the top. You want the number where a chosen region meets a chosen month.

A normal lookup finds a value by searching one direction only. A two-way lookup searches in both directions at once: it finds the right row and the right column, then returns the value sitting at their intersection.

The classic tool for this is INDEX combined with two MATCH calls, often written INDEX-MATCH-MATCH.

INDEX returns a value from a range by its position. The full form is INDEX(array, row_num, column_num).

Give it a block of cells, a row number, and a column number, and it hands back the value at that spot. For example, in a grid starting at B2, asking for row 3 and column 2 returns the value 3 rows down and 2 columns across inside that block.

The key idea: INDEX needs positions, not labels. That is exactly what MATCH provides.

MATCH finds the position of a value inside a single row or column. Its form is MATCH(lookup_value, lookup_array, match_type).

Use 0 as the match type for an exact match. The result is a number: where the value sits, counting from 1.

If "East" is the second item in the range A2:A5, then MATCH returns 2. That 2 can become the row number for INDEX.

For a two-way lookup you run MATCH twice:

• One MATCH finds which row your region is in.
• One MATCH finds which column your month is in.

Then you feed both numbers into INDEX. The row MATCH searches a vertical range of labels; the column MATCH searches a horizontal range of headers.

The result is the single cell where that row and column cross.

Picture this layout. Region labels sit in A2:A5 (East, West, North, South). Month headers sit in B1:D1 (Jan, Feb, Mar). The actual sales numbers fill B2:D5.

Two input cells drive the lookup: G1 holds the region you want, and G2 holds the month you want.

Our goal: a single formula that reads G1 and G2 and returns the matching sales figure from B2:D5.

First locate the region. MATCH searches the vertical label list A2:A5 for the value typed in G1.

If G1 contains "North", and North is the third label, this MATCH returns 3.

This number tells INDEX which row of the data block to read. Notice we search A2:A5, the labels only, not the data, so position 3 lines up with the third data row.

Next locate the month. This MATCH searches the horizontal header row B1:D1 for the value in G2.

If G2 contains "Feb", and Feb is the second header, MATCH returns 2.

That number becomes the column position for INDEX. As with the rows, we search only the headers B1:D1 so the position lines up with the data columns in B2:D5.

Now wrap both MATCH calls inside INDEX. The data block B2:D5 is the array, the row MATCH supplies the row number, and the column MATCH supplies the column number.

When G1 is "North" and G2 is "Feb", the row MATCH gives 3 and the column MATCH gives 2, so INDEX returns the value at row 3, column 2 of B2:D5.

This single formula is the complete two-way lookup.

Say B2:D5 holds: North's row is Jan 50, Feb 80, Mar 65.

• MATCH("North", A2:A5, 0) returns 3.
• MATCH("Feb", B1:D1, 0) returns 2.
• INDEX(B2:D5, 3, 2) reads row 3, column 2, returning 80.

Change G1 to "East" or G2 to "Mar" and the whole formula recalculates instantly. That is the power of driving INDEX with two MATCH lookups.

VLOOKUP only searches the first column and returns a value a fixed number of columns to the right. To switch months you would have to hard-code or compute the column index yourself.

INDEX-MATCH-MATCH lets both the row and the column be chosen dynamically by label. Reorder columns, insert new months, and the formula still works because it matches on the header text, not a fixed number.

The most common mistake is mismatched ranges. The row MATCH range must have the same height as the INDEX data block, and the column MATCH range must have the same width.

Here A2:A5 is 4 rows tall and B2:D5 is also 4 rows tall, so a MATCH result of 3 truly means the third data row. If you accidentally search A1:A5 (which includes a header), the positions shift by one and you get the wrong cell.

Test your understanding of the two-way lookup pattern.

You learned the two-way lookup pattern:

• INDEX returns a value at a row and column position inside a block.
• One MATCH finds the row by searching vertical labels.
• A second MATCH finds the column by searching horizontal headers.

The combined formula =INDEX(data, MATCH(row), MATCH(col)) reads two inputs and returns the value at their intersection. Keep the MATCH ranges the same size as the data block to avoid misalignment.