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.
A Sample of Practice Spreadsheet
Introduction to VLOOKUP Function in Google Sheets
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.
- 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.
- 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.
Follow the steps below to enter a formula in another sheet to VLOOKUP from this sheet.
- 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.
- 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.
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.
Follow the steps below to be able to do that.
- 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.
- The final formula will look as follows. Enter the formula and copy it to the cells below.
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.
Follow the steps below to be able to do that.
- 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.
- Finally, enter the formula and copy it to the cells below to see the following result.
- Here, the VLOOKUP function will consider the output of the IMPORTRANGE function as the range to look up in.
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.
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.