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])
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:
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:
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:
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
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.
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