Combining INDEX and MATCH

You now know two halves of a lookup. MATCH finds where a value is, and INDEX returns the value at a position.

Combine them and you get a complete lookup: MATCH locates the row, then INDEX pulls the data from that row in any column you choose.

The pattern is simple once you see it: put MATCH inside INDEX, where the row number normally goes.

Here is the shape you will use again and again:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Read it inside-out. MATCH runs first and returns a position number. That number then becomes the row_num for INDEX, which returns the value from your return range.

The return range and the lookup range usually have the same number of rows, so a position in one lines up with the other.

Imagine a table where column A holds product names and column C holds prices. You want the price of "Cherry."

First MATCH finds Cherry: =MATCH("Cherry", A2:A20, 0) returns, say, 3.

Then INDEX uses that 3: =INDEX(C2:C20, 3) returns the price in the 3rd row of column C.

Nest them and you get it in one go: =INDEX(C2:C20, MATCH("Cherry", A2:A20, 0)).

Hardcoding "Cherry" is fine for learning, but real formulas point at a cell instead. Put the search term in E1 and reference it.

=INDEX(C2:C20, MATCH(E1, A2:A20, 0))

Now whatever product you type in E1 instantly returns its price. Type Banana and you get Banana's price; type Date and the answer updates.

One formula becomes a reusable lookup tool driven entirely by the input cell.

Here is the trick that makes INDEX-MATCH special. The lookup column and the return column are independent, so the value you return can be to the left of the value you search.

Suppose prices are in column A and product names in column C. To find a product's price by name, you write =INDEX(A2:A20, MATCH(E1, C2:C20, 0)).

You searched column C but returned from column A. VLOOKUP cannot do this without help.

The return range decides what you get back. Searching by the same key, you can pull any column you like just by changing the INDEX range.

Find a customer's email: =INDEX(D2:D50, MATCH(E1, A2:A50, 0)).

Find that same customer's city instead: =INDEX(F2:F50, MATCH(E1, A2:A50, 0)).

The MATCH part stays identical; only the INDEX range changes to choose a different answer.

You can supply a column number to INDEX as well, found by a second MATCH. This pinpoints a value at the intersection of a row and a column.

=INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0))

The first MATCH finds the row from labels in A, the second finds the column from headers in row 1. INDEX returns the cell where they cross. This advanced pattern is covered in depth later.

For the position to line up, your lookup range and return range must start on the same row and be the same height.

If MATCH searches A2:A20 (19 rows) but INDEX returns from C2:C19 (18 rows), the positions drift and you get the wrong answer.

A reliable habit: use the exact same row span for both, like A2:A20 and C2:C20. Whole-column references such as A:A and C:C also stay aligned automatically.

If MATCH cannot find the lookup value, it returns #N/A and the whole INDEX-MATCH shows that error. Wrap it in IFNA for a tidy fallback.

=IFNA(INDEX(C2:C20, MATCH(E1, A2:A20, 0)), "Not found")

Now a missing product shows the text "Not found" instead of a scary error. IFERROR works too, but IFNA targets only the not-found case and lets other errors surface.

Put it all together. You have an employee table: IDs in column A, names in B, departments in C, salaries in D. A user types an ID into G1.

To return that employee's department: =INDEX(C2:C200, MATCH(G1, A2:A200, 0)).

To return their salary instead, swap the INDEX range to D2:D200. The lookup logic never changes, only the column you read from. This is the everyday workhorse of dynamic lookups.

When a formula looks intimidating, evaluate it the way the spreadsheet does, from the innermost function out.

For =INDEX(C2:C20, MATCH(E1, A2:A20, 0)): first read MATCH(E1, A2:A20, 0), picture it returning a number like 5, then mentally replace it to get =INDEX(C2:C20, 5).

Suddenly the formula is just "return the 5th price." This habit makes every nested lookup easy to debug.

Confirm you understand how the two functions combine.

You combined the two functions into a flexible lookup:

• Pattern: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
• MATCH finds the row position; INDEX returns the value at that position
• Lookup and return columns are independent, so you can look left as easily as right
• Keep both ranges the same height, and wrap with IFNA for clean error handling

Next, see exactly why this approach often beats VLOOKUP.