How to use Vlookup Across Multiple Worksheets in Excel

Add to Favorites

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! Smile

Excel Function: VLOOKUP()

Similar Content on TeachExcel
Center Titles Across Multiple Cells in Excel
Tutorial: How to center a title across multiple cells in Excel in order to make good looking titles...
How to Create and Manage a Chart in Excel
Tutorial: In this tutorial I am going to introduce you to creating and managing charts in Excel. Bef...
Combine Data from Multiple Worksheets in Excel
Tutorial: The easiest way to combine and consolidate data in Excel. Simple method to combine data ...
Run a Macro when a User Does Something in the Worksheet in Excel
Tutorial: How to run a macro when a user does something in the worksheet, everything from selecting ...
Link to Cells on Other Worksheets in Excel
Tutorial: It is very easy to link one cell to another cell in Microsoft Excel. The steps needed are ...
How to Add Formatting to Cells and Data in Excel Styles, Fonts, Colors, & More
Tutorial: In this tutorial I will cover how to use the various formatting tools in Excel. The Format...