How to Use INDEX and MATCH in Google Sheets
=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])
reference: the range INDEX returns a value from.rowandcolumn(optional): the position inside that range. Both default to 0. Per Google's INDEX page, a row of 0 returns the whole column, and a column of 0 returns the whole row.search_key: the value to find.range: a one-dimensional range to search. According to Google's MATCH page, a range that is taller and wider than one cell makes MATCH return #N/A.search_type(optional, default 1):0for an exact match,1for sorted ascending data,-1for sorted descending data.
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.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Code | Price |
| 2 | Pen | K-101 | 1.5 |
| 3 | Notebook | K-102 | 4.25 |
| 4 | Stapler | K-103 | 9 |
| 5 | Tape | K-104 | 2.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:
MATCH("K-103", B2:B5, 0)looks for K-103 in the codes and returns its position,3.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
- Forgetting the 0. The most common cause of a wrong answer.
- Return and search ranges of different lengths. If
A2:A5andB2:B6are offset, the position points to the wrong row. - Using a two-dimensional range in MATCH. MATCH needs a single row or column.
- A value that is not in the list. MATCH then returns #N/A. Wrap the formula in IFERROR to show a friendlier message.
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.
Exercise 2: Look to the right
Write a formula that returns the Price (column C) of the product named Tape.
What to learn next
- VLOOKUP: fetch one value from a table.
- XLOOKUP: look up a value in any direction.
- IFERROR: replace an error with a clean result.
- HLOOKUP: look up a value across a row.
- MATCH: find the position of a value.
Syntax and rules in this guide were checked against Google's INDEX and MATCH help page. See all guides.