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

  • Create an Excel FA Cup Draw
    1

    This article will look at how to create an FA cup draw in Excel. The Excel FA cup draw file is available for download and the functions and techniques that are used are explained. The finished spreadsheet provides...

  • Simulate a Cup Draw using Excel
    0

    Microsoft Excel can do some amazing things. I like to attempt little projects testing what Excel can do. I was asked the other day to simulate a cup draw using Excel and this is the result. This article will give you...

  • Impaction in Bearded Dragons
    10

    Impaction is when a bearded dragon’s digestive tract has become blocked. It is a very real hazard that is brought about when the beardie has either swallowed something that is too large, or something that it...


1 comment

Caroline 2 years 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