Google Sheets INDEX MATCH Formula: How to Use It Right

With item names in A2:A6, prices in B2:B6, and the item to find in E2, enter =INDEX(B2:B6,MATCH(E2,A2:A6,0)) to return its price. For example, if E2 contains Notebook and Notebook is in A3 with a price of 3.25 in B3, the formula returns 3.25.

What INDEX MATCH Does

INDEX and MATCH are two Google Sheets functions that work together to look up a value. MATCH finds the position of a search term in a range; INDEX uses that position to return a value from another range. In the example above, MATCH finds Notebook’s position in A2:A6, and INDEX returns the value at that same position in B2:B6.

This approach is useful when a table has one column for lookup keys and another for the information you want back. Those keys might be product names, employee IDs, invoice numbers, or dates. The formula can return a value from either side of the lookup column, unlike the traditional layout expected by VLOOKUP.

INDEX(reference,row, column) returns the value at a specified position in a range. For a single-column range, the row position is usually the one you need.
MATCH(search_key,range,search_type) returns the position of a value within a range. Use 0 for an exact match.

The functions return a position and a value, respectively; MATCH does not return the matching cell’s contents by itself. Keeping those jobs separate makes the formula adaptable to different table layouts.

Build a Basic Exact-Match Lookup

Suppose A2:A6 contains Pen, Notebook, Folder, Marker, and Tape, while B2:B6 contains their prices: 1.50, 3.25, 2.00, 1.75, and 2.50. Enter a product name in E2, then put the lookup formula in F2.

With product names in A2:A6, prices in B2:B6, and the search term in E2, use =INDEX(B2:B6,MATCH(E2,A2:A6,0)). If E2 is Notebook, MATCH returns 2 because Notebook is the second item in A2:A6, and INDEX returns the second value in B2:B6: 3.25.

The final 0 is important: it tells MATCH to search for an exact match rather than rely on sorted values. In Google Sheets, text matching with MATCH is generally not case-sensitive, so Notebook and NOTEBOOK can match the same entry. The formula looks for the value in E2 as it is written; it does not automatically remove extra spaces or correct spelling differences.

To reuse the formula down a results column, lock the source ranges while leaving the lookup cell relative. With names in A2:A6, prices in B2:B6, and each lookup value in E2 and below, enter this in F2 and fill down: =INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0)). The dollar signs keep the lookup and return ranges fixed while E2 changes to E3, E4, and so on.

Use INDEX MATCH for a Two-Way Lookup

A two-way lookup finds the correct row and column, such as a product’s sales for a selected month. INDEX can take both a row number and a column number, and each MATCH can find one of them.

Assume product names are in A2:A4, month headings are in B1:D1, and the corresponding sales figures are in B2:D4. Put a product name in G2 and a month heading in G3. If the row for Notebook contains 18, 24, and 21 under Jan, Feb, and Mar, the result for Notebook and Mar should be 21.

With product names in A2:A4, month headings in B1:D1, data in B2:D4, the selected product in G2, and month in G3, enter =INDEX(B2:D4,MATCH(G2,A2:A4,0),MATCH(G3,B1:D1,0)). The first MATCH identifies the product row; the second identifies the month column. INDEX uses both positions to return the value at their intersection.

For this to work reliably, the row lookup range must line up with the rows of the data range, and the column lookup range must line up with its columns. If the data range starts one row lower than the product names, the returned position may point to the wrong record.

Look Up a Record Using Multiple Conditions

Sometimes one field is not unique. A customer may have several orders, or the same employee name may appear in more than one department. You can match a combination of conditions by multiplying comparison results: TRUE values are treated as 1, and FALSE values as 0. Only a row satisfying every condition produces 1.

Assume customer names are in A2:A100, regions in B2:B100, and order IDs to return in C2:C100. Put the desired customer in H2 and region in H3. Use =INDEX(C2:C100,MATCH(1,(A2:A100=H2)*(B2:B100=H3),0)). It returns the order ID for the first row where both the customer and region match.

This formula returns the first matching row, not every matching order. If you need all records that meet conditions, Google Sheets’ FILTER function is a more suitable option. Also make sure all compared ranges have the same height; comparing different-sized ranges can cause errors or unexpected results.

Choose Exact or Approximate Matching

For most everyday lookups, use MATCH(search_key,range,0). Exact matching is appropriate for names, codes, IDs, and other values that should be present as entered. If there is no match, MATCH returns #N/A, which INDEX passes through.

MATCH also supports approximate search modes, but these depend on sorted data. With a search type of 1, MATCH expects the range to be sorted in ascending order and finds the largest value less than or equal to the search key. With -1, it expects descending order and finds the smallest value greater than or equal to the search key. These modes can be useful for ranges of tax brackets, rating bands, or commission thresholds, but an unsorted range can produce a misleading answer.

For a threshold table with lower limits in A2:A5 sorted from smallest to largest and rates in B2:B5, and a value to classify in E2, use =INDEX(B2:B5,MATCH(E2,A2:A5,1)). For example, if the limits are 0, 100, 500, and 1000 and E2 is 650, MATCH returns the position of 500, so INDEX returns the rate beside 500. Use this only when the threshold arrangement and intended boundary rules fit the data.

Handle Missing Values and Common Errors

A #N/A result usually means the lookup value was not found in the search range. Check spelling, spaces, number-versus-text differences, and whether the value is actually included in the selected range. For example, a cell containing the text “123” may not match a cell containing the numeric value 123 in every situation.

With product names in A2:A6, prices in B2:B6, and the search term in E2, wrap the lookup in IFNA to display a helpful message: =IFNA(INDEX(B2:B6,MATCH(E2,A2:A6,0)),"Not found"). If there is no exact match, the formula displays Not found rather than #N/A. IFNA handles #N/A specifically, so other errors remain visible instead of being hidden.

#REF! can indicate that a reference is invalid, such as an INDEX position outside the selected range. Check that both lookup ranges and the return range cover the intended records.
Wrong result: duplicate lookup keys return the first matching position. Make the key unique, add another condition, or use a function that returns multiple results if duplicates are expected.
Formula parse error: check the parentheses and commas. Spreadsheet locale settings can affect whether formulas use commas or semicolons as argument separators.
Extra spaces: clean inconsistent source text or use a helper column with TRIM when appropriate. A visually identical value can contain a leading or trailing space.

INDEX MATCH Compared with VLOOKUP and XLOOKUP

VLOOKUP searches down the first column of a selected table and returns a value from a column to its right. INDEX MATCH separates the search range from the return range, so the return column can be to the left or right of the lookup column. It also avoids relying on a hard-coded column number when the lookup and return ranges are specified directly.

XLOOKUP is another Google Sheets lookup option and can express many lookups more directly, including exact-match searches. INDEX MATCH remains useful when you encounter existing spreadsheets that use it, want to understand a widely shared formula pattern, or need the two-way structure shown above. The best choice depends on the task and the formulas your collaborators are comfortable maintaining.

Neither approach makes duplicate keys unique, and neither can return the intended record if the key or ranges are wrong. A clear layout, consistent source data, and an exact-match setting where appropriate matter more than choosing a particular lookup function.

Make the Formula Easier to Maintain

Use ranges that cover the records you actually need. Whole-column references can be convenient, but bounded ranges make the formula’s scope easier to inspect. If the dataset grows often, choose a range with room for new records or use a clearly structured data range so you remember to extend it.

Keep lookup and return ranges aligned: each item in the lookup range should correspond to the item on the same row in the return range. For a two-way lookup, check both dimensions. Descriptive headers help, and storing a lookup term in a separate input cell is usually easier to update than embedding the term in a formula.

Before relying on a result, test a value you know should match and one you know should not. Verify that duplicate keys behave as intended, that the returned value comes from the right column, and that the formula handles blanks appropriately for your sheet. A lookup can calculate correctly while still answering the wrong question if its ranges were selected incorrectly.

Frequently Asked Questions

What does the 0 mean in INDEX MATCH?
In the MATCH function, 0 requests an exact match. It is the usual choice for IDs, product names, and other values that must match the lookup key rather than fall within a sorted range.

Can INDEX MATCH look to the left?
Yes. MATCH searches one range, while INDEX returns a value from a separate range. The return range can be positioned to the left or right of the lookup range, as long as the corresponding entries line up.

Why does my INDEX MATCH formula return #N/A?
Usually, MATCH cannot find the search key exactly. Check that the key exists, the ranges include the relevant row, and the compared values have consistent spelling, spacing, and data types.

What happens when there are duplicate matches?
MATCH returns the position of the first matching value it encounters. If you need a particular record among duplicates, add another condition or use a method that returns all matches.

Can INDEX MATCH return a value based on both a row and a column?
Yes. Use one MATCH to find the row and another to find the column, then provide both positions to INDEX. This is commonly used to retrieve a value at the intersection of a record and a heading.

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.