INDEX-MATCH combination in Excel - Step-by-Step Guide

Introduction

The INDEX-MATCH combination helps you find information in a table by looking up a value in one column and returning a related value from another column. It solves the problem of searching for data when you want more flexibility than simple lookup functions.

When you want to find a price of a product by searching its name in a list.

When you need to get an employee's department by looking up their ID number.

When you want to find a student's grade by searching their name in a gradebook.

When your data has columns in any order and you want to look up values without rearranging.

When you want a more reliable alternative to VLOOKUP that can look left or right.

Steps

Step 1: Click

- cell where you want the result

The cell is selected and ready for formula input

Step 2: Type

- the selected cell

Formula input starts

💡 Start typing =INDEX(

Step 3: Type

- formula bar

You enter the range of cells where the result will come from

💡 For example, type B2:B10,

Step 4: Type

- formula bar

You start the MATCH function to find the row number

💡 Type MATCH( to begin

Step 5: Type

- formula bar

You enter the lookup value, lookup range, and match type

💡 For example, MATCH(D2, A2:A10, 0) looks for exact match of D2 in A2:A10

Step 6: Type

- formula bar

You close the parentheses to complete the formula

💡 The full formula looks like =INDEX(B2:B10, MATCH(D2, A2:A10, 0))

Step 7: Press

- Enter key

The cell shows the value from column B that matches the lookup value in D2

Before vs After

Before

Column A has product names, column B has prices, cell D2 has a product name to find

After

The formula cell shows the price of the product named in D2 by looking up columns A and B

Settings Reference

Match type

📍 Inside MATCH function formula

Defines how MATCH finds the lookup value

Default: 0

Common Mistakes

Using MATCH with wrong match type

It can return wrong row numbers if data is not sorted or exact match is needed

Always use 0 as the third argument in MATCH for exact matches unless you know your data is sorted

Swapping the ranges in INDEX and MATCH

The INDEX range must be where you want the result, MATCH range must be where you search

Make sure INDEX range is the column with results, MATCH range is the lookup column

Summary

INDEX-MATCH lets you look up values flexibly in any column order.

Use MATCH to find the row number, and INDEX to get the value from that row.

Always use 0 as match type in MATCH for exact lookups.