INDEX and MATCH combination in Google Sheets - Step-by-Step Guide

Introduction

This feature helps you find a value in a table by looking up a matching item in one column and returning a 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 your data is not sorted and you want to find matching information without errors.

When you want to look up values from the left side of a table, which VLOOKUP cannot do easily.

When you want to combine two functions to make your lookup more powerful and flexible.

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 A2:A10 to select the column with values to return

Step 4: Type

- formula bar after the range

You add a comma and start the MATCH function

💡 Type MATCH(

Step 5: Type

- inside MATCH function

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

💡 For example, MATCH(B2, C2:C10, 0) looks for exact match of B2 in C2:C10

Step 6: Type

- formula bar

Close both functions with parentheses and press Enter

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

Step 7: Press

- Enter key

The cell shows the value found by the combined INDEX and MATCH formula

Before vs After

Before

Cell is empty or has a manual value unrelated to lookup

After

Cell shows the value from column A that matches the lookup value in B2 found in column C

Settings Reference

match_type

📍 third argument of MATCH function

Defines how MATCH finds the lookup value; 0 is most common for exact matches

Default: 0

Common Mistakes

Using MATCH with match_type other than 0 when data is unsorted

MATCH may return wrong position if data is not sorted and match_type is not 0

Always use 0 for exact match unless you know your data is sorted and want approximate match

Swapping the ranges in INDEX and MATCH

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

Make sure INDEX range is the result column, MATCH range is the lookup column

Not closing parentheses properly

Formula will show error or not calculate

Count and close all parentheses for both INDEX and MATCH functions

Summary

INDEX and MATCH together let you look up values flexibly in Google Sheets.

Use MATCH to find the position of your lookup value, then INDEX to get the result from that position.

Always use match_type 0 in MATCH for exact matches to avoid errors.

Practice

1. What does the INDEX function do in Google Sheets when combined with MATCH?

easy

A. Deletes duplicate values from a range

B. Finds the position of a value in a range

C. Sorts the data in ascending order

D. Returns the value at a specific position in a range

Solution

1. Step 1: Understand the role of INDEX

   The INDEX function returns the value located at a specific row and column in a given range.

2. Step 2: Understand the role of MATCH

   The MATCH function finds the position (row or column number) of a specific value within a range.

3. Final Answer:

   Returns the value at a specific position in a range -> Option D

4. Quick Check:

   INDEX returns value, MATCH finds position [OK]

Hint: INDEX gives value; MATCH gives position [OK]

Common Mistakes:

• Confusing INDEX with MATCH roles
• Thinking INDEX sorts data
• Assuming MATCH returns values

2. Which of the following is the correct syntax to use INDEX and MATCH together to find a value in column B based on a lookup value in column A?

easy

A. =INDEX(A1:A10, MATCH(B1:B10, "Apple", 0))

B. =MATCH(B1:B10, INDEX("Apple", A1:A10, 0))

C. =INDEX(B1:B10, MATCH("Apple", A1:A10, 0))

D. =MATCH("Apple", B1:B10, INDEX(A1:A10, 0))

Solution

1. Step 1: Check INDEX syntax

   INDEX(range, row_number) returns value at row_number in range.

2. Step 2: Check MATCH syntax

   MATCH(lookup_value, lookup_range, 0) finds exact position of lookup_value in lookup_range.

3. Step 3: Combine correctly

   Use MATCH to find row in A1:A10, then INDEX to get value from B1:B10 at that row.

4. Final Answer:

   =INDEX(B1:B10, MATCH("Apple", A1:A10, 0)) -> Option C

5. Quick Check:

   INDEX(B, MATCH(value, A)) correct syntax [OK]

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

Common Mistakes:

• Swapping INDEX and MATCH arguments
• Using MATCH on wrong range
• Incorrect order of parameters

3. Given the data:
A1:A4 = {"Cat", "Dog", "Bird", "Fish"}
B1:B4 = {5, 3, 8, 2}
What is the result of =INDEX(B1:B4, MATCH("Bird", A1:A4, 0))?

medium

A. 3

B. 8

C. 2

D. 5

Solution

1. Step 1: Find position of "Bird" in A1:A4 using MATCH

   "Bird" is at position 3 in A1:A4.

2. Step 2: Use INDEX to get value at position 3 in B1:B4

   Value at B3 is 8.

3. Final Answer:

   8 -> Option B

4. Quick Check:

   INDEX(B, MATCH("Bird", A)) = 8 [OK]

Hint: MATCH finds row 3, INDEX returns B3 = 8 [OK]

Common Mistakes:

• Confusing row numbers
• Using wrong ranges
• Assuming MATCH returns value

4. You wrote the formula =INDEX(B1:B5, MATCH("Orange", A1:A5, 0)) but it returns an error. What is the most likely cause?

medium

A. The value "Orange" is not found in A1:A5

B. The ranges B1:B5 and A1:A5 have different sizes

C. INDEX cannot be combined with MATCH

D. MATCH requires a third argument of 1 or -1

Solution

1. Step 1: Understand MATCH error

   MATCH returns #N/A error if lookup value is not found in the range.

2. Step 2: Check if "Orange" exists in A1:A5

   If "Orange" is missing, MATCH fails and INDEX cannot get a valid row.

3. Final Answer:

   The value "Orange" is not found in A1:A5 -> Option A

4. Quick Check:

   MATCH missing value causes error [OK]

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

Common Mistakes:

• Assuming INDEX and MATCH can't combine
• Ignoring #N/A from MATCH
• Wrong MATCH match_type argument

5. You have a table with names in A2:A10 and scores in B2:B10. You want to find the score of the name in D2, but if the name is not found, return "Not Found" instead of an error. Which formula correctly does this?

hard

A. =IFERROR(INDEX(B2:B10, MATCH(D2, A2:A10, 0)), "Not Found")

B. =INDEX(B2:B10, MATCH(D2, A2:A10, 0)) & "Not Found"

C. =IF(ISERROR(MATCH(D2, A2:A10, 0)), "Not Found", INDEX(B2:B10, D2))

D. =MATCH(D2, A2:A10, 0) & INDEX(B2:B10, D2)

Solution

1. Step 1: Use MATCH to find position of D2 in A2:A10

   MATCH(D2, A2:A10, 0) finds exact row of the name.

2. Step 2: Use INDEX to get score from B2:B10 at that position

   INDEX(B2:B10, MATCH(...)) returns the score.

3. Step 3: Use IFERROR to catch errors if name not found

   IFERROR(..., "Not Found") returns "Not Found" instead of error.

4. Final Answer:

   =IFERROR(INDEX(B2:B10, MATCH(D2, A2:A10, 0)), "Not Found") -> Option A

5. Quick Check:

   IFERROR wraps INDEX-MATCH to handle missing names [OK]

Hint: Wrap INDEX-MATCH with IFERROR for safe lookup [OK]

Common Mistakes:

• Concatenating strings instead of handling errors
• Using ISERROR with repeated MATCH calls
• Incorrect argument order in MATCH