How to Use Vlookup Across Different Sheet Tabs in Google Sheets

0
204
How to Use Vlookup Across Different Sheet Tabs in Google Sheets

Just make a search on this site to see plenty of Vlookup related tutorials vary from basic to advanced one. Here is another addition to these awesome Vlookup tutorials. In this Google Doc Spreadsheet tutorial, let’s learn how to use Vlookup across different sheet tabs in Google Sheets. That means, we can use Vlookup with search key in one sheet tab (or even search keys with array) and return corresponding values from another sheet tab or tabs. We can learn this Vlookup use with one simple example.

Example to Vlookup Across Different Sheet Tabs in Google Sheets

1. Vlookup Involving One Single Sheet

For the example purpose, I’ve imported a sample data that showing the index of military strength of the world powers by using Google Sheets IMPORTHTML function. You can see that below.

=IMPORTHTML(“https://en.wikipedia.org/wiki/List_of_countries_by_Military_Strength_Index”,”table”, 1)

If you want, you can use the above same formula by copying it from here but re-typing the double quotes. So that you can easily follow my tutorial step by step, else just read on.

Here is the screenshot of what I’ve imported (only part of the data)

Example to Vlookup Across Different Sheet - Sample Data

This data is in “Sheet1” of my Google Sheet. Just for your reference, have a look at the IMPORTHTML formula in cell A1. Now let’s move to “Sheet2” of this Spreadsheet.

In “Sheet2”, I have few countries’ names in Column A. Against that names, I want to see military strength score of that countries. Here you can see how to use Vlookup across different tabs in Google Sheets. First learn this and then use this type of formula in your different types of data.

vlookup in array across sheet tabs

The following is the formula which I’ve applied in “Sheet2!B2”. It’s an array formula so that it would expand to adjoining cells in the column automatically.

=ArrayFormula(iferror(vlookup(A2:A,Sheet1!B3:I,2,FALSE)))

Vlookup Array Formula Explanation:

This’s a normal Vlookup formula. But the question here is the use of ArrayFormula and IFERROR. As you know, here in Sheet2!A2:A contain the search keys. It’s a range so we should use ArrayFormula to expand the result. The IFERROR is used to avoid error in the result when there is no match.

If you want to learn in detail about Vlookup Array use, please go to the below tutorial.

Vlookup in Array: How to Use Vlookup to Return An Array Result in Google Sheets

In Vlookup the search key should always be in the first column of data range. This is important. That’s why I’ve opted the range “Sheet1!B3:I” in the formula instead of “Sheet1!A3:I”. If you can’t meet this condition, the alternative solution is INDEX and Match Combination.

Similar: Index Match – Better Alternative to Vlookup and Hlookup in Google Sheets

2. Vlookup Involving Multiple Sheet Tabs (Here Three Tabs)

The below screenshot showing the result of a Vlookup formula that involving multiple sheet tabs in calculation. Here in column B2:B4 you can see the name of three food items. The first items is Soup, then a non vegetarian item that followed by a vegetarian item. This is the search keys or lookup values in my Vlookup formula. For these three items the price list in sheet tabs New1, New2 and New3 respectively. As a side note the search keys are set as a drop down menu using data validation. But you can enter the search keys as it’s.

Learn Data Validation: Restrict People from Entering Invalid Data on Google Doc Spreadsheet

vlookup involving search keys in different tabs

The Vlookup formula in Cell C2, populates the price of the food items in Column B. Here is that formula and it’s a perfect example to the use of Vlookup across different Sheet Tabs in Google Sheets.

=ArrayFormula(IF(len(B2:B),vlookup(B2:B,{‘New1′!B3:C;’New2′!B3:C;’New3’!B3:C},2,FALSE),””))

I know I should explain this formula in details. Before going to that see the content in New1, New2 and New3 sheets.

data in multiple tabs used in vlookup

Vlookup Formula Across Multiple Tabs: Formula Explanation

See the colouring properly so that you can understand how to use Vlookup across different Sheet tabs in Google Sheets. The only changes is in the range.

Normal Vlookup Syntax:

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

Array Formula Syntax By InfoInspired:

VLOOKUP(search_key, {range1;range2;range3…}, index, [is_sorted])

Formula:

=ArrayFormula(IF(len(B2:B),vlookup(B2:B,{‘New1′!B3:C;’New2′!B3:C;’New3’!B3:C},2,FALSE),””))

I’ve already detailed above why the ArrayFormula is required. Then the use of LEN function is to limit the calculations to the rows where the value is existing. It excludes all blank rows. Here is my Google Sheet where I’ve made the above Experiments.

Hope it can save your valuable time in learning the use of Vlookup Across Different Tabs in Google Sheets. Thanks for the stay.

LEAVE A REPLY

Please enter your comment!
Please enter your name here