VLOOKUP Formula in Excel with Multiple Sheets

VLOOKUP Formula in Excel with Multiple Sheets

One of the most important and widely used functions of Excel is VLOOKUP. We can use the VLOOKUP function to look for some specific data in a single worksheet, or within a range of worksheets.

Today I will be showing how we can utilize the VLOOKUP function to look for some specific data in multiple worksheets in Excel.

Download Practice Workbook

An Introduction to Excel’s VLOOKUP Function

Syntax of the Excel VLOOKUP Function

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
  • Takes a range of cells called table_array as an argument.
  • Searches for a specific value called lookup_value in the first column of the table_array.
  • Looks for an approximate match if the [range_lookup] argument is TRUE, otherwise searches for an exact match. Default is TRUE.
  • If it finds any match of the lookup_value in the first column of the table_array, moves few steps right to a specific number of column (col_index_number)
  • Then returns the value from that cell.

Look at the following figure.

Excel VLOOKUP Function

  • The formula VLOOKUP("Angela",C3:E13,3) searched for “Angela” in the first column of the table: C3:E13.
  • After it found one, it moved right to the 3rd column (As the col_index_number is 3.)
  • Then returned the value from there, 502.

Visit this link to know more about the VLOOKUP function of Excel.

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, Week 1, Week 2 and Week 3 respectively, in different worksheets.

Data Set for VLOOKUP Formula with Multiple Sheets

Data Set for VLOOKUP Formula with Multiple Sheets

Data Set for VLOOKUP Formula with Multiple Sheets

Now we have a new worksheet called “All candidates”  with the names of all the candidates sorted alphabetically (A to Z).

New Worksheet for VLOOKUP Formula with Multiple Sheets

Our objective is to extract out 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

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 first cell of the new worksheet:

=VLOOKUP(B4,'Week 1'!$B$4:$D$9,2,FALSE)

VLOOKUP Formula with Multiple Worksheets

This is showing #N/A! Error, because the value of the cell B4 in the “All candidates” sheet, Alex Hales, is not there in the range B4:D9 of the sheet “Week 1″

Then drag the Fill Handle.

VLOOKUP Formula with Multiple Worksheets

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

Notice: We have used relative cell reference for the lookup_value (B4), but absolute cell reference for the table_array ($B$4:$D$9).

Because we want the lookup_value to increase one by one during dragging the Fill Handle, but the table_array to remain constant.

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. VLOOKUP Formula to Search on Multiple Worksheets with IFERROR Function

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

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 IFERROR functions 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”)))

Enter this formula in the first cell of the “All Candidates” sheet.

=IFERROR(VLOOKUP(B4,'Week 1'!$B$4:$D$9,2,FALSE),IFERROR(VLOOKUP(B4, 'Week 2'!$B$4:$D$9,2,FALSE),IFERROR(VLOOKUP(B4,'Week 3'!$B$4:$D$9,2,FALSE),"Absent")))

IFERROR and VLOOKUP Formula with Multiple Sheets

Then double click on the Fill Handle.

IFERROR and VLOOKUP Formula with Multiple Sheets

You see, we have extracted the written number of all the candidates.

Two names were not found in any worksheet, they have been marked as absent.

Now if you wish, you can change the col_inder_number from 2 to 3 to extract out the Viva marks.

=IFERROR(VLOOKUP(B4,'Week 1'!$B$4:$D$9,3,FALSE),IFERROR(VLOOKUP(B4, 'Week 2'!$B$4:$D$9,3,FALSE),IFERROR(VLOOKUP(B4,'Week 3'!$B$4:$D$9,3,FALSE),"Absent")))

IFERROR and VLOOKUP Formula with Multiple Sheets

3. VLOOKUP Formula to Search on Multiple Worksheets with INDIRECT, INDEX, MATCH and COUNTIF Functions

The nested IFERROR and VLOOKUP formula that we used earlier is helpful, but still a bit complex to use.

There is a high probability of getting confused and generating errors if there are a lot of worksheets.

Now we will generate another formula using the INDIRECT, INDEX, MATCH and COUNTIF functions that look even more complex, but 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.

Array of the Names of the Worksheets

Then insert this formula in the first cell:

=IFERROR(VLOOKUP(B4,INDIRECT("'"&INDEX($F$5:$H$5,1,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!B4:B9"),B4)>0,0))&"'!$B$4:$D$9"),2,FALSE),"Absent")

INDIRECT and VLOOKUP Function with Multiple Sheets

Then drag the Fill Handle.

INDIRECT and VLOOKUP Function with Multiple Sheets

See we have got the written marks of all the candidates.

And those whose names have not been found have been marked as absent.

Explanation of the Formula

  • COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!B4:B9"),B4) returns how many times the value in cell C4 is present in the range ‘Week 1′!B4:B9, ‘Week 2’!B4:B9 and ‘Week 3’!B4:B9 respectively.
[Here $F$5:$H$5 is the names of the worksheets. So the INDIRECT formula receives ‘Sheet_Name’!B4:B9.]

INDIRECT and COUNTIF Functions in Excel

  • Therefore COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!B4:B9"),B4)>0 returns TRUE for each range if the value in B4 is present in that range, otherwise returns FALSE for that range.

COUNTIF with INDIRECT to Find Whether Any Value Exists in a Range or Not

  • MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!B4:B9"),B4)>0,0) returns in which worksheet the value in B4 is present.

MATCH Function to Find out in Which Worksheet a Value is Present

Here it returned 3 because the value in B4 (Alex Hales) is in worksheet no 3 (Week 3).

  • INDEX($F$5:$H$5,1,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!B4:B9"),B4)>0,0)) returns the name of the worksheet where the value in cell B4 is.

(Week 3 in this example).

Name of the Worksheet Where the Value is Present

  • INDIRECT("'"&INDEX($F$5:$H$5,1,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!B4:B9"),B4)>0,0))&"'!$B$4:$D$9") returns the total range of cells of the worksheet in which the value in B4 is present

In this example, the range in Week 3.

Range of the Worksheet Where the Value is Present

  • Finally, VLOOKUP(B4,INDIRECT("'"&INDEX($F$5:$H$5,1,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!B4:B9"),B4)>0,0))&"'!$B$4:$D$9"),2,FALSE) returns the 2nd column of the row from that range where the value in cell B4 matches.

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.

You can use a similar formula to find out the Viva marks of the candidates.

Change the col_index_number from 2 to 3.

=IFERROR(VLOOKUP(B4,INDIRECT("'"&INDEX($F$5:$H$5,1,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!B4:B9"),B4)>0,0))&"'!$B$4:$D$9"),3,FALSE),"Absent")

VLOOKUP Function to Collect Data from Multiple Cells

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.

We are inserting the formulas separately in both columns.

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

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.

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

It will return 2.

If we drag it to column D, it will become COLUMNS($B$1:D1) and return 3.

And so on.

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

=IFERROR(VLOOKUP($B4,INDIRECT("'"&INDEX($F$5:$H$5,1,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$H$5&"'!B4:B9"),$B4)>0,0))&"'!$B$4:$D$9"),COLUMNS($B$1:C1),FALSE),"Absent")

Column Index Number Made to Dynamic in VLOOKUP Function

And then drag the Fill Handle to both sides.

Col_index_num made to dynamic in VLOOKUP Function

Notice: We have changed the lookup_value from B4 to $B4, so that it remains unchanged when dragging to the right, but changes when dragging down.

Limitations of the VLOOKUP Function and Some Alternatives

  • You can not use the VLOOKUP function when the lookup_value is not in the first column of the table.

For example, in the previous example, you can not use the VLOOKUP function to know the name of the candidate who got a 90 in the written exam.

You can use the IF, IFS, INDEX-MATCH, XLOOKUP or FILTER functions of Excel to solve this (Visit this article).

  • 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 (Visit this article).

Conclusion

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

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo