The VLOOKUP function is used to search for a value in the first column of a range and return a value in the same row from another column in the range.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: The value to search for.
- table_array: The range of cells that contains the data.
- col_index_num: The column number (in the range) from which to return the value.
- range_lookup: Optional; TRUE for approximate match or FALSE for exact match.
Example: Employee Data Lookup
Sample Data:
| ID | Name | Department | Salary |
|---|---|---|---|
| 101 | John Smith | IT | 60000 |
| 102 | Mary Johnson | HR | 55000 |
| 103 | Alex Brown | Finance | 75000 |
| 104 | Chris Lee | Marketing | 52000 |
| 105 | Sarah Wilson | IT | 58000 |
Task:
Find the Department and Salary of an employee with ID 103.
Step 1: Use VLOOKUP for Department
- Formula:
=VLOOKUP(103, A2:D6, 3, FALSE) - Explanation:
- 103: The value to look for in the first column.
- A2:D6: The table array containing the data.
- 3: The column number for the Department.
- FALSE: An exact match is required.
- Result: Finance
Step 2: Use VLOOKUP for Salary
- Formula:
=VLOOKUP(103, A2:D6, 4, FALSE) - Explanation:
- 103: The value to look for in the first column.
- A2:D6: The table array containing the data.
- 4: The column number for the Salary.
- FALSE: An exact match is required.
- Result: 75000
Key Notes:
- The lookup_value (e.g., ID) must be in the first column of the table.
- The col_index_num specifies the column to return a value from (relative to the table range).
- Use FALSE for an exact match (recommended for most use cases).
Use Case Example:
| ID | Lookup Department Formula | Lookup Salary Formula |
|---|---|---|
| 103 | =VLOOKUP(103, A2:D6, 3, FALSE) | =VLOOKUP(103, A2:D6, 4, FALSE) |
