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()

Question? Ask it in our Excel Forum

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Copy Data or Formatting to Multiple Worksheets in Excel
Tutorial: Quickly copy all or parts of a single worksheet - data, formatting, or both - to multiple...
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...
Vlookup Across Multiple Workbooks
Tutorial: How to use the VLOOKUP function across multiple workbooks in Excel. This will create a lin...
Guide to Creating Charts with a Macro in Excel
Tutorial: How to add, edit, and position charts in Excel using VBA. This tutorial covers what to do ...
Combine Data from Multiple Worksheets in Excel
Tutorial: The easiest way to combine and consolidate data in Excel. Simple method to combine data ...