When you have to deal with massive data from multiple worksheets across your Excel workbook, the VLOOKUP function is your savior. The VLOOKUP function is one of the most useful and at the same time one of the most sophisticated functions in Excel. This function helps you to assemble your data from multiple data sheets in an organized way. Today, in this article we will discuss how to use the VLOOKUP function from another sheet in Excel.

**Read more:** VLOOKUP Formula in Excel with Multiple Sheets

**Table of Contents**hide

**Download Practice Workbook**

Download this practice sheet to practice while you are reading this article.

**VLOOKUP in Excel: Definition**

In plain language, the **VLOOKUP** function takes the user’s input, looks up for it in the Excel worksheet, and returns an equivalent value related to the same input. This function is available in all versions of Excel from Excel 2007.

**Summary**

The** VLOOKUP **function takes the input value, searches it in the worksheets, and returns the value matching the input.

**Syntax**

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

**Argument**

**Lookup_value:**The value we want to find by searching it in another worksheet (Required)**table_array:**The range of cells in another worksheet containing out input data (Required)**Col_index_num:**The specific column number in the sheet_range containing the information we want to achieve (Required)**[range_lookup]:**value is either**TRUE**or**FALSE**(optional)- if
**TRUE**, returns either an exact or approximate match. - if
**FALSE**, VLOOKUP will find an exact match.

- if

**Return Value**

Returns an exact or approximate value equivalent to the user’s input value.

**VLOOKUP from Another Sheet: 3 Approaches**

**1. ****VLOOKUP from Another Sheet in the Same Workbook**

In this type, we will learn how to use the **VLOOKUP** function when you have to retrieve data from another sheet in the same workbook.

**Step-1:**

Consider a dataset where “**ID”** number, Sales Rep’s** “Name”**, “**Region”** and “**Salary”** is given like the given one in the image. In this dataset, the ID numbers are given arbitrarily. Our task is to put the ID number in another worksheet and retrieve the information related to the ID number.

**Step-2:**

Create another worksheet where we want to get the information matching the ID number using the **VLOOKUP** function.

**Step-3:**

In the Name Column, Apply the** VLOOKUP** function.

The function looks like

Where,

**Lookup_value**is**B4****table_array:**is**‘Employee Data’!B$4:E$14 (**Click on the employee data worksheet and select the array**)**

**Col_index_num**is**2****[range_lookup]:**we want the exact match**(FALSE)**

Press **“Enter”**. We got our value from another worksheet.

**Step-4: **

Now select the cell that contained the function. Shift your cursor to the corner of the cell until you see this icon.

Now hold and drag the mouse to get the same result for the rest of the ID numbers.

**Step-5:**

Hold and drag the mouse to get the rest of the result. But first, you have to** “Unblock” **the columns from “**$B$4:$E$14”** to** “B$4:E$14”.**

**Step-6:**

Press **“Enter”**. Now we have got all our data from another worksheet.

**2. ****VLOOKUP from Multiple Sheets Using IFERROR Function**

When we have to look up for value between two or more multiple sheets, the previous method is not that comfortable. The best solution is to use the **VLOOKUP** function with nested the** IFERROR **function to lookup between worksheets one by one. We will now learn it.

**Step-1:**

Let’s assume that we have three worksheets containing employee data from** “India”**, “**Nepal”**, and the “**Japan”** region. We want to find their salary in a new worksheet using their ID number as input.

**Step-2:**

Make a new worksheet containing the ID number and Salary column. In the ID column, input the ID number that you want to look up in those sheets.

**Step-3:**

Apply the** VLOOKUP** with the **IFERROR** function.

The format for this formula is,

**=IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…), …, “Not found”))**

Now insert out data in this formula. The final look of the formula is

Where,

**Lookup_Value**is**B4****Sheet_range**is**India!$B$4:$E$9, Nepal!$B$4:$E$9, Japan!$B$4:$E$9****Col_index_num**is**4****Range_lookup**is**False**

If no value is found, the formula will return **“NOT FOUND”. **

Press** “Enter”**.We got our value.

**Step-4:**

Now apply the same function for the rest of the value.

We can see that in ID number “**1102316”**, the value is **“NOT FOUND”**. Because this **ID **number does not exist in those worksheets.

**3. ****VLOOKUP from Multiple Sheets Using INDIRECT Function**

There is another way to “**VLOOKUP”** between multiple sheets in Excel by using a combination of the** “VLOOKUP”** and the “**INDIRECT”** function.

**Step-1:**

Now we will consider four different worksheets containing employee information to apply this formula. Create a new worksheet where we will compile that information.

**Step-2:**

We named those worksheets **“India”**, **“Nepal”**, **“Japan”** and **“Africa”.** Now write all of the lookup sheets in any cell of your workbook and name that range.

**Step-3:**

Apply the **VLOOKUP** and the** INDIRECT** function. The format of this formula looks like,

**=VLOOKUP(lookup_value, INDIRECT(“‘”&INDEX(Lookup_sheets, MATCH(1, –(COUNTIF(INDIRECT(“‘” &Lookup_sheets& “‘!lookup_range”), lookup_value)>0), 0)) & “‘!table_array”), col_index_num, FALSE)**

Where,

**Lookup_value**is**$B4.****Lookup_sheets**is the named range containing the names of the sheets.**Lookup_range**is the column range to look up for (**$B$4:$B$9).****Table_array**is the data range (**$B$4:$E$9).****Col_index_num**is**“FALSE”**is for the exact match.

The final formula is,

This is an array formula. So we will press **SHIFT+CTRL+ENTER** simultaneously to apply this formula.

We got our value matching the same ID number. Now apply the same formula for the rest of the ID. If there is any non-existing ID, The return value is “**#N/A”.**

**Things to Remember**

➤ The VLOOKUP function always searches for lookup values from the leftmost top column to the right. This function **“Never”** searches for the data on the left.

➤If you enter a value less than **“1”** as the column index number, the function will return an error.

➤When you select your **“Table_Array”** you have to use the absolute cell references **($)** to block the array.

➤Since the combination of the VLOOKUP and the INDIRECT function is an **“Array formula”** you have to press **SHIFT+CTRL+ENTER **to apply the formula.

**Conclusion**

Searching for data in one or multiple worksheets and then compiling them systematically in another worksheet using the VLOOKUP function is discussed in this article. Though this function is difficult for the new users to comprehend, we tried to make it as simple as possible. Hope this article is useful for you. Share your thoughts if you have any confusion.