- VLOOKUP(lookup_value, Sheet_name! range, col_index_num, [range_lookup])
- =VLOOKUP(A2, Sales! A2:E6, 2, FALSE)
- VLOOKUP(lookup_value, [workbook_name]Sheet_name! range, col_index_num, [range_lookup])
How to VLOOKUP between two Excel sheets
When conducting a VLOOKUP between two sheets, you can follow a number of steps:
1. Identify the components
When using the VLOOKUP function between sheets, there are several elements you should incorporate. You want to specify the sheet range for the data rather than including the table array as you would for one sheet. Here is what the formula might look like:
VLOOKUP(lookup_value, Sheet_name!range, col_index_num, [range_lookup])
You can define each of these components as:
2. Define your criteria between sheets
You can enter the precise criteria you hope to search for in the cell where you hope to populate data. For instance, your formula might be as follows if you want to collect information from a worksheet called “Sales” and place it on a summary tab using table data found in cells A2 through E6:
=VLOOKUP(A2, Sales!A2:E6, 2, FALSE)
This indicates that you should fill out cell A2 of the table array A2 through E6 with data from the “Sales” worksheet.
3. Perform VLOOKUP between workbooks
You can also perform this function between separate workbooks. Here, you list the title of the workbook and the sheet where you want to collect data from. Here is the formula you can use:
VLOOKUP(lookup_value, [workbook_name]Sheet_name!range, col_index_num, [range_lookup])
If you’re looking for information in another worksheet, think about saving the spreadsheet to your device so you can access the information.
4. Duplicate VLOOKUP formulas
You can quickly fill in data for other cells if you are looking for information in a similar-structured table array or worksheet. You could start by making one VLOOKUP for each sheet in the top cell. The formula will duplicate when you click that cell and drag it through additional rows in the spreadsheet, automatically changing the lookup value.
The formula will collect information for each cell in that sheet as long as you have a parallel structure between the tables. Instead of manually performing the function in each cell, you can quickly copy the formula by repeating this step in multiple columns.
Reasons to use VLOOKUP between two Excel sheets
Use the VLOOKUP function between multiple sheets for the following purposes among others:
Example of VLOOKUP between two sheets
Here is an example of a VLOOKUP between two sheets:
Each tab of the spreadsheet used by Hot Cup Coffee Shop to track monthly sales corresponds to a different month of the year. Here are the sheets with its sales data:
Sheet name: January
*Sheet name: Summary
=VLOOKUP(A2, January!A2:B5, 2, FALSE)
This populates the number $1,200 on the summary tab. To fill in the information for those fields, they now click the cell and drag it across the following three rows. Once complete, the summary table looks like this:
To collect information for the other months, they repeat this process for each month, changing the sheet name in the formula for each monthly column. *.
Tips for using VLOOKUP between two sheets
Following are some pointers for using a VLOOKUP between two sheets:
Please note that Indeed is not affiliated with any of the businesses mentioned in this article.
