How to use VLOOKUP in Google Sheets from a different tab or sheet?

Users who extensively use spreadsheet programs like Google Sheets or Microsoft Excel are well aware of VLOOKUP, which is a powerful tool available for such programs to look up vertically in a record within a table and fetch results based on a specific key. VLOOKUP is extremely easy to learn and carry out when the lookup keyword and the table are present in the same sheet. But what if the table or data is in one sheet, and you want to get the VLOOKUP results in another sheet? 

There can be several situations when you need to carry out such an operation, and hence I am here with how to use VLOOKUP in Google Sheets from a different tab or sheet. Even if the data is in another Google Sheets spreadsheet, you can also use VLOOKUP to fetch results from that sheet. Yes, it is possible, and hence, read the tutorial till the end to know how.

So, without any further delay, let’s get started with how to use VLOOKUP in Google Sheets from a different tab or sheet.

Using VLOOKUP when the table is present in the same file

• Open the file in Google Sheets where you want to carry out the VLOOKUP operation. Once there, open a new sheet in the same file, and let the example go as follows.

• You have the keyword that needs to be looked up, in cell B1 of Sheet 2, and want the VLOOKUP result in cell B2 of Sheet 2.

• Now, let’s say, the Roll number of the candidates is present across column A of Sheet 1, and the marks each candidate has obtained are present across column B of Sheet 1.

• Hence, the challenge here is to look up the value present within cell B1 of Sheet 2, in Sheet 1 across column A, and hence return the corresponding value in column B as the result in the cell B2 of Sheet 2.

So, the formula in the cell B2 of Sheet 2 will go as follows.

=VLOOKUP(B1,Sheet1!A1:Sheet1!B100,2)

Or,

=VLOOKUP(B1,Sheet1!A1:B100,2)

The ‘Sheet1!‘, as you might have guessed, refer to Sheet1, and any cell address followed by that will refer to the cell in the sheet mentioned, instead of the current sheet. 

Furthermore, the two formulas will be interpreted by Google Sheets in the same way. By entering Sheet1 in the range for the first cell, the last cell will be considered to be in the same sheet, and hence entering Sheet1! twice is redundant. But you can optionally do that if you find it useful for understanding while dealing with multiple sheets.

So, that’s how to use VLOOKUP in Google Sheets from a different tab or sheet if the tab or sheet is present in the same file.

Using VLOOKUP when the table is present in a different Google Sheets file

But, there can be certain situations when the sheet containing the table or data is present in a different Google Sheets file. It is also possible to use VLOOKUP to get results from another sheet or file. 

In such a situation, the first thing that you need to do is, connect the current sheet with the sheet where the data is present.

The IMPORTRANGE function here connects to another Google Sheets file and hence pulls data from the cell range referred to in the connected file in Google Sheets.

The syntax for the IMPORTRANGE function goes as follows.

=IMPORTRANGE (“SHEET_URL", "SHEET! RANGE") 

The target sheet URL can be copied from the address bar, the Sheet! refer to the sheet, and hence the range.

In the VLOOKUP function, use the IMPORTRANGE function as a nested function when the range is supposed to be entered, i.e., just after the search key.

So, if the keyword to be looked up is in cell number B1, and the data is present in cells A1 to B100 within Sheet1 in a different file, the formula will go as follows.

Here, IMPORTRANGE is used as a nested function within VLOOKUP.

=VLOOKUP(B1,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1G4fWvqnQ0g05..........VWOTxKqp-_KhNEMSQ88hCc","Sheet1!A1:B100"),2)

*Some part of the URL is removed here.

After you use the above function, you will be asked for permission to connect to the external sheet, for the very first time. Simply click on ‘Allow access’. As the data is present in an external file, it might take some time for VLOOKUP to deliver the results, but in my observation, it was almost instantaneous.

However, if you are connecting to an external Google Sheets file, you must have the necessary read permissions to access the file. You might request the owner to give you read permissions, and hence use the IMPORTRANGE function to use VLOOKUP by connecting to the external sheet.

So, that’s how to use VLOOKUP in Google Sheets from a different tab or sheet, no matter whether the sheet is present in the same file or another file in Google Sheets. Hope you will find the above two ways to use VLOOKUP on Google Sheets useful in your everyday work.

Do you have any further questions regarding the topic? Feel free to comment on the same below.

Other Articles:

• How to Electronically sign documents for free using Adobe Reader for PC and mobile
• 10 Best Screen Recorders for Windows 10 or 11
• How to embed a YouTube video in a Google Slides presentation
• Top Artificial Intelligence Websites and Tools that you should use
• How to install and use the same WhatsApp account on up to 4 smartphones