How to Use Vlookup across Multiple Sheets

The Vlookup function in Excel is traditionally used to look for and return a value in a table on one worksheet. However Vlookup can be used to look up and return a value across multiple sheets in Excel.

Say you want to find a value on Sheet1, but if it cannot be found then look for it on Sheet2, and if it still cannot be found then look on Sheet3 and so on. The method of doing this will differ depending on the version of Excel you are using.

This article will look at returning somebody’s name by looking across 3 Excel worksheets. The formula will be entered on the summary sheet and aims to find the person with the number 108.

Download the spreadsheet used in this article.

Vlookup across Multiple Sheets in Excel 2007

Excel 2007 saw the release of the IFERROR function and this will be used to run Vlookup across more than one sheet.

The formula below has been entered into cell B3 on the Summary sheet.

=IFERROR(VLOOKUP(A3,Region1!A1:B4,2,FALSE),IFERROR(VLOOKUP(summary!A3,Region2!A1:B4,2,FALSE),IFERROR(VLOOKUP(summary!A3,Region3!A1:B4,2,FALSE),"Not found")))

This formula checks the table on the Region1 sheet and if an error is found, checks the table on the Region2 sheet and then Region3. If the number cannot be found then the text Not found is displayed.

Vlookup across Multiple Sheets Using Excel 2003

The IFERROR function is not available in Excel 2003. The ISNA and IF functions have been used to run the Vlookup across different worksheets in this version.

The formula below has been entered in cell B2 on the Summary sheet.

=IF(ISNA(VLOOKUP(A2,Region1!A1:B4,2,FALSE)),IF(ISNA(VLOOKUP(summary!A2,Region2!A1:B4,2,FALSE)),IF(ISNA(VLOOKUP(summary!A2,Region3!A1:B4,2,FALSE)),"",VLOOKUP(A2,Region3!A1:B4,2,FALSE)),VLOOKUP(summary!A2,Region2!A1:B4,2,FALSE)),VLOOKUP(summary!A2,Region1!A1:B4,2,FALSE))

This formula is longer and looks quite daunting but only uses three functions.

The ISNA function is used to check if the Vlookup function returned a #N/A error message or not. If it did then the IF function is used to run the Vlookup on the next sheet. If a #N/A message is still returned then the Vlookup is run on the next sheet. If no value is found then the text Not found is entered into the cell.

More by this Author


1 comment

Caroline 22 months ago

As Charlie Sheen says, this article is "WGNNINI!"

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working