**The VLOOKUP function** is basically used to search values vertically, and itโs a very easy and useful tool in Excel. This article will discuss **how to utilize the Excel VLOOKUP formula with multiple sheets**. This function actually returns dates, strings, numerical values, etc.

## ๐ Download Excel File

Download the Excel file we used to create this article so you can practice.

## Learn to Utilize VLOOKUP Function with Multiple Sheets Using Excel Formula with These 5 Examples

There are several ways of using the **VLOOKUP** formula with several sheets. We used three datasets that contained GRE scores from admission candidates from three different countries.

In our first sheet, scores of candidates from Mexico are given.

The scores of Canadian candidates are shown on the following sheet.

On the third sheet, scores for Chinese candidates are given.

**Example**

### 1. Integrating Multiple Functions

You can combine multiple functions with **VLOOKUP** to achieve your goal. The functions that you can use with **VLOOKUP **are ย **IFERROR**, **INDIRECT**, **MATCH**, and **COUNTIF** functions.

Letโs see how to do this with some simple steps.

โฌ๏ธโฌ๏ธ **STEPS **โฌ๏ธโฌ๏ธ

- First, in any part of your worksheet, name the other sheets youโll be looking at for data. Here, we have created an array with other sheetsโ names on cell
**F6:H6**.

- After that, insert the given formula, we have inserted the formula given below in cell
**C6**.

**=IFERROR(VLOOKUP(B6,INDIRECT(โโโ&INDEX($F$6:$H$6,1,MATCH(TRUE,COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!B6:B17โณ),B6)>0,0))&โโ!$B$6:$D$17โณ),2,FALSE),โAbsentโ)**

**๐จ Formula Breakdown**

**IFERROR(VLOOKUP(B6,INDIRECT(โโโ&INDEX($F$6:$H$6,1,MATCH(TRUE,COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!B6:B17โณ),B6)>0,0))&โโ!$B$6:$D$17โณ),2,FALSE),โAbsentโ)**

๐ The number of times the value in cell B6 is present in the range Mexico is first provided by **COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!B6:B17โ)**! Canada, **B6:B17** Chinese, and **B6:B17! B6โB17**, in that order.

๐ The worksheet names are **$F$6:$H$6** in this instance. As a result, **โSheet Nameโ** is passed to the **INDIRECT** **formula! B6:B17**. The functionโs return value in this section is, therefore **{0, 0, 1}**.

๐ This causes **MATCH(TRUE,COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!B6:B17โ),$B6)>0,0)** to change into **MATCH(TRUE,0,0,1>0,0)**, which returns the worksheet that has the value in **B6**. In this case, the result is **3**.

๐ As the value in **B6 (Lin)** is found in worksheet no.** 3**, it thus returned **3**ย **( Chinese)**.

๐ This causes **INDEX($F$6:$H$6,1,MATCH(TRUE,COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!B6:B17โ),$B6)>0,0)** to change into **INDEX($F$6:$H$6,1,3)**, which is in charge of returning the precise name of the worksheet where the value in cell **B6** is. Therefore, it gives value **Chinese**.

๐ย Therefore, the **INDIRECT** function becomes, **INDIRECT(โโโ&Chinese&โโ!$B$6:$D$11โ)** and provides the total cell ranges in which the value in **B6** is present. As a result, the output becomes,**{โChengโ,150,160;โโHuanโ,158,140;โโMeiโ,150,149;โโYang,170,165;โโKai ,130,140;โโLin,165,140โ}**.

๐ย Last but not least, **VLOOKUP($B6,โChengโ,150,160;โHuanโ,158,140;โMeiโ,150,149;โYang,170,165;โKai,130,140;โLin,165,140โณ,2,FALSE)** returns the second column of the row from that range where the value in cell **B6** matches. The result is **165**, which is the score we wanted on the quantitative exam.

๐ย Because we nested the name within an **IFERROR** function, it will return **โAbsentโ** if the name cannot be found in any worksheet.

- After that, on the other cell enter the formula below.

**=IFERROR(VLOOKUP(B6,INDIRECT(โโโ&INDEX($F$6:$H$6,1,MATCH(TRUE,COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!B6:B17โณ),B6)>0,0))&โโ!$B$6:$D$17โณ),3,FALSE),โAbsentโ)**

- Next, we have to fill the available cells. Use
**Fill Handle**and drag it toward the end. You will see that the cells are now filled.

- Notice that data the missing data from those three cells are shown as
**Absent**in that sheet, and the rest of the sheet is filled with data torn from those three sheets. - Finally, follow the same process of dragging the icon of
**Fill Handle**ย for the adjacent cell to fill up.

**๐ Read More: VLOOKUP to Find Duplicates in Two Columns in Excel**

**Example**

### 2. Combining IFERROR and VLOOKUP Functions

We can combine **VLOOKUP **with **the IFERROR function**. This function searches data in multiple worksheets one by one. Suppose this is a worksheet that contains the names of all candidates. Now, we want to find out who among these people has submitted their GRE scores. Letโs follow the steps below.

โฌ๏ธโฌ๏ธ **STEPS **โฌ๏ธโฌ๏ธ

- First, weโll look up the results from the first worksheet. To do this, we have entered the formula given below in the cell of the quantitative score, which is
**C6**. Enter the formula and see the data.

The formula is given below.

**=IFERROR(VLOOKUP(B6,Mexico!$B$6:$D$11,2,FALSE),IFERROR(VLOOKUP(B6, Canada!$B$6:$D$11,2,FALSE),IFERROR(VLOOKUP(B6,Chinese!$B$6:$D$11,2,FALSE),โNot Foundโ)))**

**๐จ Formula Breakdown**

**IFERROR(VLOOKUP(B6,Mexico!$B$6:$D$11,2,FALSE),IFERROR(VLOOKUP(B6, Canada!$B$6:$D$11,2,FALSE),IFERROR(VLOOKUP(B6,Chinese!$B$6:$D$11,2,FALSE),โNot Foundโ)))**

Letโs analyze the nested** IFERROR** function by its syntax.

๐ย **B6 **is the *lookup_value, ***Mexico **refers sheet 1 and** $B$6:$D$11** is the* table_array* of that sheet. The *col_index_number* is represented by **2.**So,**VLOOKUP(B6,Mexico!$B$6:$D$11,2,FALSE) **is to look up the value in sheet 1.

๐ย Then comes the **IFERROR** function, which says that if the value is not found in the first referred sheet then lookup this value in the other two sheets, which is represented by following the same syntax. **VLOOKUP(B6, Canada!$B$6:$D$11,2,FALSE) **searches in the 2nd sheet, and if this returns False, which means the value is not in that sheet it goes to look in sheet 3 by **VLOOKUP(B6,Chinese!$B$6:$D$11,2,FALSE)**.

๐ย And finally, if this value is not even found in the last sheet it will show up the message** โNot Foundโ**.

- After that, for the next cell, you have to apply almost the same formula but with modifications. Here is the formula that we have used.

**=IFERROR(VLOOKUP(B6,Mexico!$B$6:$D$11,3,FALSE),IFERROR(VLOOKUP(B6, Canada!$B$6:$D$11,3,FALSE),IFERROR(VLOOKUP(B6,Chinese!$B$6:$D$11,3,FALSE),โNot Foundโ)))**

- After that, select all cells and use the icon of
**Fill Handle**to fill up the remaining cells.

- Finally, after dragging
**Fill Handle**icon downward, you will find the cells are filled, and the data that was not on those three sheets have shown the message**Not Found**.

**๐ Read More: 8 Ways to VLOOKUP to Return Multiple Values Vertically in Excel**

**Example**

### 3. Utilizing Dynamic Column Index Number

In other approaches of Excelย **VLOOKUP** in multiple sheets formula, formulas are inserted in every cell. In other processes, **col_index_num **is defined as **2** to extract the **quantitative **marks and **3** to obtain the **verba**l marks. But in this example, we are going to use **COLUMNS($C$1:D1) **instead of **col_index_num **. Follow the steps illustrated below to learn more.

โฌ๏ธโฌ๏ธ **STEPS **โฌ๏ธโฌ๏ธ

- Firstly, insert the sheets name in any cells on your worksheet. We have used the cell range
**F6:H6**.

- After that,
**Enter**the given formula.

**=IFERROR(VLOOKUP($B6,INDIRECT(โโโ&INDEX($F$6:$H$6,1,MATCH(TRUE,COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!B6:B17โณ),$B6)>0,0))&โโ!$B$6:$D$17โณ),COLUMNS($C$1:D1),FALSE),โAbsentโ)**

**๐จ Formula Breakdown**

**IFERROR(VLOOKUP($B6,INDIRECT(โโโ&INDEX($F$6:$H$6,1,MATCH(TRUE,COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!B6:B17โณ),$B6)>0,0))&โโ!$B$6:$D$17โณ),COLUMNS($C$1:D1),FALSE),โAbsentโ)**

๐ย Firstly, the function** COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!B6:B17โ)** shows how many occasions the cell **$B6 **ย has occurred in the required range **Mexico!B6:B17**, **Canada!B6:B17** and **Chinese!B6:B17** respectively.

๐ย Here **$F$6:$H$6** is worksheetโs name. Therefore, **INDIRECT** formula takes **โSheet_Nameโ!B6:B17**.So this part of the function returns the value **{0,0,1}**.

๐ย Then , **MATCH(TRUE,COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!B6:B17โ),$B6)>0,0) **becomes **MATCH(TRUE,{0,0,1}>0,0), **which provides the worksheet value in **B6** is present.Here, the output becomes** 3**.

๐ย ย It provided **3** because the amount in **B6** (**Lin**) is in 3rd worksheetย (** Chinese**).

๐ย After that,**INDEX($F$6:$H$6,1,MATCH(TRUE,COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!B6:B17โ),$B6)>0,0)** becomes** INDEX($F$6:$H$6,1,3)**which is responsible for returning the worksheetโs name where the cell **B6** is located. So, it returns the value **Chinese**.

๐ย Then, the **INDIRECT** function becomes, **INDIRECT(โโโ&Chinese&โโ!$B$6:$D$11โ)** and returns the all cells range. As a result, the output becomes,**{โChengโ,150,160;โโHuanโ,158,140;โโMeiโ,150,149;โโYang,170,165;โโKai ,130,140;โโLin,165,140โ}**.

๐ย Also, the function **COLUMNS($C$1:D1) **refers to the column number, which we are searching for. Here it returns** 2**.

๐ย And so, **VLOOKUP($B6,{โChengโ,150,160;โโHuanโ,158,140;โโMeiโ,150,149;โโYang,170,165;โโKai ,130,140;โโLin,165,140โ},2,FALSE)** provides the rangeโs second column and row, where cell B6โs value is the match. The output becomes **165**. So, it is the required quantitative exam mark.

๐ย On the other hand if The name cannot be available in another worksheet, that will provide** โAbsentโ**.

- Now, you can drag
**Fill Handle**icon and fill it up. We obtained the verbal marks by dragging**Fill Handle**icon from the side of the quantitative marks.

- Finally, drag the
**Fill Handle**icon again downwards to fill up the other cells below.

- So, you have filled up all the cells by just inserting the formula into one of the cells.

**๐ Read More: 5 Ways to Extract Filtered Data into Another Excel Sheet**

**Example**

### 4. Applying INDIRECT Function

To use Excel **VLOOKUP** in multiple sheets formulaย you can also integrate it with **the INDIRECT**** function**. To know how to do this, follow the steps below.

- First, in any of the cells in your worksheet, write the sheets name that you would like to search for. We have selected an array in cell
**F6:H6**. - Then
**Enter**the given formula in your preferred cell; we have used cell**C6**.

**=VLOOKUP(B6,INDIRECT(โโโ&INDEX($F$6:$H$6,MATCH(1,โ(COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!$B$6:$D$17โณ),B6)>0),0))&โโ!$B$6:$D$17โ),2,FALSE)**

**๐จ Formula Breakdown**

**VLOOKUP(B6,INDIRECT(โโโ&INDEX($F$6:$H$6,MATCH(1,โ(COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!$B$6:$D$17โณ),B6)>0),0))&โโ!$B$6:$D$17โ),2,FALSE)**

๐ย At first,** COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!B6:B17โ)** provides how many occasions the cell **B6** is available in range **Mexico!B6:B17**, **Canada!B6:B17** and **Chinese!B6:B17** respectively.

๐ย Here **$F$6:$H$6** is the worksheetโs name. Therefore, **INDIRECT** formula takes **โSheet_Nameโ!B6:B17**.So this part of the function returns the value **{0,0,1}**.

๐ย Then , **MATCH(1,โ,COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!B6:B17โ),$B6)>0,0) **becomes **MATCH(TRUE,{0,0,1}>0,0), **which provides the worksheet value in **B6** is present.Here, the output becomes** 3**.

๐ย It gives **3** because cell value in **B6** (**Lin**) lies in worksheet no **3** (** Chinese**).

๐ย After that,**INDEX($F$6:$H$6,1,MATCH(TRUE,COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!B6:B17โ),$B6)>0,0)** becomes** INDEX($F$6:$H$6,1,3)**which is responsible for giving the worksheet name where the cell value **B6** is. So, it returns the value **Chinese**.

๐ย Then, the **INDIRECT** function becomes, **INDIRECT(โโโ&Chinese&โโ!$B$6:$D$11โ)** and provides the overall range of cells in which value in **B6** is present. Therefore, the output becomes,**{โChengโ,150,160;โโHuanโ,158,140;โโMeiโ,150,149;โโYang,170,165;โโKai ,130,140;โโLin,165,140โ}**.

๐ย And so, **VLOOKUP($B6,{โChengโ,150,160;โโHuanโ,158,140;โโMeiโ,150,149;โโYang,170,165;โโKai ,130,140;โโLin,165,140โ},2,FALSE)** delivers the row from that rangeโs second column where the value in cellย **B6** matches. The output becomes **165**. So, This is the score on the quantitative exam that we were after..

๐ย On the other side, it will display **N/A** or an error if the name is not present in any worksheet.

- After that, use the formula below to fill up the adjacent cell. This formula is quite the same as the previous one, but with a bit more cell modification.

**=VLOOKUP(B6,INDIRECT(โโโ&INDEX($F$6:$H$6,MATCH(1,โ(COUNTIF(INDIRECT(โโโ&$F$6:$H$6&โโ!$B$6:$D$17โณ),B6)>0),0))&โโ!$B$6:$D$17โ),3,FALSE)**

- In the next step, you have to use
**Fill Handle**in order to fill up the rest of the cells in that column. Drag the icon of**Fill Handle**downward, and you will find that you have filled the rest of the cells. - Follow the same steps for the adjacent column, using
**Fill Handle**icon in order to fill it up. Notice that the data that was not available in those three sheets is now showing the message error or**N/A**in both of the columns.

**๐ Read More: 6 Solutions If VLOOKUP Returns #N/A When Match Exists in Excel**

**Example**

### 5. Exploring Across Each Worksheet Separately Utilizing VLOOKUP Only

We can also use the Excelย **VLOOKUP** in multiple sheets formula only to search the data in multiple worksheets at the same time. However, keep in mind that by using this function, we will be able to search within each individual worksheet. See the following steps below to know more.

โฌ๏ธโฌ๏ธ **STEPS **โฌ๏ธโฌ๏ธ

- First, select the cell where you would like to see the extracted value. We have chosen cell
**C6**. - Now
**Enter**the given formula in your preferred cell.

**=VLOOKUP(B6,Mexico!$B$6:$D$11,2,FALSE)**

**๐จ Formula Breakdown**

**VLOOKUP(B6,Mexico!$B$6:$D$11,2,FALSE)**

Here,

๐ย **B6** is the *lookup_value*, which indicates the cell number, where we have stored the value that we are looking for in other sheets.

๐ย **Mexico **is the *sheet name* where we are searching for that value. As we are searching it in individual sheets separately, we have to change this to search in other sheets.

๐ย **$B$6:$D$11 **is the *table_array,* to specify the table in that sheet where to search for data.

๐ย **2 **is the *col_index_number.*

๐ย **False** is the *[range_lookup]*

๐ย This means the function takes an argument which is a range of cells, by *table_array*,then searches for specific value by *lookup_value *argument in the initial column of the array.

๐ย It looks for an exact match by the *[range_lookup], *False and if it is not available in that sheet, it shows a N/A or error, here as the result of Lin was not available in the first sheet, it is showing the error.

๐ย But if it finds the exact match of *lookup_value *in the first column of the* table_array, *it moves a few steps right for a specific column* col_index_number* and returns the value of this column.

- After that, you need to use
**Fill Handle**icon again in order to fill up the other cells.

- And notice that, the values which were available only in the first sheet have been shown; otherwise, itโs displaying an error.

- After completing one column, just follow the same process for the next column. Enter the given formula.

**=VLOOKUP(B6,Mexico!$B$6:$D$11,3,FALSE)**

- Utilize
**Fill Handle**icon just as before to fill all the cells in that column.

- By dragging the icon of
**Fill Handle**downward, we have filled the entire column.

Furthermore, you can follow this process and search all the other sheets available. The main disadvantage of this process is that you have to enter functions individually for all sheets, which will be a tedious process if you have many worksheets to search for.

However, this process is a useful one because the formula is quite simple in comparison to other formulas.

**๐ Read More: What Is Table Array in VLOOKUP in Excel?**

## ๐ Takeaways from This Article

๐ย You can now search multiple sheets individually or together.

๐ After reading this article you can now use the combinations of formulas that you have learned and used effectively.

## Conclusion

VLOOKUP is a blessing for Excel users, as it saves lots of time and effort for them, making it very easy to use. So, we will recommend that you use any of the formulas mentioned above whenever you have to take data from spreadsheets. We have shown five effective examples to use the **VLOOKUP** function with multiple sheets using the Excel formula. Please leave a comment if you want to give any suggestions or questions. Donโt forget to visit our **Excelden** page to enhance your Excel-related knowledge.

## Related Articles

**7 Ways to Use VLOOKUP to Compare Two Lists****Find MAX of Multiple Values Using VLOOKUP Function in Excel****5 Examples to Use IF and VLOOKUP as Nested Function in Excel****2 Ways to Use VLOOKUP Function with Two Lookup Values****3 Quick Ways to Vlookup and Return Multiple Values Horizontally**