# VLOOKUP from another sheet in Excel

The **VLOOKUP formula**, short for Vertical Lookup, is a very useful function in Excel for searching a value in a given data range and returning a corresponding value from the same row. You can also use **VLOOKUP from another sheet** in Excel to retrieve data from multiple sheets and create meaningful summaries.

## VLOOKUP from another sheet Generic Formula

**=VLOOKUP(lookup_value, sheet!table_array, col_index_number, [range_lookup])**

## Explanation

Let’s take a look at the different parts of the formula before we reconstruct it using a real example. Below are the various parameters required by this function.

**lookup_value**: This is the value you want to search for in a table. It can be a:**Number**– Look for a certain number value**Text**– Look for a text value. You will need to enclose the text within quotation marks**Cell**– Refer to a cell and use its value for searching

**table_array**: A range of at least two or more columns. The**lookup_value**is searched within data contained in the**first column**, whereas at least one more column is required where data will be retrieved from.**col_index_number**: This specifies which column should the value be retrieved from. It applies to the**table_array**provided, and considers the first column (where the data is actually searched from) as Column 1. If you want to retrieve corresponding data from Column 2, you will provide**2**as the col_index_number here.**range_lookup****[Optional]**: This specifies whether you want to search for an approximate match (TRUE) or exact match (FALSE).

## Reconstructing the VLOOKUP formula: Basic example

Now that we know the parameters, let’s see how to understand a VLOOKUP formula. Consider the following example:

`=VLOOKUP(“Orange”,Sheet1!B5:C9,2,0)`

This formula says: Look for the text **Orange** in **Sheet1** from data range (column) **B5-B9**, and if a match is found, return the value stored in the **2nd** column (i.e. **Column C**). Look for an **exact match**.

Here’s another basic example:

`=VLOOKUP(B5,Sheet1!B5:C9,2,1)`

**Read as**: Look for the value stored in cell **B5** of the current sheet, and search for it in **Sheet1** from data range **B5-B9**, and return the value stored in the **2nd** column (i.e. **Column C**). Look for an **approximate match**.

Now, let’s look at a real-world example.

## Real-World Example: Product summary

Consider the following scenario: You have a list of products with details for your electronics retail store:

The **first sheet** contains product IDs, names, manufacturers and other information.

*Figure 1.1. Sample data: Product list*

The **second sheet** contains product IDs and the quantity available in your store.

*Figure 1.2. Sample data: Product inventory details*

The **third sheet** contains product IDs along with their prices.

*Figure 1.3. Sample data: Product price list*

**Note**: The common identifier in all 3 sheets is the “ID” field. We’ll use that to our advantage, and use the cells containing IDs as our search criteria.

Let’s assume we need to create a summary on the fly and collect information from all 3 sheets into a fourth sheet we will name as **Summary**. We’ll demonstrate the VLOOKUP from another sheet functionality in this fourth sheet.

*Figure 1.4. Sample data: Summary*

## Using VLOOKUP from another sheet

Let’s start using ID as search criteria, and retrieve the names of each product. We’ll use the following formula:

**=VLOOKUP(B5,Products!B5:C9,2,0)**

**Explanation**: Look for value in B5 (which is the first ID), then go to the Products sheet and search B5-B9 for that value. Bring in value stored in column C. The end result, when copied to all rows in the name column, is as follows. Pay attention to the formula bar:

*Figure 2.1. Formula Result: Retrieve names using VLOOKUP*

Similarly, we will bring in the number of stocks for each product available in the inventory, using the same IDs from column B

**=VLOOKUP(B5,Inventory!B5:C9,2,0)**

**Explanation**: Look for value in B5 and search for it in the Inventory worksheet sheet from B5-B9. Bring in the value stored in column C (which is the Quantity, as per Figure 1.2 above)

One last example, let’s now complete our summary by getting the price of each product.

**=VLOOKUP(B5,Prices!B5:C9,2,0)**

**Explanation**: Look for value in B5, search in **Prices** sheet and bring back value in column C (Price, as per Figure 1.3) The end result is as follows:

*Figure 3. End result: Consolidated summary from 3 different worksheets*

## Leave a Comment