How to Use INDEX and MATCH in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Intermediate · 8 min read · includes 2 free exercises

In short: MATCH finds the position of a value in a list, and INDEX returns the item at a position. Together, =INDEX(return_range, MATCH(search_key, search_range, 0)) looks up a value in any direction.

What INDEX and MATCH do

INDEX returns the value at a given position in a range. MATCH returns the position of a value in a list. Put MATCH inside INDEX and you have a lookup: MATCH finds where the item is, and INDEX fetches the matching item from another column. Unlike VLOOKUP, the column you search does not have to be to the left of the column you return.

The syntax

=INDEX(reference, [row], [column])
=MATCH(search_key, range, [search_type])

A step-by-step example

Here is a product table in cells A1:C5. The code is in column B, to the right of the name.

ABC
1NameCodePrice
2PenK-1011.5
3NotebookK-1024.25
4StaplerK-1039
5TapeK-1042.75

To find the name of the product with code K-103:

=INDEX(A2:A5, MATCH("K-103", B2:B5, 0))

This returns Stapler. Here is why:

  1. MATCH("K-103", B2:B5, 0) looks for K-103 in the codes and returns its position, 3.
  2. INDEX(A2:A5, 3) returns the third item in the name column: Stapler.

VLOOKUP cannot do this directly, because it only returns columns to the right of the one it searches.

Always use 0 for an exact match

If you leave out the last argument of MATCH, it uses the default 1, which assumes your list is sorted ascending and returns the largest value that is less than or equal to the key. On an unsorted list that gives wrong results without any error message. For lookups of names and codes, write 0.

Common mistakes

Try it yourself

Use the product table above (A1:C5). Type your formula into the box, starting with =, then press Check.

Exercise 1: Look to the left

Write a formula that returns the Name (column A) of the product whose Code is K-103. Use INDEX and MATCH with an exact match.

Hint: MATCH("K-103", B2:B5, 0) gives the row position.

Exercise 2: Look to the right

Write a formula that returns the Price (column C) of the product named Tape.

Hint: search column A for "Tape", return from column C.

What to learn next

Syntax and rules in this guide were checked against Google's INDEX and MATCH help page. See all guides.