lookup_value - Required. The value to search for in the first column (e.g., employee ID "E001", product code "P123").
table_array - Required. The table range containing data (e.g., A2:D10). First column is searched, other columns return values.
col_index_num - Required. Column number to return value from (2 for 2nd column, 3 for 3rd, etc.). Count from left of table.
range_lookup - Optional. FALSE for exact match (most common), TRUE for approximate match (grades, ranges). Defaults to TRUE.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Emp ID | Name | Department | Salary | Search ID | Department | |
| 2 | E001 | John Smith | Sales | $65,000 | E003 | ||
| 3 | E002 | Sarah Johnson | HR | $58,000 | |||
| 4 | E003 | Mike Chen | Engineering | $82,000 | |||
| 2 | E001 | John Smith | Sales | $65,000 | E003 | =VLOOKUP(F2,$A$2:$D$6,3,FALSE) Engineering |
Find "E001" in column A, return 3rd column value
Returns "Engineering" from column C
Look up value from F2, $ locks table when copying
Formula adjusts F2→F3 but table range stays fixed
Returns "Not Found" instead of #N/A error
Graceful error handling for missing values
Find closest value ≤85 for grades, tax brackets
Returns grade "B" for score 85 from range table
Use the VLOOKUP function in Excel to build employee lookup systems where you search by employee ID and automatically retrieve department, salary, or contact information. This lookup formula pattern is essential for HR managers, payroll specialists, and department heads who need instant access to employee data. The function eliminates manual searching through employee rosters, reduces data entry errors in HR reports, and enables dynamic dashboards where selecting an employee ID populates all related information automatically across multiple cells.
| A | B | C | D | F | G | |
|---|---|---|---|---|---|---|
| 1 | Emp ID | Name | Department | Salary | Search ID | Department |
| 2 | E001 | John Smith | Sales | $65,000 | E003 | |
| 3 | E002 | Sarah Johnson | HR | $58,000 | ||
| 4 | E003 | Mike Chen | Engineering | $82,000 | ||
| 5 | =VLOOKUP(F2,$A$2:$D$6,3,FALSE) Engineering |
Assign letter grades based on numeric scores using the VLOOKUP function in Excel with approximate match (TRUE). This technique works perfectly for tax brackets, commission tiers, shipping rates, and any scenario where values fall into ranges. The formula with TRUE parameter finds the largest value less than or equal to your lookup value, making it ideal for educators assigning grades, sales managers calculating tiered commissions, and finance teams applying progressive tax rates automatically.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Min Score | Grade | Student Score | Letter Grade |
| 2 | 0 | F | 85 | |
| 3 | 60 | D | ||
| 4 | 70 | C | ||
| 5 | 80 | B | ||
| 6 | 90 | A | ||
| 7 | =VLOOKUP(C2,$A$2:$B$6,2,TRUE) B |
❌ The Problem:
✅ Solution:
=VLOOKUP("E003", A2:D10, 4, FALSE)Always use FALSE for exact match lookups like employee IDs, product codes, or account numbers. The function defaults to TRUE (approximate match), but most real-world lookups need exact matching. FALSE ensures you get the exact record you want or a clear #N/A error if not found, rather than silently returning wrong data.
❌ The Problem:
✅ Solution:
=XLOOKUP("E003", B2:B10, D2:D10)VLOOKUP can only search the leftmost column of your table range. If you need to search a column and return values to its left, use <Link href="/formulas/xlookup">XLOOKUP</Link> or <Link href="/formulas/index">INDEX</Link>/<Link href="/formulas/match">MATCH</Link> instead. This function has this left-to-right limitation built in, which is why XLOOKUP is often recommended as the modern replacement.
❌ The Problem:
✅ Solution:
=VLOOKUP(F2, $A$2:$D$10, 3, FALSE)Use absolute references ($A$2:$D$10) for the table range so it stays fixed when copying the formula to other cells. The lookup value (F2) should remain relative to adjust for each row. This is the most common mistake and the easiest to fix - just add $ signs before column letters and row numbers in your table range.
Master these VLOOKUP variants:
From basics to advanced - AI generates perfect formulas instantly.