Introduction
VLOOKUP is an Excel function that stands for “Vertical Lookup”. This function is used to look up a specific data value in a table and return the corresponding value associated with that data value.
What is the purpose of the VLOOKUP function?
The VLOOKUP function is mainly used to quickly find a specific value in a large data table and return the associated value. This is very useful in large data analysis and reporting as it helps users process and analyze large amounts of data more easily.
VLOOKUP parameter details
The VLOOKUP function has four parameters:
- The first parameter is the value to look for. This value can be any cell reference or text string.
- The second argument is the table containing the values to look up. This is usually a range, such as A1:B10.
- The third parameter is the column number used to lookup the value. This is the number in which column to look for the value. For example, to find the first column in the range A1:B10, you need to set this parameter to 1.
- The fourth parameter is the column number used to return the associated value. This is the number of columns in the table where the value is to be returned. For example, to return the value of the second column in the range A1:B10, you need to set this parameter to 2.
VLOOKUP Example
Following are three use cases for the VLOOKUP function.
Case 1: Find the price of a specific product in the data table
Here is a data table with product prices:
| product name | price |
|---|---|
| Product A | $100 |
| Product B | $200 |
| Product C | $150 |
| Product D | $300 |
If you wanted to find the price of “Product B” in that table, you could use the following formula:
=VLOOKUP("Product B", A1:B5, 2, FALSE)This will return 200 because the price of Product B is 200.
Case 2: Find the salary of a specific employee in the data table
The following is a data table containing employee salaries:
| Employee Name | Salary |
|---|---|
| Alex | $5000 |
| Elaine | $6000 |
| Tom | $8000 |
| Frank | $7000 |
If you want to find the salary of “Tom” in this table, you can use the following formula:
=VLOOKUP("Tom", A1:B5, 2, FALSE)This will return 8000, because Tom’s salary is 8000.
Case 3: Find the customer name for a specific order in the data table
Here is a data table with order information:
| Order Number | Customer Name | Order Amount |
|---|---|---|
| 001 | Alex | $100 |
| 002 | Jack | $200 |
| 003 | Tom | $150 |
| 004 | Frank | $300 |
If you wanted to find the name of the customer whose order number was “003” in this table, you could use the following formula:
=VLOOKUP("003", A1:C5, 2, FALSE)This will return “Tom” because the customer name for order number 003 is Tom.
In the above formula, we set the value to be looked up to “003”, the range to look for is A1:C5, the column number to look up is 2 (that is, the column where the customer name is located), and the last parameter is set to FALSE to ensure We only return exact matches.
Summarize
The VLOOKUP function is a very useful function in Excel, it can help you quickly look up and return the value associated with a given value. By mastering the parameters and use cases of the VLOOKUP function, you can more easily process and analyze large amounts of data. Hope this tutorial helps you!