Vlookup formula in excel with example between two sheets

In many cases, your data may be connected but spread out among different sheets. With the help of VLOOKUP between sheets, you can overcome this obstacle and extract corresponding data from different sheets.

This gives you the freedom to have data in multiple sheets and not face restrictions in using them.

Before we move forward, let’s understand how to use VLOOKUP between sheets:

• VLOOKUP Syntax
• VLOOKUP from another sheet
• VLOOKUP from another workbook

Look at each of these topics one-by-one!

VLOOKUP Syntax

What does it do?

Searches for a value in the first column of a table array and returns a value in the same row from another sheet´s column (to the right) in the table array.

Formula breakdown:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

What it means:

=VLOOKUP(this value in Sheet1, in this list in Sheet2, and get me value in this column in Sheet2, Exact Match/FALSE/0])

Sometimes you are faced with a situation where you have a list of data and you want to bring in complementary data from a different sheet within the same workbook.

Let’s say that you have a list of “items” in a table within Sheet1 and you want to bring in their corresponding “item id’s” from Sheet2.

You can manually copy and paste the item id’s from Sheet2 to Sheet1 but that would take too long and you are also prone to errors.

The quick and error-proof way is by using the VLOOKUP function.

So, let’s dive in and learn how to use VLOOKUP between sheets!

VLOOKUP from another sheet

Watch it on YouTube and give it a thumbs-up!

Follow the step-by-step tutorial on how to VLOOKUP for multiple sheets with example and download this Excel workbook to practice along:

DOWNLOAD WORKBOOK

STEP 1: We enter the VLOOKUP function in the blank cell where we need to extract the data

=VLOOKUP(

STEP 2: Enter the first argument for VLOOKUP – Lookup_value

What is the value that you want to look for?

In our example, it is Television, so select that in the “Items” column

=VLOOKUP(B14,

STEP 3: Enter the second argument for VLOOKUP – Table_array

What is the table or range that contains your data?

Select the Stock inventory table in the stock list sheet so that the VLOOKUP formula will search there

=VLOOKUP(B14,‘STOCK LIST’!D9:E23,

Ensure that you press F4 so that you can lock the table range.

=VLOOKUP(B14,‘STOCK LIST’!$D$9:$E$23,

STEP 4: Enter the third argument for VLOOKUP – Col_index_num

What is the column that you want to retrieve the value from?

Since we want to retrieve the item id we will write 2, as the item id is the second column in the table array that we selected

=VLOOKUP(B14,’STOCK LIST’!D9:G23,2

STEP 4: Enter the fourth argument for VLOOKUP – Range_lookup

What kind of matching do you need?

We want an exact match of the Television text so make sure FALSE OR 0 is selected.

=VLOOKUP(B14,’STOCK LIST’!D9:G23,2,0)

Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

You now have all of the results!

In this example, you have learned how to pull data from another worksheet in the same workbook using VLOOKUP from multiple sheets. Excel also allows you to get data from another workbook from your computer.

Let’s see how that is done!

VLOOKUP from another workbook

Suppose you have monthly sales data in one workbook and you have the employee name and data in another workbook.

You have to link these two workbooks using VLOOKUP to extract values for a better and cohesive database.

In this tutorial, we will show you how you can use VLOOKUP across different Excel workbooks and link and extract data easily.

Step 1: We enter the VLOOKUP function in the blank cell where we need to extract the data

This is the cell where we want the corresponding data. In our case, this is the department of the employee in cell C15.

=VLOOKUP(

Step 2: Enter the first argument of the VLOOKUP function – Lookup_value

What is the value that you want to look for?

It is the value to be found in the first column of the table. It can be a value, text string, or reference.

In our example, it is Mark, so select that in the “Employees” column

=VLOOKUP(B15,

Step 3: Enter the second argument of the VLOOKUP function – Table_array

What is the table or range that contains your data?

It is a table of logical values, texts, numbers from which the data is retrieved

Here you need to go to the workbook where your employees’ data is stored so that we can get the name of the department they are associated with.

Select the data table containing the names and departments of the employees.

Ensure that you press F4 so that you can lock the table range. You will be sure the range is locked when $ signs appear.

=VLOOKUP(B15,‘[Employee Data.xlsx]employee list’!$D$9:$G$23

Notice how the name of the different workbooks appears between square brackets. This is the indication that the reference is from another workbook.

So if you are typing out the formula make sure to include the square brackets.

Step 4: Enter the third argument of the VLOOKUP function – Col_index_num

What is the column that you want to retrieve the value from?

It is the column number from which the matching value should be returned.

Since we want to retrieve the Department name we will write 2, as the department name is the second column in the table array that we selected.

Similarly, if we wanted to extract the employee code, we would type 3 here as employee code is in the third column of the table that we selected.

=VLOOKUP(B15,'[Employee Data.xlsx]employee list’!$D$9:$G$23,2

Step 5: Enter the fourth argument of the VLOOKUP function – Range_lookup

What kind of matching do you need?

To find a perfect match, we need to select FALSE here.

In case the data is arranged in ascending order and belongs to a certain range/period, then TRUE should be selected to get the closest match.

We want an exact match of Mark text so make sure FALSE or 0 is selected.

=VLOOKUP(B15,'[Employee Data.xlsx]employee list’!$D$9:$G$23,2,0)

Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

This is how you can extract values using VLOOKUP in multiple sheets or multiple workbooks!

Click here to learn more application of this versatile function – VLOOKUP!!

HELPFUL RESOURCE:

How to merge two sheets by using VLOOKUP in Excel?

How to use the VLOOKUP function in two tables Excel?

How do you do a VLOOKUP?

How to match data in two Excel worksheets?

How do I do a VLOOKUP between two Excel spreadsheets?

Can VLOOKUP work between sheets?