5 Vlookup Examples

Introduction to Vlookup Examples

The Vlookup function in Excel is a powerful tool used for looking up and retrieving data from a table based on a specific value. It allows users to search for a value in the first column of a table and return a corresponding value from another column. In this article, we will explore five practical examples of using the Vlookup function to demonstrate its versatility and usefulness in various scenarios.

Example 1: Simple Vlookup

Consider a situation where you have a list of employee IDs and their corresponding names, and you want to find the name of an employee based on their ID. You can use the Vlookup function to achieve this.

Employee ID	Name
101	John Doe
102	Jane Smith
103	Bob Johnson

To find the name of the employee with ID 102, you would use the following Vlookup formula: =VLOOKUP(102, A2:B4, 2, FALSE), where A2:B4 is the range of cells containing the employee data.

Example 2: Vlookup with Multiple Criteria

In some cases, you may need to look up data based on multiple criteria. While the Vlookup function itself does not support multiple criteria, you can use it in conjunction with other functions like INDEX and MATCH to achieve this. Suppose you have a table with employee IDs, departments, and salaries, and you want to find the salary of an employee based on their ID and department.

Employee ID	Department	Salary
101	HR	50000
102	Marketing	60000
103	IT	70000

You can use the following formula to find the salary: =INDEX(C2:C4, MATCH(1, (A2:A4=102)*(B2:B4=“Marketing”), 0)), assuming the data is in columns A, B, and C.

Example 3: Vlookup with Approximate Match

The Vlookup function allows you to perform an approximate match by setting the fourth argument to TRUE. This is useful when you need to find a value that is close to but not exactly equal to the lookup value. For instance, if you have a table with temperature readings and you want to find the closest reading to a specific temperature.

Temperature	Reading
20	100
25	120
30	140

To find the reading for a temperature closest to 26, you would use: =VLOOKUP(26, A2:B4, 2, TRUE).

Example 4: Vlookup with Error Handling

When using the Vlookup function, you may encounter errors if the lookup value is not found in the table. To handle such errors, you can use the IFERROR function to return a custom message instead of the default #N/A error.

📝 Note: Always consider error handling when working with Vlookup to ensure your formulas are robust and user-friendly.

For example, if you have a formula =VLOOKUP(102, A2:B4, 2, FALSE) and you want to return “Employee not found” if the ID is not in the table, you can use: =IFERROR(VLOOKUP(102, A2:B4, 2, FALSE), “Employee not found”).

Example 5: Vlookup Across Multiple Tables

In some scenarios, you may need to perform a Vlookup across multiple tables. This can be achieved by using the INDIRECT function to reference the tables. Suppose you have two tables, one for sales data and another for customer information, and you want to find the customer name based on a sales ID.

Sales ID	Customer ID	Sales Amount
501	101	1000
502	102	2000

And the customer table:

Customer ID	Name
101	John Doe
102	Jane Smith

You can use the following formula to find the customer name: =VLOOKUP(VLOOKUP(501, A2:C3, 2, FALSE), E2:F3, 2, FALSE), assuming the sales data is in columns A to C and the customer data is in columns E to F.

To summarize the key points, the Vlookup function is a versatile tool that can be used in various scenarios, including simple lookups, multiple criteria searches, approximate matches, error handling, and searches across multiple tables. By mastering the Vlookup function and combining it with other Excel functions, you can efficiently manage and analyze data in your spreadsheets.