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.