How to VLOOKUP in Excel from Another Sheet

Excel's VLOOKUP function is an invaluable tool for anyone dealing with large datasets. Whether you're looking for specific product prices, VLOOKUP can save you a tremendous amount of time and effort. This guide will walk you through the basics of VLOOKUP, explain the difference between exact and approximate matches, and provide practical examples to help you master this function.

When you're managing vast amounts of data in Excel, manually searching for specific information can be daunting. VLOOKUP, which stands for Vertical Lookup, allows you to search for a value in one column and return a corresponding value from another column. This tutorial will teach you how to use VLOOKUP efficiently, differentiate between exact and approximate matches, and apply this function in real-world scenarios.

What is VLOOKUP in Excel?

The VLOOKUP function searches for a value in the first column of a table and returns a value in the same row from a specified column. It requires four parameters: 

• Lookup value - The value you want to search for.
• Table array - The range of cells that contains the data.
• Column index number - The column number in the table array from which to retrieve the value.
• Range lookup - An optional parameter to specify whether you want an exact match.

➣ Excel Code: "=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)"

Understanding Exact and Approximate Matches

The range_lookup function in LOOKUP is hard for determining how the function searches for the lookup value.

➣ Exact Match (FALSE)

When range_lookup is set to FALSE, VLOOKUP looks for an exact match. If it finds multiple matches, it returns the first one. If no exact match is found, it returns a #N/A error. This option is useful when you need precise data retrieval.

Suppose you have a table listing animal speeds, and you want to find the animal that runs exactly 50 miles per hour. Your formula would look like this: (swift)

➣ Excel Code: "=VLOOKUP(50, $A$2:$B$12, 2, FALSE)"

If Lion is listed as running 50 miles per hour, VLOOKUP will return Lion. If no animal matches 50 exactly, it will return #N/A.

➣ Approximate Match (TRUE)

When range_lookup is set to TRUE, VLOOKUP looks for an approximate match. It searches for the closest value that is less than or equal to the lookup value, provided the lookup column is sorted in ascending order.

Using the same animal speed table, if you want to find the closest speed to 80 miles per hour, your formula would be: (swift)

➣ Excel Code: "=VLOOKUP(80, $A$2:$B$12, 2, TRUE)"

If the closest speed listed is 70 for a cheetah, VLOOKUP will return Cheetah. If the lookup value is smaller than the smallest value in the column, it will return #N/A.

Practical Examples

Example 1: Exact Match VLOOKUP

Imagine you have a list of employees and their IDs. You want to find the ID of an employee named Sandra Kwon. Your dataset is organized with names in column B and IDs in column D. 

➣ Excel Code: "=VLOOKUP("Kwon", B2:D10, 3, FALSE)"

This formula searches for "Kwon" in column B and returns the corresponding ID from column D.

Example 2: Approximate Match VLOOKUP

Consider a scenario where you have a table of product prices sorted in ascending order. You want to find the price of a product that costs close to $25.

➣ Excel Code: "=VLOOKUP(25, $A$2:$B$10, 2, TRUE) "

If your table lists prices like $20, $22, and $30, VLOOKUP will return the price closest to $25 without exceeding it, which in this case might be $22.

Using VLOOKUP with Multiple Sheets

VLOOKUP can also retrieve data from different sheets within the same workbook. To do this, include the sheet name in the table array parameter.

Suppose you have employee data in Sheet1 and updated email addresses in Sheet2. You want to update the email address in Sheet1 based on the name in Sheet2.

➣ Excel Code: "=VLOOKUP(B2, Sheet2!$A$2:$C$10, 3, FALSE) "

This formula looks for the name in B2 of Sheet1, searches for it in Sheet2 within the range A2, and returns the corresponding email address from the third column.

Using VLOOKUP with Different Workbooks

To use VLOOKUP across different workbooks, specify the file name along with the sheet name and table array.

If your updated email addresses are in a different workbook named "2023_employee_emails.xlsx," your formula would look like this:

➣ Excel Code: "=VLOOKUP(B2, '[2023_employee_emails.xlsx]Sheet1'!$A$2:$C$10, 3, FALSE)"

Automating Data Retrieval with VLOOKUP

Manual data entry can be prone to errors. Using tools like Coupler.io, you can automate data imports to Excel from multiple sources, ensuring your data is always up-to-date and minimizing the need for manual input.

Also Read - How to Learn Cloud Computing Step by Step in 2024?

Conclusion

The VLOOKUP function in Excel is a powerful tool for anyone working with large datasets. By understanding how to use exact and approximate matches and applying these concepts to real-world examples, you can streamline your data management tasks and make more informed decisions. Whether you're updating employee records or tracking product prices, mastering VLOOKUP will improve your efficiency and accuracy in Excel.