Syntax
VLOOKUP(search_key,sheet_name!cell_range,index,[is_sorted])search_key represents the value you want to look up.sheet_name!cell_range is the range of cells within which the Vlookup function should search. index is the column number within the range to search within. [is_sorted]optional; can be True or False (False looks for an exact match).
Sample Usage
=VLOOKUP(10001,salaries!$A$3:$D$15,2,FALSE)
//This will retrieve the salary associated with employee code 10001 from the salaries sheet, within the second column of range A3:D15.
Sample Google Sheets template with formula here
The VLOOKUP function in Google Sheets is quite useful for looking up data in a column from the same sheet, especially if the worksheet contains a large amount of data. Better still, VLOOKUP from another sheet in Google Sheets can be employed to lookup for values from another sheet within the same workbook or from another workbook. This is particularly handy if you have many spreadsheets with different information, and you want to look up values from different sheets without switching the tab or opening the other spreadsheets.
In this tutorial, we will learn how to the VLOOKUP function to fetch values from another sheet in Google Sheets.
Formula for VLOOKUP from another sheet in Google Sheets
The generic VLOOKUP formula in Google Sheets is as given below.
=VLOOKUP(search_key, range, index, [is_sorted])VLOOKUP from another sheet in Google Sheets formula:
=VLOOKUP(search_key,sheet_name!cell_range,index,[is_sorted])VLOOKUP from another sheet in Google Sheets in the same spreadsheet file
When to use VLOOKUP from another sheet in Google Sheets
It is a good practice to keep a spreadsheet size small keeping separate data in separate spreadsheets. This not only keeps the table neat and tidy, and light making it quicker to load but also makes it easier to read and work with it.
But this requires us to switch tabs between spreadsheets frequently if we want to fetch or look up information on another sheet. This hassle can be removed by using VLOOKUP to pull information from another sheet to the spreadsheet we’re working on.
How to use VLOOKUP from another sheet in Google Sheets
Let us learn how with a practical example.
Suppose we have employees’ data: their names, IDs, date of birth, etc. in one sheet; and their salary details along with their IDs in another spreadsheet. We’ll name the first sheet “employee details” and the second sheet “salaries”.
We can look up individual employee’s salaries from the first sheet by using the VLOOKUP function.
Here’s the step-by-step procedure on how to do that:
Step 1: On the cell that you want to pull the result to, enter the formula. Here I’ve used the cell G3.
Step 2: In the search key parameter, enter the cell number containing value you want to look up. In this case, the employee ID. So I enter A3, or simply click the cell.
Step 3: Then go to the spreadsheet that you want to pull the information from and select the whole range. Press F4 so that the range is locked. You can also do this manually by entering the dollar sign ($) before each character.
This will ensure that the formula reference does not change when the formula is copied.
Step 4: Next, specify the range. Here, 2, since salary is listed in the second column of the range.
Step 5: And then we specify the last parameter as FALSE since we want the formula to find an exact match.
Enclose the formula and press Enter. This will pull the salary of employee 10001 into the cell in the first sheet. We can drag the value to the last cell to fetch the salaries of the other employees.
Tip:
If you want to look for specific values, you can select a reference cell in the search key parameter instead of the cell that contains the key. This makes the formula more versatile, as you can enter any value in the reference cell and it will return the answer.
VLOOKUP from another sheet in Google Sheets in a different workbook
We can also use the VLOOKUP from another sheet in Google Sheets to look up for values in a different workbook.
To do this, we use the IMPORTRANGE function in the range parameter in the VLOOKUP formula.
The procedure is similar to the steps above except that we have to switch the browser tab, then copy the URL of the spreadsheet, and enter the range manually.
So, we type in the formula, and in the step 4 above, we use the IMPORTRANGE function to reference the range from the spreadsheet in another workbook. Here’s how to do it.
Step 1: Go to the workbook and open the sheet you want to fetch the result from.
Step 2: Copy the URL from the browser address bar, paste it into the formula, and enclose it with a pair of quotation marks.
Step 3: Add a comma and specify the range string, which is the name of the spreadsheet, followed by an exclamation mark and the range of the data to pull it from. Also, place $ before each of the characters so that the range remains fixed. Enclose this too with double quotes.
The index is the same as above, which is 2, since we’re using the same data set. And set the last parameter as FALSE.
Given below is how the formula looks like:
=VLOOKUP(A3,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1QE-M3aCyPkHDWrs74Lww9CwNqkINOvCjofYKnIhp3yE/edit#gid=0","salaries!$A$2:$D$22"),2,FALSE)
Here, you’ll be presented with #REF! instead of the answer, as authorisation is needed before data can be pulled from another worksheet. Click Allow access. The answer will be returned in the cell. Dragging the cell to the rest of the cell similarly returns the salaries for the rest of the employees.
Using named range in VLOOKUP from another sheet in Google Sheets
You can create a named range and reference the range by that name. This can be handy if you have a range that you use often in your formula so that you can just use the name you’ve given to the range instead of going to the sheet and selecting or typing it in.
To create a named range, select the range from which to look for values→click Data→Named ranges. Give it a name and click Done.
You’ll now use this name you’ve given to the range in the formula instead of specifying the range.
Let us try it with one more example.
In the spreadsheet containing salary details, create a named range and name it salaries.
The VLOOKUP formula will now look like this:
=VLOOKUP(A3, salaries, 2, FALSE)We can likewise use the named range in VLOOKUP from another sheet in Google Sheets from another workbook. Simply replace the range_string parameter in IMPORTRANGE function with the name of the named ranged. For this, we’ll name it salaries2.
The formula is as under:
=VLOOKUP(A3,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1QE-M3aCyPkHDWrs74Lww9CwNqkINOvCjofYKnIhp3yE/edit#gid=0","salaries2"),2,FALSE)We can, as in the previous cases, drag the result to the rest of the cells to pull the answers to the sheet.
Conclusion
The VLOOKUP function can, as we have seen, do a lot more than just look for values in the same worksheet. VLOOKUP from another sheet in Google Sheets is one of the many things you can do with it.
Share this article if you find it helpful. Help us to help more people. Visit our blog for more useful tips and tutorials on Google Sheets.
Similar articles
https://blog.tryamigo.com/how-to-use-if-function-across-multiple-sheets/
https://blog.tryamigo.com/how-to-use-xlookup-function-in-google-sheets/