Master VLOOKUP with Practical Examples: Exact, Approximate, Multiple-Criteria, and Left Lookup
Once you master practical VLOOKUP examples in Excel, you can connect HR data, sales data, price lists, and grading tables with a single formula. This article includes tables, formulas, and boundary-value examples that beginners can reproduce right away, covering exact and approximate matches, multiple criteria, left lookup, and error handling. At the end, you will also find a checklist for switching to XLOOKUP.
Quick Fix: Safely Find the Right Value Today
- Use exact match only:
=VLOOKUP($A2, $F$2:$H$1000, 2, FALSE) - Lock ranges: Use absolute references with
$for lookup tables. - Convert to a table: Ctrl+T → Structured references are recommended.
- Return blanks for errors:
=IFERROR(VLOOKUP($A2, Table1, 2, FALSE), "") - Remove spaces from key values:
=VLOOKUP(TRIM($A2), Table1, 2, FALSE)
Why Is VLOOKUP Confusing?
- Exact vs. approximate: FALSE is exact; TRUE or an omitted argument is approximate (requires boundary values and sorting).
- Direction limitation: VLOOKUP works only from left to right.
- Vulnerable to inserted columns: col_index_num is a number.
- Unlocked absolute references: Ranges shift.
Practical Example 1: Exact Match (Employee ID → Name/Department)
| A (Input) | B (Name) | C (Department) |
|---|---|---|
| E001 | ||
| E002 |
Lookup Table (F:H)
| F (Employee ID) | G (Name) | H (Department) |
|---|---|---|
| E001 | KIM | Sales |
| E002 | LEE | HR |
| E003 | PARK | Finance |
'Name
=IFERROR(VLOOKUP($A2,$F$2:$H$1000,2,FALSE),"")
'Department
=IFERROR(VLOOKUP($A2,$F$2:$H$1000,3,FALSE),"")
Practical Example 2: Approximate Match (Sales Performance → Commission Rate Boundaries)
| Minimum Sales | Commission Rate |
|---|---|
| 0 | 0% |
| 10000000 | 3% |
| 30000000 | 5% |
| 60000000 | 7% |
=VLOOKUP($B2, $E$2:$F$5, 2, TRUE)- The first column of the lookup table must be sorted in ascending order.
- Clearly define an inclusive minimum-value design (boundary values).
Practical Example 3: Multiple-Criteria VLOOKUP (CHOOSE/Helper Column)
Method A – Helper Column
- Helper column:
=상품코드 & "-" & TEXT(월,"yyyymm") - Lookup:
=IFERROR(VLOOKUP($A2&"-"&TEXT($B2,"yyyymm"), $F$2:$I$1000, 4, FALSE), "")
Method B – CHOOSE
=IFERROR(
VLOOKUP($A2&"-"&TEXT($B2,"yyyymm"),
CHOOSE({1,2}, $F$2:$F$1000&"-"&TEXT($G$2:$G$1000,"yyyymm"), $I$2:$I$1000),
2, FALSE),
"")Practical Example 4: Look Up Values to the Left (Three Left-Lookup Methods)
'INDEX/MATCH
=IFERROR(INDEX($F$2:$F$1000, MATCH($A2, $G$2:$G$1000, 0)),"")
'XLOOKUP
=IFERROR(XLOOKUP($A2, $G$2:$G$1000, $F$2:$F$1000, ""), "")
'VLOOKUP+CHOOSE
=IFERROR(VLOOKUP($A2, CHOOSE({1,2}, $G$2:$G$1000, $F$2:$F$1000), 2, FALSE),"")
Migrate to XLOOKUP (When, Why, and How)
- It offers advantages for left lookup, boundary-value control, and stability when columns are inserted.
- Exact-match conversion:
=XLOOKUP($A2, Table[키], Table[이름], "") - Approximate-match conversion:
=XLOOKUP($B2, $E$2:$E$5, $F$2:$F$5, , -1)
Common Errors & Troubleshooting
| Symptom | Cause | Solution |
|---|---|---|
| #N/A | Missing key/spaces/formatting | Normalize with TRIM/VALUE; use IFERROR. |
| #REF! | Inserted/deleted columns | Switch to XLOOKUP/INDEX-MATCH. |
| Incorrect approximate match | Not sorted correctly | Sort in ascending order; use XLOOKUP(-1/1). |
| Duplicate keys | Duplicate data | Remove duplicates/use an auxiliary key. |
| Slow performance | Excessive references | Use tables and minimize required columns. |
Checklist & Best-Practice Patterns
- Use exact match (0) by default; approximate match requires sorting.
- Use absolute references for ranges and use tables.
- Improve UX with IFERROR and normalize keys.
- For multiple criteria: Helper column > CHOOSE.
- For left lookup, use XLOOKUP/INDEX-MATCH.
- Minimize lookup ranges and create templates.
- Validate before deployment with boundary-value tests.
Related Articles
- Master VLOOKUP with Multiple Criteria
- Switch to XLOOKUP: From Basics Onward
- SUMIFS Multiple-Criteria Best Practices
References: Microsoft Support – VLOOKUP, Microsoft Support – XLOOKUP