How to use Vlookup Across Multiple Worksheets in Excel

This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel.  This means that you can keep your data table on one worksheet while using Vlookup on another worksheet to return the data.

Vlookup Syntax

Here is the full Vlookup formula with its arguments listed:

The lookup_value is what you will use to search a table; the table_array is the cell-reference to the data that you want to search; the col_index_num is the number of the column from which you want to return data where the left most column in the table_array is number 1; the range_lookup controls if you want an exact match or not and can be either TRUE or FALSE.

If you need a refresher on Vlookups or simply a more in-depth explanation of its arguments, you can go here How to use the Vlookup Function in Excel.

Standard Vlookup In Use

Here is the standard Vlookup function in Excel, which references a table of data that is on the same worksheet.

Vlookup Across Worksheets

Here is the same Vlookup as the one above, except that the data table is now on Sheet2:

The only major change is that the table_array argument now has Sheet2! before the cell reference of the table that contains the data.  The data table is on Sheet2 in the workbook and the name of this sheet is what needs to go before the table reference.  If the data table was on a worksheet called Raw Data then you would need to put Raw Data! before the table reference within the Vlookup function.  And don't forget the exclamation point "!" that goes after the sheet name and before the table reference, notice "Sheet2!A2:B4" in the above function.

Easy Way to Use Vlookup Across Multiple Worksheets

It would be tedious to have to type the name of the worksheet when you want to make cross-sheet references.  Thankfully, there is an easy way to do this.

When you enter the function and you get to the table_array argument, simply click the tab of the worksheet that contains the data and select the data on that tab.  Then, BEFORE you move to another tab or do anything else, insert the comma into the function so that you can move to the next argument.  If you move to another tab before inserting the comma, the sheet reference will change to the currently visible tab try it out and you will see what I mean.

While still on the same tab, input the last two arguments, col_index_num, and range_lookup (optional).  Then hit the Enter key and you have successfully created a Vlookup function across multiple worksheets in Excel!