VLOOKUP Between Two Google Sheets: A Practical Guide

To look up a value in a different Google Sheets file, combine VLOOKUP with IMPORTRANGE: use =VLOOKUP(E2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/your-spreadsheet-id/edit","Products!A:C"),3,FALSE), replacing the URL, tab name, and cell references with yours. If the data is on another tab in the same spreadsheet, use VLOOKUP with that tab’s range; you do not need IMPORTRANGE.

What VLOOKUP between sheets does

A lookup connects a value in one table to related information in another. For example, an order sheet might contain product codes, while a separate product catalog contains those codes alongside product names and prices. A lookup finds the matching code and returns a value from the catalog.

Google Sheets has two different cases that people may call “between two sheets.” The source and destination may be separate tabs inside one spreadsheet file, or they may be separate spreadsheet files. The first case needs a cross-tab reference. The second needs an import connection as well as the lookup. That distinction determines which formula to use.

In either case, VLOOKUP searches down the leftmost column of the range you give it. It returns a value from another column in the same row. This left-to-right structure is the main limitation to keep in mind when arranging the source data.

Look up data on another tab in the same file

Suppose the current tab has a product code in E2, and a tab named Products has product codes in column A, names in B, and prices in C. With product codes in Products!A:A and prices in Products!C:C, enter this in the destination cell:

With the lookup code in E2 and the source table in Products!A:C, this formula returns the price from the third column of that range:

=VLOOKUP(E2,Products!A:C,3,FALSE)

If the tab name contains spaces or punctuation, put single quotes around it. With the same lookup code in E2 and the source table on a tab named Product List, use:

=VLOOKUP(E2,'Product List'!A:C,3,FALSE)

The formula can go in an ordinary cell on the destination tab. Copy it down to look up other rows; the relative reference E2 will change to E3, E4, and so on. If you want the source range to stay fixed while copying, use absolute references. With lookup values in E2:E100 and the source table on Products, use:

=VLOOKUP(E2,Products!$A$2:$C$500,3,FALSE)

This is still one spreadsheet file, so no permission prompt for importing another file is involved. Make sure the tab name and range are spelled correctly, including spaces.

Connect separate spreadsheet files with IMPORTRANGE

For two different Google Sheets files, IMPORTRANGE makes a range from the source file available to a formula in the destination file. The first argument is the source spreadsheet URL or ID; the second is the range as text, including the source tab name. VLOOKUP then searches the imported range.

For example, assume E2 contains a product code in the destination file. The source file’s Products tab has codes in column A, names in B, and prices in C. With the source data in Products!A:C, this returns the price in column three:

=VLOOKUP(E2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/your-spreadsheet-id/edit","Products!A:C"),3,FALSE)

Replace your-spreadsheet-id with the ID in the source file’s URL, or paste its full URL. Replace Products with the exact source tab name. If the tab name has spaces, write it inside single quotes as part of the range text, such as "'Product List'!A:C". The quotation marks around the range argument are required because IMPORTRANGE expects a text string there.

The first time the destination file connects to a source file, the formula may show #REF! and an access prompt. Select the cell and click Allow access. The connection needs authorization from someone who can access the source file. After permission is granted, the result should calculate; large imports can take a little time to load.

Build the formula step by step

Identify a unique or dependable key shared by both tables, such as a product ID, employee number, or email address. Prefer IDs to names when names can repeat or be entered differently.
Check the source layout. The key must be in the first column of the range supplied to VLOOKUP, and the value you want returned must be in a column to its right.
Choose the right connection. Refer to the other tab directly for data in the same file; use IMPORTRANGE for a separate file.
Set the column index by counting from the left edge of the lookup range, not by counting worksheet columns. If the range is A:C, A is 1, B is 2, and C is 3.
Use FALSE for an exact match in most ordinary lookups. This avoids approximate-match behavior, which can return an unexpected result when keys are not sorted.
Test one known key before filling the formula down. Compare the returned value with the source row to verify the range and column index.

Suppose the source range begins at B rather than A: codes are in B, names in C, and prices in D. With E2 as the lookup cell and B:D as the source range, the price is still in column three of the selected range:

=VLOOKUP(E2,Products!B:D,3,FALSE)

Counting from the range’s first column is a common point of confusion. The index is not the worksheet’s column number; D is the fourth worksheet column but the third column in B:D.

Exact matches, duplicates, and data quality

The final argument FALSE tells VLOOKUP to look for an exact match. Use it for codes, names, IDs, and most categorical values. If the source does not contain the requested key, the function returns #N/A. Leaving out the final argument or setting it to TRUE requests approximate matching, which relies on an appropriately sorted first column and is not the safe default for a typical list.

If the key appears more than once, VLOOKUP returns the first matching row it encounters in the selected range. It does not warn that duplicates exist or combine their results. If duplicates matter, clean or investigate the source table, or choose a method that returns multiple matching rows.

Visually identical values can also fail to match. One file may store an ID as a number and the other as text; a cell may contain leading or trailing spaces; or a date may be stored differently. Check the underlying values and formats, and standardize the key columns. Avoid casually converting IDs that have leading zeros, because treating them as numbers can remove those zeros.

Common errors and how to diagnose them

#REF! with an access message: authorize the IMPORTRANGE connection using Allow access. If there is no prompt, confirm that the account opening the destination can access the source file.
#REF! or an import range error: check the source URL, tab name, and range text. A misspelled tab name or an invalid range string can prevent the import.
#N/A: confirm that the lookup key exists in the first column of the selected range and that the formula uses the intended exact-match option. Check for extra spaces and text-versus-number mismatches.
#VALUE!: check that the column index is a positive number and does not exceed the number of columns in the selected range.
The wrong value appears: confirm that the index counts from the range’s left edge, that the first column contains the intended key, and that a duplicate key is not appearing earlier than the desired row.
The sheet feels slow: importing a very large range or repeating separate imports in many formulas can add work. Import only the columns and rows needed, and avoid recalculating the same large connection unnecessarily.

For a cleaner display when a key is absent, you can wrap the lookup in IFNA. With E2 as the lookup value and Products!A:C as the source table, this shows “Not found” instead of #N/A:

=IFNA(VLOOKUP(E2,Products!A:C,3,FALSE),"Not found")

This handles the missing-match error, not every possible problem. A misspelled tab or an invalid column index should still be corrected rather than hidden behind a generic message.

Performance, access, and sharing considerations

IMPORTRANGE brings source data into a destination formula; it does not copy the source spreadsheet’s formatting or make a static snapshot. The destination can update as the source changes, though refresh and recalculation may take time, especially with large ranges or many dependent formulas. For a modest lookup table, importing only the needed columns is usually easier to maintain than importing an entire sheet.

Consider who can see the destination file. Once a connection is authorized, editors of the destination spreadsheet may be able to use IMPORTRANGE to retrieve other ranges from the connected source, not only the range currently displayed in your formula. Do not connect sensitive source data to a destination file unless its sharing permissions are appropriate. A lookup is not a way to bypass the source file’s access controls.

If you need a fixed record for an audit or handoff, a live formula may not be the right final format. You can copy the displayed results and use Paste special > Values only to replace formulas with their current values. That removes the live link, so later changes to the source will not update those pasted values.

When to use a different lookup method

VLOOKUP is a good fit when the search key is the leftmost column of a compact table and the return column is to its right. If the return field is to the left of the key, VLOOKUP cannot return it from that range. You could rearrange the source columns, or use another formula such as INDEX with MATCH, or Google Sheets’ XLOOKUP where available in your account.

For a result that should include every matching row rather than just the first, consider FILTER. For a multi-step summary or a more involved data query, QUERY may suit the task better. These alternatives have different behavior and are not simply interchangeable: choose based on whether you need one match, multiple rows, a flexible search direction, or a grouped result.

Another option is to import the required source table once into a helper tab in the destination file, then run ordinary VLOOKUP formulas against that local tab. This can make a workbook easier to inspect and can reduce repeated IMPORTRANGE calls. The trade-off is an extra tab and a larger visible footprint for the imported data; protect and share the destination accordingly.

Frequently Asked Questions

Can VLOOKUP search a different tab in Google Sheets?
Yes. Reference the tab and range in the second argument, such as Products!A:C. Put single quotes around tab names that contain spaces, for example 'Product List'!A:C.

Do I need IMPORTRANGE for two tabs in the same spreadsheet?
No. IMPORTRANGE is for a range in another spreadsheet file. A direct tab reference is enough when both tabs belong to the same file.

Why does my IMPORTRANGE VLOOKUP show #REF!?
Often the destination has not yet been allowed to connect to the source. Click the formula cell and choose Allow access when prompted. Also check that the URL, source tab, and range are valid.

Can VLOOKUP return a value from a column to the left?
Not with its standard lookup range: VLOOKUP searches the range’s first column and returns from a column to its right. Rearrange the source range or use a different lookup approach, such as INDEX and MATCH or XLOOKUP.

How do I return all matching rows instead of one?
VLOOKUP returns one match, specifically the first matching row in the range. In Google Sheets, FILTER is an option when you want a set of rows that meet a condition.

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.