Today we will show how to use **the VLOOKUP formula** with multiple sheets in Excel. Actually, one of the most important and widely used functions of Excel is **the VLOOKUP function**. Furthermore, we can use **the VBA VLOOKUP function** to look for specific data in a single worksheet, or within a range of worksheets.

Also, today we will show how we can utilize **VLOOKUP formulas** to look for some specific data in **multiple worksheets** in Excel.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here:

## An Introduction to Excel’s VLOOKUP Function

**=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])**

- This function takes a range of cells called
**table_array**as an argument. - Then, searches for a specific value called
**lookup_value**in the**first**column of the**table_array**. - Furthermore, looks for an approximate match if the
**[range_lookup]**argument is**TRUE**, otherwise searches for an exact match. Here, the default is**TRUE**. - If it finds any match of the
**lookup_value**in the**first**column of the**table_array**, moves a few steps right to a specific column**(col_index_number).**

Then, returns the value from that cell.

Additionally, we have attached an example of this **VLOOKUP** function. Now, look at the following figure.

**Formula Breakdown**

Here, the formula **VLOOKUP(G8,B4:D15,3,FALSE)** searched for the value of **G8** cell “**Angela**” in the **first** column of the table: **B4:D15**.

After it found one, it moved right to the **3rd **column (As the **col_index_number** is **3**.)

Then returned the value from there, was **322**.

## 5 Ways to Use VLOOKUP Formula in Excel with Multiple Sheets

Here, we have a workbook with the marks in the written and viva examinations of some candidates in three weeks in different worksheets. In addition, the name of the first one is **Week 1**.

Then, the name of the** 2nd** worksheet is **Week 2**.

Lastly, the name of the** 3rd** worksheet containing marks of** Marco Group** is **Week 3**.

Now, our objective is to extract their marks from the **three** worksheets to the new worksheet using the **VLOOKUP** function of Excel.

### 1. VLOOKUP Formula to Search on Each Worksheet Separately

Here, we have a new worksheet called **“VLOOKUP only”** with the names of all the candidates sorted alphabetically **(A to Z)**. Now, we will use the **VLOOKUP** formula to search from multiple sheets in Excel.

First of all, we will search through the** three** worksheets separately.

Here we will search** lookup_value** from one worksheet into a range of cells of another worksheet.

The syntax of the formula will be:

**=VLOOKUP(lookup_value,‘Sheet_name’!table_array, col_index_number,FALSE)**

- To search for the
**Marks**in**Written**of the**Candidates of Week 1**, enter this formula in the**C5**cell of the new worksheet:

`=VLOOKUP(B5,'Week 1'!$B$5:$D$10,2,FALSE)`

- Subsequently, press
**ENTER**.

This is showing** #N/A!** Error, because the value of the cell **B5** in the **“VLOOKUP only”** sheet, **Alex Hales**, is not there in the range **B5:D10** of the sheet **“Week 1****“**.

- Then, drag the
**Fill Handle**icon.

As a result, we see the marks of only those candidates who appeared in **Week 1** are being shown, the rest are showing errors.

- Similarly, to find the viva mark, write down the following formula in the
**D5**cell.

`=VLOOKUP(B5,'Week 1'!$B$5:$D$10,3,FALSE)`

- Then, press
**ENTER**.

- Then, drag the
**Fill Handle**icon to apply the formula in the rest of the cells.

So, we see the marks of only those candidates who appeared in **Week 1** are being shown, the rest are showing errors.

Furthermore, we can perform a similar task for **Week 2** and **Week 3** also, but that will not satisfy our needs. Therefore, we have to search for a better approach.

**Notice:**Here, we have used relative cell reference for the

**lookup_value (B5)**, but absolute cell reference for the

**table_array ($B$5:$D$10)**. Because we want the

**lookup_value**to increase one by one during dragging the

**Fill Handle**icon, but the

**table_array**remains constant.

**Read More:** **Why VLOOKUP Returns #N/A When Match Exists? (5 Causes & Solutions)**

### 2. Search on Multiple Sheets with IFERROR Function in Excel

This time we will first search for a candidate in the first worksheet (**Week 1**).

Then, if we do not find him/her in the first worksheet, we will search in the second worksheet (**Week 2**).

And if we still do not find him/her, we will search in the third worksheet (**Week 3**).

If we do not find him/her still, we will decide that he/she was **absent** from the exam.

In the previous section we saw, **VLOOKUP** returns **N/A! **Error if it does not find any match to the **lookup_value **in the** table_array**.

So this time we will nest** VLOOKUP** functions within **the** **IFERROR function** to handle the errors.

Therefore the syntax of the formula will be:

**=IFERROR(VLOOKUP(lookup_value,”Sheet1_Name”!table_array,col_index_number,FALSE),IFERROR(VLOOKUP(lookup_value,”Sheet2_Name”!table_array,col_index_number,FALSE),IFERROR(VLOOKUP(lookup_value,”Sheet3_Name”!table_array,col_index_number,FALSE),”Absent”)))**

- Now, enter the following formula in the
**C5**cell of the**“VLOOKUP & IFERROR”**sheet.

`=IFERROR(VLOOKUP(B5,'Week 1'!$B$5:$D$10,2,FALSE),IFERROR(VLOOKUP(B5, 'Week 2'!$B$5:$D$10,2,FALSE),IFERROR(VLOOKUP(B5,'Week 3'!$B$5:$D$10,2,FALSE),"Absent")))`

- Then, press
**ENTER**.

As a result, you will see the written marks of **Alex Hales**.

Then, we will find the viva marks of Alex Hales.

- So, write down the following formula in the
**D5**cell.

`=IFERROR(VLOOKUP(B5,'Week 1'!$B$5:$D$10,3,FALSE),IFERROR(VLOOKUP(B5, 'Week 2'!$B$5:$D$10,3,FALSE),IFERROR(VLOOKUP(B5,'Week 3'!$B$5:$D$10,3,FALSE),"Absent")))`

- After that, press
**ENTER**.

- Then, select both cells
**C5**and**D5**. - Consequently, drag the
**Fill Handle**icon to**AutoFill**the corresponding data in the rest of the cells**C6:D24**.

Lastly, you will see both the marks in written and viva for all the candidates.

**Read More:** **VLOOKUP Example Between Two Sheets in Excel**

**Similar Readings**

**VLOOKUP Not Working (8 Reasons & Solutions)****Excel LOOKUP vs VLOOKUP: With 3 Examples****What Is a Table Array in VLOOKUP? (Explained with Examples)****How to Use Nested VLOOKUP in Excel (3 Criteria)****Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)**

### 3. Using Combined Formula to Search on Multiple Sheets in Excel

Actually, the nested** IFERROR** and **VLOOKUP** formula that we used earlier is helpful, but still a bit complex to use. Basically, there is a high probability of getting confused and generating errors if there are a lot of worksheets.

Thus, we will generate another formula using the **INDIRECT**, **INDEX**, **MATCH**, and **COUNTIF** functions that look even more complex, but is comparatively easier to apply when there are a lot of worksheets.

- First of all, create a horizontal array with the names of all the worksheets. Here, we have created one in
**F5:H5**cells.

- Then, insert the following formula in the
**C5**cell.

`=IFERROR(VLOOKUP(B5,INDIRECT("'"&INDEX($F$5:$H$5,1,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!B5:B10"),B5)>0,0))&"'!$B$5:$D$10"),2,FALSE),"Absent")`

- Subsequently, press
**ENTER**.

**Formula Breakdown**

- Firstly,
**COUNTIF(INDIRECT(“‘”&$F$5:$H$5&”‘!B5:B10”),B5)**returns how many times the value in cell**B5**is present in the range**‘Week 1′!B5:B10**,**‘Week 2’!B5:B10**and**‘Week 3’!B5:B10**respectively. [Here**$F$5:$H$5**is the names of the worksheets. So the**INDIRECT**formula receives**‘Sheet_Name’!B5:B10**.]**Output: {0,0,1}**.

- Secondly,
**MATCH(TRUE,{0,0,1}>0,0)**returns in which worksheet the value in**B5**is present.**Output:****3**.

- Here it returned
**3**as the value in**B5**(**Alex Hales**) is in worksheet no**3**(**Week 3**). - Thirdly,
**INDEX($F$5:$H$5,1,3)**returns the name of the worksheet where the value in cell**B5**is.**Output: “Week 3”**.

- Fourthly,
**INDIRECT(“‘”&”Week 3″&”‘!$B$4:$D$9”)**returns the total range of cells of the worksheet in which the value in**B5**is present.**Output: {“Nathan Mills”,72,59;”Ruth Williamson”,53,55;”Alex Hales”,67,70;”Matthew Shepherd”,76,45;”Christina Paul”,69,75;”Ricardo Moyes”,57,61}.**

- Finally,
**VLOOKUP(B5,{“Nathan Mills”,72,59;”Ruth Williamson”,53,55;”Alex Hales”,67,70;”Matthew Shepherd”,76,45;”Christina Paul”,69,75;”Ricardo Moyes”,57,61},2,FALSE)**returns the**2nd column**of the row from that range where the value in cell**B5**matches.**Output: 67**.

- So, this is the written exam mark we were looking for.
- And in case the name is not found in any worksheet, it will return
**“Absent”**because we nested it within an**IFERROR**function.

Here, you can use a similar formula to find out the** Viva** marks of the candidates.

- So, change the
**col_index_number**from**2**to**3**and write the formula.

`=IFERROR(VLOOKUP(B5,INDIRECT("'"&INDEX($F$5:$H$5,1,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!B5:B10"),B5)>0,0))&"'!$B$5:$D$10"),3,FALSE),"Absent")`

- Then, press
**ENTER**to get the result.

- Then drag the
**Fill Handle**icon.

Lastly, we have got both the written and viva marks of all the candidates. Moreover, whose names have not been found have been marked as absent.

**Read More:** **INDEX MATCH vs VLOOKUP Function (9 Examples)**

### 4. VLOOKUP Formula with Dynamic Column Index Number

Until now, to extract the marks in the written exam, we are using **col_index_num** as **2**. And for the viva marks, **3**.

Actually, we are inserting the formulas separately in both columns.

Eventually, when we have several columns, it will be quite troublesome to insert formulas in all the columns separately.

So, this time we will generate a formula so that we can insert the formula in the **first **column, and drag it to all the columns through the **Fill Handle **icon.

Simple. Instead of inserting a pure number as the **col_index_num**, insert **COLUMNS($C$1:D1)** if the formula is in **column C** (**For Written Marks**).

Then, it will return **2**.

Then, if we drag it to **column E**, it will become **COLUMNS($C$1:E1)** and return **3**. And so on.

- So now we change the formula in the previous section to this:

`=IFERROR(VLOOKUP($B5,INDIRECT("'"&INDEX($F$5:$H$5,1,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!B5:B10"),$B5)>0,0))&"'!$B$5:$D$10"),COLUMNS($C$1:D1),FALSE),"Absent")`

- Then, press
**ENTER**.

- After that, drag the
**Fill Handle**icon to the right side for getting the**Viva**marks.

- Then, drag the
**Fill Handle**icon down.

Lastly, you will see both the marks in written and viva for all the candidates.

**Notice:**Here, we have changed the

**lookup_value**from

**B5 to $B5**, so that it remains unchanged when dragging to the right, but changes when dragging down.

**Read More:** **Excel Dynamic VLOOKUP (with 3 Formulas)**

### 5. VLOOKUP Formula with Combined Functions in Excel

Here, we will use another** VLOOKUP **formula in Excel with multiple sheets ignoring the **IFERROR** function. So, let’s see the steps given below.

**Steps:**

- Firstly, you have to select a new cell
**C5**where you want to keep the written marks. - Secondly, you should use the formula given below in the
**C5**cell.

`=VLOOKUP(B5,INDIRECT("'"&INDEX($F$5:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!$B$5:$D$10"),B5)>0),0))&"'!$B$5:$D$10"),2,FALSE)`

- Thirdly, press
**ENTER**.

- Similarly, use the following formula in the
**D5**cell to get the Viva marks.

`=VLOOKUP(B5,INDIRECT("'"&INDEX($F$5:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!$B$5:$D$10"),B5)>0),0))&"'!$B$5:$D$10"),3,FALSE)`

- Subsequently, press
**ENTER**.

- Then drag the
**Fill Handle**icon.

Lastly, you will see both the written and viva marks of all the candidates. Moreover, you will see the **#N/A** error where the names were missing in the mentioned sheets.

## Limitations of VLOOKUP Function and Some Alternatives in Excel

- Here, you cannot use the
**VLOOKUP**function when the**lookup_value**is not in the**first**column of the table. For example, in the previous example, you cannot use the**VLOOKUP**function to know the name of the candidate who got a**90**on the written exam. - However, you can use the
**IF**,**IFS**,**INDEX MATCH**,**XLOOKUP**, or**FILTER**functions of Excel to solve this (Here, you may visit**this article**). - Furthermore,
**VLOOKUP**returns only the**first**value if more than one value matches the**lookup_value**. In these cases, you can use the**FILTER**function to get all the values (Here, you may visit**this article**).

## How to Apply VLOOKUP Formula in Excel with Multiple Workbooks

In this section, we will discuss how to apply the **VLOOKUP **formula in Excel with multiple workbooks. Now, let’s have the following workbook named **Mock Test Marks**. Additionally, in that workbook, there are **three** worksheets too. They are **Week 1, Week 2**, and **Week 3**.

At this time, we want to compare the preliminary and final written marks getting by the candidates. At first, we found the final written marks. Here, you can find that by following any of the previous methods. Now, we will extract the preliminary written marks from another workbook.

- So, write down the following formula in the
**D5**cell.

`=IFERROR(VLOOKUP(B5,'[Mock Test Marks.xlsx]Week 1'!$B$5:$D$10,2,FALSE),IFERROR(VLOOKUP(B5, '[Mock Test Marks.xlsx]Week 2'!$B$5:$D$10,2,FALSE),IFERROR(VLOOKUP(B5,'[Mock Test Marks.xlsx]Week 3'!$B$5:$D$10,2,FALSE),"Absent")))`

*Here, when using this formula, you must open both workbooks. Otherwise, you have to use the fill path/location instead of using only the file name.*

- Then, press
**ENTER**.

- Then, drag the
**Fill Handle**icon down.

Lastly, you will see both the final and preliminary written marks for all the candidates.

## Practice Section

Now, you can practice the explained method by yourself.

## Conclusion

Using these methods, you can use the **VLOOKUP **function of Excel as a formula to extract data from multiple sheets in a workbook. So, do you have any other questions? Feel free to ask us.

## Further Readings

**How to Vlookup and Sum Across Multiple Sheets in Excel (2 Formulas)****INDIRECT VLOOKUP in Excel****VLOOKUP with Numbers in Excel (4 Examples)****Use of VBA VLOOKUP to Find Values from Another Worksheet in Excel****XLOOKUP vs VLOOKUP in Excel (Comparative Analysis)****VLOOKUP to Search Text in Excel (4 Easy Ways)****How to Use VLOOKUP with COUNTIF (3 Ways)**