.jpeg)
VLOOKUP is an Excel function used to find a specific value in a table and return related information from the same row. The letter "V" stands for vertical because the function searches for a value in the first column of a selected table.
For example, if you have an employee list containing employee IDs, names, departments, and salaries, you can use VLOOKUP to find an employee's name or salary by entering their ID. Excel searches the table and returns the matching information automatically, saving time and reducing manual errors.
This article explains how to use VLOOKUP in Excel, understand its syntax, return values from different columns, handle errors, and find approximate matches with practical examples.
VLOOKUP Syntax in Excel
The VLOOKUP function follows this syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Each argument tells Excel what to search for, where to search, which column to return, and whether to find an exact or approximate match.
| Argument | Definition |
|---|---|
| lookup_value | The value you want Excel to find, such as an employee ID or product name. |
| table_array | The range of cells containing the lookup value and the information you want to retrieve. The lookup value must be in the first column of this range. |
| col_index_num | The position of the column from which Excel returns the result. The first column of the selected range is numbered 1. |
| [range_lookup] | Specifies the type of match. Use FALSE for an exact match and TRUE for an approximate match. If omitted, Excel uses TRUE. |
For most searches involving employee IDs, product codes, or other specific values, use FALSE to find an exact match.
Sample Data for VLOOKUP
Suppose you maintain an employee table in Excel containing employee IDs, names, departments, and salaries. The table is stored in cells A1.
| Employee ID | Name | Department | Salary |
|---|---|---|---|
| 101 | Amit | IT | 30000 |
| 102 | Neha | HR | 35000 |
| 103 | Rahul | IT | 32000 |
| 104 | Priya | Sales | 38000 |
In this example, enter the employee ID you want to search for in cell F2. Excel will use that value to find the corresponding employee information.
Example 1: Find an Employee Name Using VLOOKUP
Suppose you want to find the name of the employee whose ID is 103. Enter 103 in cell F2 and type the following formula in cell G2:
=VLOOKUP(F2,A2:D5,2,FALSE)
Output
| Cell | Value |
|---|---|
| F2 | 103 |
| G2 | Rahul |
Explanation
The formula searches for the value in F2 within the first column of the range A2. It finds employee ID 103 in the row containing Rahul's details.
The number 2 tells Excel to return the value from the second column of the selected range, which is the Name column. The final argument, FALSE, ensures that Excel searches for an exact match.
As a result, the formula returns Rahul in cell G2.
Example 2: Find an Employee's Salary Using VLOOKUP
You can use the same lookup value to retrieve information from another column. To find Rahul's salary, change the column index number from 2 to 4.
=VLOOKUP(F2,A2:D5,4,FALSE)
Output
| Cell | Value |
|---|---|
| F2 | 103 |
| G2 | 32000 |
Explanation
The selected table contains four columns: Employee ID, Name, Department, and Salary. Since Salary is the fourth column, the formula uses 4 as the column index number. Excel finds employee ID 103 and returns the salary from the same row, which is 32000. You can change the column index number to retrieve other information from the table:
| Column Index Number | Returned Information |
|---|---|
| 1 | Employee ID |
| 2 | Name |
| 3 | Department |
| 4 | Salary |
The column number is counted from the beginning of the selected range, not from the worksheet's column letters.
Example 3: Display a Message When a Value Is Not Found
VLOOKUP returns the #N/A error when it cannot find an exact match for the lookup value. For example, if cell F2 contains employee ID 105, the function cannot find that ID in the sample table.
Use this formula:
=VLOOKUP(F2,A2:D5,2,FALSE)
Output
#N/A
Use IFERROR() to Handle the Error
You can combine VLOOKUP with the IFERROR() function to display a readable message instead of an error.
=IFERROR(VLOOKUP(F2,A2:D5,2,FALSE),"Not found")
Output:
Not found
Explanation
The IFERROR() function checks the result of the VLOOKUP formula. If the formula returns an error, Excel displays the message Not found instead. This makes worksheets easier to understand, especially when users search for values that may not exist in the table. However, IFERROR() handles all errors, not just #N/A. It can also hide problems such as an invalid column index. Therefore, check the formula carefully when troubleshooting unexpected results.
Example 4: Use VLOOKUP for an Approximate Match
VLOOKUP can also find a value that falls within a range. This is useful when assigning grades based on marks, identifying commission rates, or checking values against predefined ranges.
Suppose a grading table is stored in cells A2.
| Minimum Marks | Grade |
|---|---|
| 0 | F |
| 40 | C |
| 60 | B |
| 80 | A |
If a student scores 72 marks, you can use the following formula to find the corresponding grade:
=VLOOKUP(72,$A$2:$B$5,2,TRUE)
Output
B
Explanation
The last argument, TRUE, tells Excel to use an approximate match. Excel finds the largest value in the first column that is less than or equal to 72. In this table, that value is 60, which corresponds to grade B. Therefore, the formula returns B. For an approximate match, the first column should be sorted in ascending order. If the data is not sorted correctly, Excel may return an incorrect result.
Example 5: Use VLOOKUP to Search Another Worksheet
You do not have to keep the lookup table on the same worksheet as the formula. VLOOKUP can also retrieve information from another worksheet in the same workbook. Suppose the employee table is stored on a worksheet named Employees, and you want to return the department for the employee ID entered in cell A2 of your current worksheet.
Use this formula:
=VLOOKUP(A2,Employees!$A$2:$D$100,3,FALSE)
Explanation
The formula searches for the employee ID in cell A2 in the first column of the Employees worksheet and returns the department from the third column. The ! separates the worksheet name from the cell range, while $ keeps the range fixed when copying the formula. If the worksheet name contains spaces, enclose it in single quotation marks.
You can learn more about cell references and Excel formulas in Excel Interview Questions.
Common VLOOKUP Errors and How to Fix Them
VLOOKUP may return an error or an unexpected result when the lookup value, table range, or formula arguments are incorrect. The following table explains common problems and how to resolve them.
| Error or Problem | Possible Cause | Solution |
|---|---|---|
| #N/A | The lookup value does not exist in the first column of the selected range. | Check the lookup value and make sure the correct table range is selected. |
| #N/A even when values look identical | One value is stored as text while the other is stored as a number, or extra spaces are present. | Check the data types and remove unnecessary spaces where appropriate. |
| #REF! | The column index number is greater than the number of columns in the selected table. | Use a valid column index number within the table range. |
| Incorrect result | Approximate matching is being used when an exact match is required, or the approximate-match table is not sorted correctly. | Use FALSE for exact matches and sort the first column when using TRUE. |
| Wrong result after copying | The table range changes as the formula is copied to other cells. | Lock the table range using absolute references, such as $A$2:$D$5. |
Checking these points can help you correct most VLOOKUP problems without rebuilding the entire formula.
Limitations of VLOOKUP
VLOOKUP is useful for finding information in a table, but it has a few limitations.
- It searches only from left to right: The lookup value must be in the first column of the selected range. VLOOKUP cannot directly return a value from a column to the left of the lookup column.
- It uses a column index number: You must specify the position of the column from which the result should be returned. Changes to the table structure can affect the result.
- It returns one result per formula: A standard VLOOKUP formula returns one matching value, not every row that matches the lookup value.