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.
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.
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.
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
Although Excel’s greatest strength is its ability to analyse huge amounts of data, it is also great for creating forms. We can create formulas, apply validation rules and use techniques to make our forms user...
I love undertaking little projects in Excel, especially ones that interest me. A couple of weeks ago I set about creating an Excel Football League Table, and here it is. The league table calculates a team’s...
Thinking of buying a basset hound puppy? Wondering what equipment you need in the house ready for the arrival of your new pet. This article will identify the 10 items you cannot be without to welcome home and care for a...