Complete VLOOKUP Guide (Exact Match, Approximate Match, Error Fixes, and Practical Examples)
The VLOOKUP function is one of Excel’s primary lookup functions, allowing you to search vertically in a table and instantly return the information you need. This guide covers the VLOOKUP function from the basics to the performance benefits of approximate matches, common error fixes, and INDEX/MATCH and XLOOKUP alternatives.
Need multiple criteria? Continue here: Go to Multiple-Criteria VLOOKUP
Quick Fix: Master VLOOKUP in 3 Minutes (Exact Match)
- Set the table range: Clearly select the table_array, such as A1:D100 (the lookup key must be in the first column).
- Enter the lookup value: For example, enter an employee ID in F2.
- Enter the formula:
=VLOOKUP(F2, $A$2:$D$100, 3, FALSE) - Use absolute references: Lock the range with
$before copying the formula. - If an error occurs: Check the lookup key format (number vs. text) → clean it up with Data > Text to Columns.
VLOOKUP Syntax and How It Works
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_value: The value to find
- table_array: The table range where the first column contains the lookup key
- col_index_num: The column number to return (1, 2, 3, and so on from the left)
- range_lookup: FALSE = exact match; TRUE = approximate match
Exact Match (FALSE) vs. Approximate Match (TRUE)
- FALSE: No sorting required; uses a linear search (slower with large datasets)
- TRUE: Requires ascending sort order; faster with binary search
Practical Examples: Reproducible Samples
Example 1) Department by Employee ID (Exact Match)
| EmpID | Name | Dept |
|---|---|---|
| 1001 | J.Kim | Sales |
| 1002 | A.Lee | HR |
| 1003 | M.Park | Finance |
| 1004 | S.Choi | Sales |
| 1005 | E.Han | IT |
| 1006 | R.Lim | IT |
=VLOOKUP(F2, $A$2:$C$8, 3, FALSE) → If F2 = 1005, the result is IT.
Example 2) Price by Part Code (Approximate Match for Faster Lookups)
=VLOOKUP(H2, $B$2:$C$1000, 2, TRUE) (the code column must be sorted in ascending order)
Example 3) Two-Way Lookup (VLOOKUP + MATCH)
=VLOOKUP(H4, $B$5:$E$16, MATCH(H5, $B$4:$E$4, 0), 0)Example 4) Wildcard Lookup
=VLOOKUP("JK*", $A$2:$C$8, 2, FALSE)
Common Errors and Fixes
| Issue | Cause | Solution |
|---|---|---|
| #N/A | Value not found or format mismatch | Standardize formats; use Text to Columns, TRIM, or VALUE |
| #REF! | col_index_num exceeds the range | Change the column number so it is within the range |
| #VALUE! | Incorrect argument format | Check integer and range references |
| Incorrect value (approximate match) | Improper sorting | Maintain ascending sort order |
Limitations and Alternatives: Left Lookup, Last Value, and Performance
Left Lookup
With VLOOKUP, the return column must be to the right. Use INDEX/MATCH or XLOOKUP instead.
=XLOOKUP(H2, C:C, B:B, "Not found")Last Match
=INDEX($B$1:$B$20, MAX(IF($A$1:$A$20=H2, ROW($A$1:$A$20)-ROW($A$1)+1)))Performance Optimization
- Exact match (FALSE) uses a linear search → slower with large datasets
- Approximate match (TRUE) uses sorting and binary search → faster with large datasets
- XLOOKUP/XMATCH offer flexible control over search and match modes
Checklist and Notes
- The key must be in the first column of the table
- Wildcards can be used with exact matches
- Approximate matches require sorting
- Do not exceed the column number range
- For left or last-match lookups, use INDEX/MATCH or XLOOKUP
Related Articles
- XLOOKUP vs. VLOOKUP: Which Should You Use?
- Summarize Multiple Criteria with SUMIFS
- Fix All Excel Errors in One Place
- The Complete TEXTSPLIT Guide
Some definitions and syntax in this article refer to Microsoft and Exceljet resources.
External references: Microsoft VLOOKUP, Exceljet VLOOKUP