How to Use VLOOKUP Formula in Excel with Multiple Sheets

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.

An Introduction to Excel’s VLOOKUP Function

• 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.

Use VLOOKUP Formula in Excel with Multiple Sheets: 5 Suitable Ways

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 function 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:

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

• 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.

• 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.

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:

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

• 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.

• 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.

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 are 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.

• Subsequently, press ENTER.

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.

• 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, those whose names have not been found have been marked as absent.

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:

• Then, press ENTER.

• After that, drag the Fill Handle icon to the right side to get 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.

Read More: How to Use Dynamic VLOOKUP in Excel 

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.

• Thirdly, press ENTER.

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

• 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.

Read More:7 Practical Examples of VLOOKUP Function in Excel

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. They are Week 1, Week 2, and Week 3.

At this time, we want to compare the preliminary and final written marks obtained 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.

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.

Download Practice Workbook

You can download the practice workbook from here:

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

• 10 Best Practices with VLOOKUP in Excel
• VLOOKUP Example Between Two Sheets in Excel
• Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP
• How to Make VLOOKUP Case Sensitive in Excel
• VLOOKUP from Another Sheet in Excel
• How to Remove Vlookup Formula in Excel
• How to Apply VLOOKUP to Return Blank Instead of 0 or NA
• How to Hide VLOOKUP Source Data in Excel
• How to Copy VLOOKUP Formula in Excel