INDEX MATCH in Google Sheets: Search Another Sheet

Yes. In Google Sheets, INDEX MATCH can look up a value on another tab in the same spreadsheet and return a related value from that tab. With the lookup value in A2, product IDs in Products!A2:A100, and product names in Products!B2:B100, use =INDEX(Products!B2:B100,MATCH(A2,Products!A2:A100,0)). The final 0 makes MATCH search for an exact match.

How INDEX MATCH works across sheets

INDEX and MATCH divide a lookup into two jobs. MATCH searches a range for a value and reports its position; INDEX uses that position to return an item from a corresponding range. The ranges can be on a different tab from the formula, so the lookup can use a compact table kept elsewhere in the spreadsheet.

In the example above, MATCH finds the position of the value in A2 within the product ID column. If it finds the ID in the fourth cell of that range, it returns 4. INDEX then returns the fourth item in the product-name range. Because both ranges begin on row 2 and have the same height, their positions line up.

The formula does not move or copy data between tabs. It reads the source range and displays the corresponding result in the cell containing the formula. If the source value changes, the displayed result updates as the sheet recalculates.

Set up a basic lookup step by step

First, identify the column containing the unique value you will search for and the column containing the result you want. For example, a Products tab might store product IDs in column A and product names in column B, while a separate Order Form tab contains an ID to look up in A2.

Go to the cell where you want the result to appear, such as B2 on Order Form.
Enter an INDEX formula around the result range on the Products tab.
Put MATCH inside it, using the lookup cell and the ID range on Products.
Use 0 as MATCH’s third argument to request an exact match, then press Enter.

Assuming the lookup ID is in Order Form!A2, product IDs are in Products!A2:A100, and product names are in Products!B2:B100, enter this in Order Form!B2: =INDEX(Products!B2:B100,MATCH(A2,Products!A2:A100,0)). For example, if A2 contains P-104 and P-104 is the seventh ID in the listed range, the formula returns the name in the seventh position of Products!B2:B100.

Use matching range lengths: a 99-row ID range should correspond to a 99-row result range. You can use open-ended references such as A2:A when new records are regularly added, but bounded ranges can make a formula easier to inspect and may avoid needlessly processing large columns in a busy spreadsheet.

Reference tab names correctly

A tab name without spaces or certain punctuation can be written directly before an exclamation mark, as in Products!A2:A100. If the tab name contains spaces or special characters, enclose the name in single quotation marks. For example, with the lookup ID in A2, IDs in Product List!A2:A100, and names in Product List!B2:B100, use =INDEX('Product List'!B2:B100,MATCH(A2,'Product List'!A2:A100,0)).

The single quotation marks belong around the tab name, not the range. If you rename a tab through Google Sheets, references are generally adjusted to the new name, but formulas copied from other sources or edited by hand can still contain invalid references. The exclamation mark separates the tab name from its cell or range reference.

A reference such as Products!A2 means a tab named Products in the same spreadsheet file. It does not mean a separate Google Sheets file. That distinction matters when your source data lives in another spreadsheet, as described below.

Choose exact match and understand duplicates

For a conventional ID, name, SKU, or code lookup, use 0 for MATCH’s third argument. It asks for an exact match, rather than an approximate position based on sorted data. Omitting that argument can result in approximate-match behavior, which is usually not what a reader expects when looking up an identifier.

If a lookup value occurs more than once, MATCH returns the position of the first matching entry it encounters in the specified range. INDEX MATCH therefore returns the result from the first matching row, not every matching row and not necessarily the most recent one. If duplicates are possible, decide whether to remove them, make the key unique, or use another approach that returns multiple records.

Exact matching in Google Sheets is generally not case-sensitive: codes such as abc and ABC are treated as equivalent by this MATCH pattern. Also, MATCH with 0 supports wildcard characters in text searches. An asterisk can stand for a sequence of characters, and a question mark can stand for one character. If a lookup value must contain a literal wildcard character, escape it with a tilde. For strictly case-sensitive matching or more complex text rules, use a different formula design.

Handle missing values and common errors

If MATCH cannot find the lookup value, it returns #N/A, and the surrounding INDEX formula displays that error too. First check that the value really exists and that the lookup and source values have compatible formats. A number stored as text, an extra space, or a mistyped character can make two values that look alike fail to match.

With the lookup ID in A2, source IDs in Products!A2:A100, and return values in Products!B2:B100, wrap the lookup in IFNA to show a clearer message when there is no match: =IFNA(INDEX(Products!B2:B100,MATCH(A2,Products!A2:A100,0)),"No match"). IFNA handles the not-found error while allowing other kinds of formula errors to remain visible, which can help with troubleshooting.

Other common problems include a misspelled tab name, a missing quotation mark around a tab name with spaces, and ranges that start on different rows or have different sizes. If the formula returns a valid but unexpected result, inspect the first matching row and check for duplicate keys. If an error appears after a tab is renamed or a range is reorganized, confirm that the formula still points to the intended columns.

Look up a value using more than one condition

Sometimes one column does not uniquely identify a record. For instance, an order number may repeat in different regions, so the lookup needs to match both order number and region. Google Sheets can test both conditions in MATCH by multiplying the two TRUE/FALSE comparisons; matching rows produce 1 for both conditions and therefore a combined value of 1.

Assume the lookup order number is in A2 and region is in B2 on the formula tab. On Orders, order numbers are in A2:A100, regions in B2:B100, and amounts to return in C2:C100. Use =INDEX(Orders!C2:C100,MATCH(1,(Orders!A2:A100=A2)*(Orders!B2:B100=B2),0)). The result is the amount from the first row where both the order number and region match. Make sure all three source ranges cover the same rows.

This pattern is useful for combining fields into a logical key without adding a helper column. It still returns only the first row meeting all conditions. If you need every qualifying row, a filter-based formula is a better fit.

Return a value at the intersection of a row and column

INDEX MATCH can also perform a two-way lookup. One MATCH finds a row using a label, while a second MATCH finds a column using a header. INDEX then returns the cell at their intersection, which is handy for tables organized by item and month, or by employee and metric.

Suppose A2 contains an item name and B2 contains a month. On Inventory, item names are in A2:A20, month headers are in B1:F1, and the data grid is B2:F20. Use =INDEX(Inventory!B2:F20,MATCH(A2,Inventory!A2:A20,0),MATCH(B2,Inventory!B1:F1,0)). If the item is on the third data row and the month is the second header, the formula returns the value in the third row and second column of the grid.

For this setup, the row-label range must align with the data grid’s rows, and the header range must align with its columns. A missing item or month produces #N/A, so the same checks for spelling, formatting, and exact matches apply.

Same spreadsheet tab or separate spreadsheet file?

If the source is another tab in the same Google Sheets file, use a normal tab reference such as Products!A2:A100. If the source is in an entirely separate spreadsheet file, a tab reference alone will not reach it. Google Sheets provides IMPORTRANGE to bring a range from another file into the current one.

For example, you can first import the source table into a helper tab. In cell A1 of that helper tab, use =IMPORTRANGE("spreadsheet_url","Products!A:B"), replacing spreadsheet_url with the source spreadsheet’s URL or its spreadsheet ID. On the first connection, Sheets may ask you to grant access. Once authorized, the imported columns can be referenced by INDEX MATCH like any other local tab. Using a helper tab keeps the external import separate from the lookup logic and can make the formula easier to maintain.

IMPORTRANGE depends on access permissions and the source file being available. If you only need a one-time snapshot, copying values may be simpler; if the data must stay current, an imported range can be more appropriate. Avoid creating many separate imports of the same large data set when one helper import will do.

INDEX MATCH compared with other lookup options

INDEX MATCH is useful when the lookup column is not immediately to the left of the return column, or when you want the two ranges specified independently. Unlike a fixed column number in some lookup formulas, the return range can be selected directly. That flexibility can be helpful when a table’s columns are rearranged, although the formula still needs to reference the intended ranges.

VLOOKUP is often shorter for a simple table when the lookup column is its leftmost column and the return column is to its right. FILTER is more suitable when you want all rows meeting a condition rather than just the first match. In Google Sheets, XLOOKUP is another option for a straightforward lookup where you specify lookup and result ranges separately. Choose based on whether you need one result, multiple results, or a formula that is easiest for the spreadsheet’s future maintainers to understand.

Google Sheets and Excel both support INDEX and MATCH, but they are separate programs and some features, menus, and behaviors differ. In particular, a Google Sheets tab reference is not an Excel external-workbook reference. If you move the formula into Excel and intend to read another workbook, you may need to adjust the reference to include that workbook’s name.

Frequently Asked Questions

Can INDEX MATCH search a different tab in Google Sheets?
Yes. Add the tab name and an exclamation mark before each source range, such as Products!A2:A100. Use single quotation marks around a tab name that contains spaces.

Why does my formula return #N/A?
MATCH has not found the lookup value in the specified range. Check the tab name, range, spelling, extra spaces, and whether one value is stored as text while the other is a number. Confirm that the formula uses 0 for an exact match.

Does INDEX MATCH return every duplicate match?
No. This pattern returns the result for the first matching position. Use a filtering approach if you need a list of all rows that meet the condition.

Can I look up data in another Google Sheets file?
Yes, but a regular tab reference only accesses tabs in the current file. Use IMPORTRANGE to connect to an external spreadsheet, grant access when prompted, and then look up the imported data.

Should I use INDEX MATCH or VLOOKUP?
Use whichever fits the table and is clearest to maintain. VLOOKUP can be concise when the key is in the leftmost column; INDEX MATCH allows separate lookup and result ranges. For multiple matching rows, consider FILTER instead.

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.