GuidesGoogle Sheets

How to use INDEX MATCH in Google Sheets

Learn how to combine INDEX and MATCH in Google Sheets to look up data in any direction, with exact-match syntax and troubleshooting tips.

Quick answer

To use INDEX MATCH in Google Sheets, combine =INDEX(range, MATCH(lookup_value, lookup_range, 0)) to find data in any column, unlike VLOOKUP which only searches left to right. INDEX returns a value from a specified range based on a row and column number, while MATCH finds the position of a lookup value within that range.

Steps at a glance

  1. Select a cell where you want the lookup result to appear.
  2. Type =INDEX( and select the range containing your return values.
  3. Add a comma, then start the MATCH function inside INDEX.
  4. Enter the lookup value, then select the range to search.
  5. Add 0 as the match type for an exact match, then close parentheses.
  6. Press Enter to run the formula and check the result.
  7. Copy the formula down or across to apply it to other rows.

Summary

INDEX MATCH combines two separate Google Sheets functions into one flexible lookup formula that can search for and return data in any direction. It solves the biggest limitation of VLOOKUP, letting you pull values from columns positioned anywhere relative to your lookup column.

Step-by-step guide

  1. Step 1

    Organize your lookup and return data

    Set up your spreadsheet so the column you want to search, like Product Name, and the column you want to return, like Price, are in clearly defined ranges, even if they aren't adjacent. Unlike VLOOKUP, INDEX MATCH doesn't require your lookup column to sit to the left of your return column, a difference explained further in the VLOOKUP guide if you want to compare the two approaches.

  2. Step 2

    Enter the INDEX function

    Click an empty cell and type =INDEX( followed by the range of cells that contains the values you want returned, such as B2:B20. This range should include only the column with your desired output, not the entire table.

  3. Step 3

    Nest the MATCH function inside INDEX

    After a comma, type MATCH( and enter the lookup value, either a direct cell reference like A2 or typed text in quotes. Follow this with a comma and the range where Sheets should search for that value, such as A2:A20.

  4. Step 4

    Set the match type to zero for an exact match

    Add a comma and type 0 as the third argument in MATCH to force an exact match rather than an approximate one. Close both the MATCH and INDEX parentheses so the full formula reads =INDEX(B2:B20,MATCH(A2,A2:A20,0)).

  5. Step 5

    Press Enter and verify the result

    Press Enter to calculate the formula. Google Sheets returns the value from your INDEX range that corresponds to the row where MATCH located your lookup value.

  6. Step 6

    Wrap the formula in IFERROR to handle missing matches

    If there's a chance your lookup value won't exist in the data, wrap the whole formula in an IFERROR function, like =IFERROR(INDEX(B2:B20,MATCH(A2,A2:A20,0)),"Not found"). This prevents #N/A errors from cluttering your sheet, and you can learn more in the IFERROR guide.

  7. Step 7

    Copy the formula to other rows

    Drag the fill handle in the bottom-right corner of the cell down or across to apply the formula to additional rows. Google Sheets adjusts relative references automatically, but lock your search ranges with absolute references, like $B$2:$B$20, if you're copying the formula across many rows.

Why this matters

You need this when your spreadsheet's lookup value sits to the right of the data you want to return, a situation VLOOKUP can't handle. Mastering INDEX MATCH lets you build flexible, reliable formulas for reports, inventories, or grade sheets without restructuring your columns.

Frequently asked questions

  • What's the difference between INDEX MATCH and VLOOKUP?

    VLOOKUP only searches for values in the leftmost column of a range and returns data from columns to the right. INDEX MATCH has no such restriction, letting you look up and return values in any column regardless of position.

  • Can INDEX MATCH search using more than one criteria?

    Yes. You can nest multiple conditions inside MATCH using array-style syntax, such as combining two columns with an ampersand, to match on more than one value at once.

  • Why does my INDEX MATCH formula return a blank result?

    A blank result usually means the lookup value exists but the corresponding cell in your return range is genuinely empty. Double-check the source data to confirm the cell actually contains a value.

Related guides

Create interactive demos like this one — free, no coding required.

Start for free →