How to VLOOKUP from Another Sheet in Google Sheets (2 Ways)

VLOOKUP is a vertical lookup that we use when we need to find a value row-wise within columns in a table. For example, we can use VLOOKUP to look up a name and find the corresponding Email ID.  The VLOOKUP function allows us to lookup for values in the same sheet, another sheet, or from a different spreadsheet in Google Sheets. Follow the article to see how to do that.


Introduction to VLOOKUP Function in Google Sheets

Objective:
VLOOKUP is a method to find a value related to the lookup value. For example, you can VLOOKUP a product name to find the price of that product.

Syntax:

VLOOKUP(search_key, range, index, [is_sorted])

Inputs:

  • search_key: This is what we are looking for, It can be a number or text. Suppose we are considering cell A2 as a search_key. Then VLOOKUP will search for the value of that cell.
  • range: This is the parameter that says where to search the value. If we need to find the value in column B, then this will be the range.
  • index: It is the column number within the range from which we need to get the value related to the search_key. Suppose the range is B:D and from this total range we need to get the value from column D. In that case, the index will be 3 (column B = 1, column C = 2).
  • [is_sorted]: FALSE or 0 means looking for an Exact Match. TRUE or 1 means looking for an Approximate Match. You must sort the range in ascending order for TRUE.

Return Value:

  • Returns the first matched value from the lookup.

2 Ways to VLOOKUP from Another Sheet in Google Sheets

We will follow two ways to VLOOKUP while the data set is in the same spreadsheet. In the first method, we will VLOOKUP from a single sheet. In the second method, we will do that from multiple sheets.


1. VLOOKUP from Single Sheet

Consider the following dataset containing Student Names and Email IDs. This sheet is named Dataset.

VLOOKUP from Single SheetFollow the steps below to enter a formula in another sheet to VLOOKUP from this sheet.

📌 Steps:

  • First, select cell D5 in another sheet, and insert the VLOOKUP function. If you enter the “=” sign followed by VLOOKUP, you will see the function listed below the cell. Select it or press Tab to insert the function.

  • Then select cell C5 as we want to look up the value of that cell. Put a comma after that.

  • After that go to the Dataset sheet and select the range B5:C14. Then, press F4 to make it an absolute reference. Put a comma after that.

VLOOKUP from Single Sheet

  • Then, type 2 for the index as we need VLOOKUP to return the Email ID from the second column in the range. Put a comma after that.
  • Next, type 0 or FALSE as the range is not sorted and we need to look up an Exact Match. The final formula will look as follows. Now drag the Fill Handle icon in the lower right corner of the cell to copy the formula below.
=VLOOKUP(C5,Dataset!$B$5:$C$14,2,0)

Read More: How to Use VLOOKUP Function for Exact Match in Google Sheets


Similar Readings


2. VLOOKUP from Multiple Sheets

Now consider the following dataset in another sheet named Dataset2. We will VLOOKUP from this sheet along with the first sheet named Dataset.

VLOOKUP from Multiple SheetsFollow the steps below to be able to do that.

📌 Steps:

  • First, select cell D5 and insert the VLOOKUP function as earlier. Then, select cell C5 followed by a comma. Next, select the ranges in those datasets separated by commas or semicolons and enclose them with curly brackets. Put a comma after that.

How to VLOOKUP from Another Sheet in Google Sheets

  • The final formula will look as follows. Enter the formula and copy it to the cells below.
=VLOOKUP(C5,{Dataset!$B$5:$C$14,Dataset2!$C$5:$D$14},2,0)

Read More: How to Use the VLOOKUP Function in Google Sheets


How to VLOOKUP from Another Sheet in Different Google Spreadsheets

Suppose you have a new spreadsheet with the following dataset. You need to VLOOKUP from the NewWB sheet in that spreadsheet.

VLOOKUP from Another Sheet in Different Google Spreadsheets

Follow the steps below to be able to do that.

📌 Steps:

  • First, insert the VLOOKUP function and select cell C5, as we want to look up the value from that cell.
  • Then insert the IMPORTRANGE function inside curly brackets. Next copy the link of the other spreadsheet from the address bar and use it as the first input for the IMPORTRANGE function.
  • Now enclose the link with double quotes and put a comma after that. Then, type the sheet name of the dataset and enclose it with single quotes. Next, put an exclamation mark (!) followed by the data range. After that, enclose the entire cell reference with double quotes.

  • Now complete the formula by typing 2 as the index. As a result, the formula will look as follows.
=VLOOKUP(C5,{IMPORTRANGE("https://docs.google.com/spreadsheets/d/1X3c4hnMl2eXF0vPGWq9DWXX-xU0WviaAXMxVwh2mWTc/edit#gid=0","'NewWB'!B5:C14")},2,0)
  • Finally, enter the formula and copy it to the cells below to see the following result.

VLOOKUP from Another Sheet in Different Google Spreadsheets

  • Here, the VLOOKUP function will consider the output of the IMPORTRANGE function as the range to look up in.

Read More: VLOOKUP with IMPORTRANGE Function in Google Sheets


Things to Remember

  • Don’t forget to use absolute references while entering the VLOOKUP formula.
  • Always select the correct data range as VLOOKUP does not allow you to have any blank data while selecting the range.
  • While executing VLOOKUP from another spreadsheet, make sure you have permission or access to that sheet.

Conclusion

We have shown you 3 different ways to VLOOKUP from another sheet in Google Sheets. Now you can VLOOKUP from single or multiple sheets, or even from a different spreadsheet. If you have any queries or suggestions, please let us know in the comment section. You may also visit our OfficeWheel blog to explore more Google Sheets-related articles.


Related Articles

Afrina

Afrina

This is Afrina Nafisa. Currently working as a Technical Writer at OFFICEWHEEL.COM. I have done my graduation from Ahsanullah University of Science and Technology. As I am eager to learn more and more currently my motive is to make myself better every day with my work so that I can make better content for all the readers.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo