Google Sheets VLOOKUP: Link Two Sheets Step by Step

To look up a value in a different Google Sheets file, combine VLOOKUP with IMPORTRANGE: allow access to the source file, then enter a formula such as =VLOOKUP(A2,IMPORTRANGE("SOURCE_URL","Products!A:B"),2,FALSE). Replace the placeholders with your lookup cell, source spreadsheet URL, and tab and range. This retrieves a matching value from the other file; it does not merge the spreadsheets or create a two-way link.

The instructions below are for Google Sheets in a web browser. If the data is on another tab in the same spreadsheet, you can use VLOOKUP alone. Excel does not have Google Sheets’ IMPORTRANGE function, so the cross-file formula here is not an Excel formula.

What You’ll Need

Access to both Google Sheets files. You need permission to view the source data; the source owner may need to grant it.
The URL of the spreadsheet containing the data, plus the exact name of its tab.
A shared value in both files, such as a product ID or email address. VLOOKUP uses this value to find the corresponding row.
A destination column where you can enter the formula and fill it down.

Step-by-Step

Identify the lookup key in each file. In the destination spreadsheet, find the column containing the value you want to search for. For example, the destination tab might have product IDs in column A, starting at A2. In the source file, identify the column with those same IDs and the column containing the result you want, such as product names or prices. The key column must be the first column of the range you give VLOOKUP. If IDs are in column C and prices in D, your source range should begin with C, not A.
Check the source data for consistency. Confirm that the lookup keys use the same format in both files. A number stored as text may not match a true number, and extra spaces can also stop an exact match. Check whether the source contains duplicate keys: VLOOKUP returns the first matching row it encounters, so duplicates may produce an unexpected result. If a key should identify only one record, make the source values unique before relying on the result.
Copy the source spreadsheet URL. Open the spreadsheet that holds the data and copy its address from the browser. You can use the full URL in the formula. You do not need to copy the source cell values into your destination file first. Keep the URL handy, and note the tab name exactly as it appears at the bottom of the source spreadsheet; spelling and spaces matter.
Enter the formula in the destination file. Suppose the destination has a product ID in A2, while the source tab is named Products and has IDs in column A and product names in column B. Click the destination cell where the name should appear, such as B2. With those exact columns and starting cell, the formula is:

=VLOOKUP(A2,IMPORTRANGE("SOURCE_URL","Products!A:B"),2,FALSE)

Replace SOURCE_URL, including the placeholder text, with the copied source spreadsheet URL. Keep the URL and range inside quotation marks. The range Products!A:B means columns A and B on the Products tab; the 2 tells VLOOKUP to return the second column of that range, and FALSE requests an exact match. If your source tab has a space in its name, write the range text as, for example, "Product Data!A:B".

Grant access to the imported range. The first time a destination spreadsheet connects to a source spreadsheet, the formula may show #REF! with a prompt to connect the sheets. Select the formula cell and click Allow access if the prompt appears. This permission is for the connection between those files. If you cannot grant access, ask the source owner to share the source spreadsheet with your Google account or to check the file’s sharing settings.
Confirm the returned value. After access is allowed, check that the result in B2 is the expected product name for the ID in A2. Test a second known ID as well. If the formula returns the wrong value, recheck the source range’s first column and the column number in the formula. If the result is #N/A, verify that the key exists and matches exactly; that error usually means the lookup did not find it.
Fill the formula down the destination column. Once the first result is correct, select its cell and drag the small square at the lower-right corner downward, or copy and paste the formula into the remaining rows. Because A2 is a relative reference, it changes to A3, A4, and so on. The source range is written as whole columns, so it continues to cover the same source columns as the formula fills down. Review a few filled results, especially at the bottom of the list.
Use the right source range if the layout differs. If the source IDs are in column C and the values to return are in column D, and the destination key is still in A2, use the range C:D and return column 2. With those assumed columns and the source tab named Products, the formula is:

=VLOOKUP(A2,IMPORTRANGE("SOURCE_URL","Products!C:D"),2,FALSE)

The number 2 means the second column within C:D, which is D. It does not mean column B of the spreadsheet. If the desired result is in a column to the left of the key, VLOOKUP cannot return it with that range; rearrange the source data or use another lookup approach.

Common Mistakes

Leaving out IMPORTRANGE for a different file. A range reference by itself can refer to a tab in the current spreadsheet, but not to another spreadsheet file. Use IMPORTRANGE to bring the external range into the VLOOKUP formula.
Putting the return column before the key. VLOOKUP searches only the leftmost column of its range. If the source range starts with the return values instead of the lookup keys, matches will fail or the formula will search the wrong data.
Using TRUE or omitting the match setting. VLOOKUP can perform approximate matching when the final argument is TRUE or omitted. For IDs, names, and other exact keys, use FALSE so the formula does not rely on sorted data or return a nearby match.
Counting the return column incorrectly. The column index is counted from the left edge of the specified range. For a range B:D, B is 1, C is 2, and D is 3. It is not the sheet’s absolute column number.
Assuming duplicate keys return every result. VLOOKUP returns one matching result, not all matching rows. It returns the first matching row in the range, which can be misleading if the source has repeated IDs.
Changing the source tab name or columns without updating the formula. A renamed tab or moved data can break the reference or point it at the wrong fields. Update the range text and return-column index if the source layout changes.

Tips

Use a stable ID as the key. Names can be misspelled or repeated; a unique customer, order, or product ID is usually a more dependable lookup value.
Start with a small test. Connect the files and verify a few known records before filling a formula down hundreds or thousands of rows. This makes a wrong range or mismatched key easier to spot.
Consider a bounded range for large source sheets. Instead of importing full columns, you can use a range such as Products!A1:B5000 if you know the data size. Adjust the range as the source grows, or new rows may not be included.
Keep source headers out of the lookup data when possible. A range starting on row 1 is fine when the lookup key will never equal the header, but starting on row 2 makes it clear that the search is over records rather than labels.
Use a same-file reference for tabs in one spreadsheet. If the source is another tab in the same file, you do not need IMPORTRANGE. With a lookup key in A2 and keys and results in columns A and B of a tab named Products, enter =VLOOKUP(A2,Products!A:B,2,FALSE). If a tab name contains spaces, enclose the tab name in single quotes in the range reference.
Know what the link does. IMPORTRANGE imports values from the source into a formula result; the destination does not edit the source record when you change a returned value. Allow for updates to take time, and avoid creating unnecessary chains of spreadsheets that import from one another.

Troubleshooting

The formula shows #REF!. Select the cell and look for an access prompt. Click Allow access if available. If there is no prompt, check that the URL identifies the correct spreadsheet, that your account can open the source, and that the range text names a real tab. A tab called Product Data must be referred to as Product Data, not Products.

The formula shows #N/A. The exact key may not exist in the source range, or its displayed value may differ from its underlying value. Check for spaces, punctuation, leading zeros, or text-versus-number differences. Confirm that the lookup key is in the first column of the imported range. Test a known key directly against the source to distinguish a formula problem from missing data.

The result is from the wrong record. Check for duplicate keys and confirm the formula ends with FALSE. Then verify that the range begins with the correct key column and that the index points to the intended return column. If the same key appears more than once, tidy or disambiguate the source data.

You see a formula parse error. Check the parentheses, commas, quotation marks, and spelling of the tab name. Make sure the spreadsheet URL and range string are each surrounded by straight double quotation marks. In some Google Sheets locales, formulas use semicolons instead of commas; if the comma version is rejected, use the argument separator shown by your spreadsheet’s locale settings.

The formula works for one row but not after filling down. Confirm you started with the intended lookup cell and copied the formula into the correct rows. If you edited the formula to use a limited source range, check that the range still includes the matching record. Also check that the destination key cells are not blank or formatted differently from the source keys.

Frequently Asked Questions

Can VLOOKUP link two separate Google Sheets files?
Yes. Use IMPORTRANGE as the VLOOKUP range argument, allow access the first time, and use a key column at the left edge of the imported range.

Do both files need to be open?
No. The formula can retrieve the source data without both files open in browser tabs. Your account still needs permission to access the source spreadsheet.

Can I link two tabs in the same Google Sheets file?
Yes. Use VLOOKUP with a reference to the other tab, such as Products!A:B, and skip IMPORTRANGE. Use single quotes around tab names that contain spaces.

Will changes in the source update the destination?
IMPORTRANGE can refresh imported values, so the lookup result can reflect source changes. It is not an instant guarantee; allow time for updates, particularly in large or interconnected spreadsheets.

Can I return more than one column with VLOOKUP?
A basic VLOOKUP returns one column for each formula. To retrieve several fields, use separate VLOOKUP formulas with the appropriate column index for each field, or choose a different Google Sheets approach if you need a multi-column result.

Does this formula work in Excel?
No. IMPORTRANGE is a Google Sheets function. Excel has different ways to refer to data in another workbook, so this exact formula is intended for Google Sheets in a web browser.

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.