INDEX MATCH in Google Sheets: How It Works and Examples

Yes. Google Sheets supports both INDEX and MATCH, and combining them lets you find a value in one range and return a related value from another. For an exact lookup, the core pattern is =INDEX(return_range,MATCH(search_key,search_range,0)); the final 0 tells MATCH to look for an exact match.

What INDEX MATCH Means in Google Sheets

INDEX MATCH is not a separate Google Sheets function. It is the common name for a formula that nests the MATCH function inside INDEX. MATCH finds where a requested item appears in a range, while INDEX uses that position to return a value from another range.

This pairing is useful when a sheet contains related information in separate columns. For example, a product list might have product codes in one column and product names in another. MATCH can locate the row containing a code, and INDEX can return the name from that same row. The functions work in Google Sheets in a web browser, and the basic formula pattern is also familiar to Excel users.

It is often presented as an alternative to VLOOKUP. One reason is flexibility: the return range does not have to sit to the right of the search range. The formula can look in a column on the right and return a value from a column on the left, or work with a two-dimensional table.

How the Two Functions Work Together

MATCH returns a position within the range you give it, not the worksheet row number. If the search range is A2:A6 and the match is in its third cell, MATCH returns 3. INDEX then uses that relative position in the return range. If the return range is B2:B6, position 3 points to B4.

The general pattern for a vertical lookup is =INDEX(return_range,MATCH(search_key,search_range,0)). The ranges should cover the same number of rows and follow the same order. If the search range starts at row 2 but the return range starts at row 3, the position may point to the wrong record even though the formula is syntactically valid.

The arguments have distinct jobs:

search_key is the value you want to find, such as a product code in a separate input cell.
search_range is the one-column or one-row range where MATCH searches.
0 selects exact matching, which is usually the safest choice for IDs, names, and codes.
return_range contains the values INDEX should return, aligned with the search range.

Build a Basic Exact-Match Lookup

Suppose A2:A6 contains SKU-101, SKU-102, SKU-103, SKU-104, and SKU-105, while B2:B6 contains Notebook, Pen, Folder, Marker, and Stapler in the corresponding rows. Cell D2 contains the code you want to look up, SKU-103. With those values in place, enter the formula below in E2.

With product codes in A2:A6, product names in B2:B6, and the search code in D2, use =INDEX(B2:B6,MATCH(D2,A2:A6,0)). For D2 containing SKU-103, the result is Folder.

To use it, put the formula in the cell where you want the answer, then replace the example ranges and input cell with the locations in your own sheet. Keeping the lookup value in a separate cell is helpful because you can change that value without editing the formula. If you copy the formula down a column, consider whether your ranges should stay fixed; dollar signs, as in $A$2:$A$6, keep a range from shifting as the formula is copied.

Google Sheets normally displays a result in the formula cell, while the formula itself remains visible in the formula bar when that cell is selected. If your spreadsheet uses a locale that expects semicolons between function arguments, the separators may need to be adjusted. The examples here use the standard comma-separated form.

Look Left or Find a Value at a Row-and-Column Intersection

INDEX MATCH can return information from either side of the search column. Imagine employee names are in A2:A6 and employee IDs are in B2:B6, and D2 contains a name. A VLOOKUP layout would normally require the search column to be the leftmost column in its selected range, but INDEX MATCH can search the names and return an ID from the column to their left.

With employee names in A2:A6, IDs in B2:B6, and the name to find in D2, use =INDEX(B2:B6,MATCH(D2,A2:A6,0)). If D2 contains a name on the fourth row of the listed names, the formula returns the ID from B5. The formula uses the same basic structure as the product example; only the search and return ranges have changed.

You can also use two MATCH functions to find an intersection in a table. Suppose employee names are in A2:A4, quarter headings are in B1:D1, and the figures are in B2:D4. G2 contains Mina, H1 contains Q2, and Mina’s row has 12, 14, and 11 under Q1, Q2, and Q3. With that layout, use =INDEX(B2:D4,MATCH(G2,A2:A4,0),MATCH(H1,B1:D1,0)). The result is 14: the first MATCH identifies Mina’s row, and the second identifies the Q2 column.

This two-way approach is useful for schedules, price matrices, survey tables, and monthly reports where both the row and column label can change. Make sure the data area begins directly below the column headers and beside the row labels so each MATCH result points to the intended position in INDEX.

Exact Match, Approximate Match, and Sorting

For most everyday lookups, use 0 as the third argument of MATCH. It requests an exact match and does not require the search range to be sorted. This is especially important for text labels, account numbers, product codes, and other values that should not be treated as ranges.

MATCH also has approximate modes. A search type of 1 finds the largest value less than or equal to the search key and expects the range to be sorted in ascending order. A search type of -1 finds the smallest value greater than or equal to the key and expects descending order. If the data is not sorted as required, approximate results can be misleading. Omitting the third argument uses the default approximate behavior, so including 0 helps make an exact lookup explicit.

Exact does not necessarily mean case-sensitive. Standard MATCH searches are not case-sensitive, so names that differ only by capitalization may be treated as the same text. With exact-match mode, MATCH also supports wildcard characters in text searches: an asterisk can represent a sequence of characters, and a question mark can represent one character. If you need to search for a literal wildcard character, Google Sheets provides the tilde as an escape character. For IDs and text that might contain these symbols, be aware that wildcard matching can affect what is found.

Common Errors and How to Troubleshoot Them

The most common error is #N/A, which usually means MATCH did not find the requested value. Check for extra spaces, inconsistent punctuation, a number stored as text in one place but as a number in another, or a search key that simply is not present. Confirm that the formula is looking in the intended range and that the value in the input cell is what you expect.

A second frequent problem is mismatched ranges. If MATCH searches A2:A100 but INDEX returns from B2:B99, the positions may no longer refer to corresponding records. Use ranges with the same number of entries and keep their starting rows aligned. In a two-way lookup, also confirm that the INDEX data area has the same number of rows and columns as the label ranges used by the two MATCH functions.

If a formula returns a plausible but incorrect answer, inspect the source data and the match mode. Duplicate values are one possible cause: MATCH returns the position of the first matching entry it encounters, so repeated product codes or names do not automatically select the last or most recent record. Decide how duplicates should be handled before relying on a lookup for a report.

A #REF! error can indicate that an INDEX position does not fit inside the supplied range, or that a referenced range has been deleted or changed. Check that the row and column positions make sense for the size of the INDEX range. Also look for accidental missing parentheses or commas if Google Sheets reports a formula parse error. If your locale uses semicolon separators, changing the argument separators may resolve that particular issue.

INDEX MATCH Compared with Other Lookup Options

VLOOKUP is familiar and can be straightforward when the search field is the first column of a table and the return field is to its right. In Google Sheets, its optional sorted argument matters: for an exact lookup, set that argument to FALSE rather than relying on the default approximate behavior. INDEX MATCH takes a little more explanation, but it can return a value to the left of the search range and lets the search and return ranges be specified separately.

Google Sheets also supports XLOOKUP, which can make many lookups more readable because its arguments directly identify the lookup range and the return range. It is a reasonable option when a formula is maintained by people who prefer a single lookup function. INDEX MATCH remains useful for readers who already know the pattern, for two-way table lookups, and in spreadsheets where that structure is already in use.

There is no special fee for using INDEX or MATCH as functions in Google Sheets. Which method is best depends more on the layout, the needs of the people maintaining the sheet, and how clearly the formula communicates its purpose than on cost. For a small, simple table, any well-constructed exact lookup may be sufficient; for a changing matrix or a lookup to the left, INDEX MATCH offers a flexible structure.

Frequently Asked Questions

Does INDEX MATCH work in Google Sheets?
Yes. Both INDEX and MATCH are supported in Google Sheets, and nesting MATCH inside INDEX is a standard way to look up a value and return a corresponding result.

Do I need to sort data for an exact INDEX MATCH lookup?
No. Use 0 as MATCH’s search type for an exact match; that mode does not require the search range to be sorted.

Can INDEX MATCH look to the left?
Yes. Set MATCH to search the column containing the lookup value, then set INDEX to return from the aligned column on the left. The return range does not have to be to the right.

Why does my INDEX MATCH formula return #N/A?
Usually MATCH cannot find the search key. Check the input value, spaces, data types, punctuation, and the selected search range. Also confirm that the requested value is actually present.

Does MATCH return the last duplicate?
No. A standard MATCH returns the first matching position it encounters in the search range. If the lookup column contains duplicates, the formula will not automatically choose the latest or last record.

Is INDEX MATCH better than VLOOKUP in Google Sheets?
Not in every case. INDEX MATCH is more flexible about where the return column sits and supports two-way lookups, while VLOOKUP can be simpler for a rightward lookup in a basic table. Choose the structure that fits the data and is easiest to maintain.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.