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.

Practice

1. What is the main advantage of using INDEX-MATCH over VLOOKUP in Excel?

easy

A. It automatically sorts the data for faster lookup.

B. It only works with numeric data.

C. It can look up values in any column, not just to the right.

D. It requires less typing than VLOOKUP.

Solution

1. Step 1: Understand VLOOKUP limitation

   VLOOKUP can only search for a value in the first column and return a value to the right.

2. Step 2: Understand INDEX-MATCH flexibility

   INDEX-MATCH can look up values anywhere in the table, left or right, because MATCH finds the position and INDEX returns the value.

3. Final Answer:

   It can look up values in any column, not just to the right. -> Option C

4. Quick Check:

   INDEX-MATCH is more flexible than VLOOKUP [OK]

Hint: INDEX-MATCH can look left or right, VLOOKUP only right [OK]

Common Mistakes:

• Thinking VLOOKUP can look left
• Believing INDEX-MATCH sorts data automatically
• Assuming INDEX-MATCH only works with numbers

2. Which of the following is the correct syntax for using INDEX and MATCH together to find a value in Excel?

easy

A. =MATCH(INDEX(A2:A10), "Apple", B2:B10)

B. =INDEX(A2:A10, MATCH("Apple", B2:B10, 0))

C. =INDEX(MATCH("Apple", A2:A10, 0), B2:B10)

D. =MATCH(A2:A10, INDEX("Apple", B2:B10, 0))

Solution

1. Step 1: Understand MATCH syntax

   MATCH("Apple", B2:B10, 0) finds the position of "Apple" in B2:B10 with exact match (0).

2. Step 2: Understand INDEX syntax

   INDEX(A2:A10, position) returns the value in A2:A10 at the position found by MATCH.

3. Final Answer:

   =INDEX(A2:A10, MATCH("Apple", B2:B10, 0)) -> Option B

4. Quick Check:

   INDEX uses MATCH position correctly [OK]

Hint: MATCH finds position, INDEX returns value at that position [OK]

Common Mistakes:

• Swapping INDEX and MATCH arguments
• Using MATCH as first argument of INDEX
• Incorrect order of parameters

3. Given the table:

  A       B
1 Fruit   Price
2 Apple   3
3 Banana  2
4 Cherry  5

What is the result of the formula =INDEX(B2:B4, MATCH("Banana", A2:A4, 0))?

medium

A. 2

B. 3

C. 5

D. #N/A

Solution

1. Step 1: Use MATCH to find position of "Banana"

   MATCH("Banana", A2:A4, 0) searches A2:A4 and finds "Banana" at position 2.

2. Step 2: Use INDEX to get price at position 2

   INDEX(B2:B4, 2) returns the value in B3, which is 2.

3. Final Answer:

   2 -> Option A

4. Quick Check:

   Banana price = 2 [OK]

Hint: MATCH finds row, INDEX returns value from that row [OK]

Common Mistakes:

• Confusing row numbers with positions
• Using wrong ranges for INDEX or MATCH
• Forgetting exact match (0) in MATCH

4. You wrote the formula =INDEX(B2:B5, MATCH("Orange", A2:A5, 0)) but it returns #N/A. What is the most likely reason?

medium

A. You forgot to use absolute references ($).

B. The ranges B2:B5 and A2:A5 are not the same size.

C. INDEX cannot be used with MATCH.

D. "Orange" is not found in the range A2:A5.

Solution

1. Step 1: Understand MATCH error

   MATCH("Orange", A2:A5, 0) returns #N/A if "Orange" is not found in A2:A5.

2. Step 2: Check formula behavior

   Since MATCH returns #N/A, INDEX cannot return a value and the whole formula returns #N/A.

3. Final Answer:

   "Orange" is not found in the range A2:A5. -> Option D

4. Quick Check:

   Missing lookup value causes #N/A [OK]

Hint: Check if lookup value exists before using MATCH [OK]

Common Mistakes:

• Assuming #N/A is due to range size mismatch
• Thinking INDEX can't work with MATCH
• Ignoring need for exact match in MATCH

5. You have a table with columns Product (A2:A6), Category (B2:B6), and Price (C2:C6). How would you use INDEX-MATCH to find the price of the first product in the "Fruit" category?

hard

A. =INDEX(C2:C6, MATCH("Fruit", B2:B6, 0))

B. =INDEX(B2:B6, MATCH("Fruit", A2:A6, 0))

C. =MATCH("Fruit", C2:C6, 0)

D. =INDEX(C2:C6, MATCH("Fruit", C2:C6, 0))

Solution

1. Step 1: Use MATCH to find position of "Fruit" in Category

   MATCH("Fruit", B2:B6, 0) finds the first row where Category is "Fruit".

2. Step 2: Use INDEX to get Price at that position

   INDEX(C2:C6, position) returns the price of the product in that row.

3. Final Answer:

   =INDEX(C2:C6, MATCH("Fruit", B2:B6, 0)) -> Option A

4. Quick Check:

   Find category position, then get price [OK]

Hint: MATCH category, INDEX price to get correct value [OK]

Common Mistakes:

• Matching category in wrong column
• Using MATCH on price column instead of category
• Swapping INDEX and MATCH ranges