How to Use VLOOKUP From Another Workbook in Google Sheets

Using VLOOKUP from another workbook in Google Sheets requires the use of the IMPORTRANGE function. The IMPORTRANGE function allows you to access data from another workbook in Google Sheets. Here's how you can use VLOOKUP with IMPORTRANGE:

1. Open Google Sheets and make sure you have access to both workbooks – the one with the data you want to look up and the one where you want to display the result.
2. In the workbook where you want to display the result, click on the cell where you want the VLOOKUP result to appear.
3. Type the following formula:

=VLOOKUP(search_key, IMPORTRANGE("spreadsheet_key", "sheet_name!range"), index, [is_sorted])

Here's a breakdown of the formula components:

• search_key: The value you want to search for in the first column of the data range in the other workbook.
• spreadsheet_key: The key of the other workbook. You can find it in the URL of the other workbook. It's the long string of letters and numbers between "/d/" and "/edit".
• sheet_name: The name of the sheet in the other workbook where the data is located.
• range: The range of cells in the other workbook where you want to perform the VLOOKUP.
• index: The column number in the range containing the return value.
• [is_sorted] (optional): A boolean value (TRUE or FALSE) indicating whether the first column of the range is sorted. If omitted, it defaults to TRUE.

Example

Let's assume you have two workbooks:

1. Workbook A: Contains a list of products and their prices in Sheet1 with the range A1:B10.
2. Workbook B: Where you want to display the price of a specific product.

Here's how you can use VLOOKUP with IMPORTRANGE in Workbook B to look up the price of a product in Workbook A:

1. Get the spreadsheet_key of Workbook A from its URL.
2. In Workbook B, click on the cell where you want to display the product price.
3. Type the following formula:

=VLOOKUP("Product Name", IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Sheet1!A1:B10"), 2, FALSE)

Replace "Product Name" with the name of the product you want to look up, and "1aBcDeFgHiJkLmNoPqRsTuVwXyZ" with the actual spreadsheet_key of Workbook A.

4. Press Enter. If this is the first time you're using IMPORTRANGE with Workbook A, you'll see a #REF! error with an "Allow access" button. Click on the "Allow access" button to grant permission to access the data in Workbook A.
5. The VLOOKUP result should now appear in the selected cell in Workbook B.