Today's Goals
- Memorize what each of the four VLOOKUP arguments does
- Use FALSE for exact matching
- Understand the first-column rule
- Diagnose the #N/A and #REF! errors
Key Concepts
1. The scenario first
Two sheets: a price list (item code in column A, price in B) and a sales log that only recorded codes. The log needs prices filled in automatically. That is VLOOKUP's job: take a value, find it in the first column of a table, return content from a chosen column of the same row.
2. Four arguments, one by one
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
| Argument | Meaning | Example |
|---|---|---|
| lookup_value | what to find | code A2 |
| table_array | where to look (value must be its first column) | $F$2:$G$8 |
| col_index_num | which column to return | 2 |
| range_lookup | FALSE exact, TRUE approximate | FALSE |
3. Always FALSE at first
Most daily lookups are exact: code to code, ID to ID — use FALSE (or 0). TRUE is for band lookups such as score grades and demands a sorted first column. Beginners should stick to FALSE; it avoids half the traps.
4. IFERROR goes on last
A miss shows #N/A, which looks bad on a report. Wrap it: =IFERROR(VLOOKUP(...),"Not found"). Order matters — diagnose first, decorate last. Reaching for IFERROR first only buries real problems.
Today's Code
Price list in F2:G8, log codes in column A, B2 holds:
excel=VLOOKUP(A2,$F$2:$G$8,2,FALSE)
| Code | Result | Process |
|---|---|---|
| P003 | 12.5 | Found P003 in F, took column 2 of that row |
| P010 | #N/A | P010 is not in the list |
| P001 | 6.8 | Hit the first row |
The $ signs lock the lookup table: dragging down moves only A2 while the range stays put — last lesson's absolute referencing at work.
Exercises
- Bring employee names into an attendance sheet, matched by ID.
- The price list lives in K:L; write the formula for M2 with a locked range.
- Wrap exercise 1 in IFERROR to show "No such ID" on a miss.
Exercise Solutions
- =VLOOKUP(A2,Employees!$A$2:$B$50,2,FALSE) — build the cross-sheet reference by clicking, not typing.
- =VLOOKUP(M2,$K$2:$L$100,2,FALSE); column index 2 means column L.
- =IFERROR(VLOOKUP(A2,Employees!$A$2:$B$50,2,FALSE),"No such ID") — the whole formula goes into the first slot.
Real-World Scenario
A monthly stock-take sheet lists codes and counts only; amounts must come from the exported price list. N2: =VLOOKUP(L2,PriceList!$A$2:$C$200,3,FALSE) pulls the price (column 3); O2: =M2*N2 makes the amount; drag both down. 500 rows reconciled in three minutes instead of an afternoon. Caveat: if some codes carry leading zeros and others do not, unify formats first or the whole column turns #N/A.
Common Errors and Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| #N/A | Value absent; number-vs-text mismatch; hidden spaces | Ctrl+F in the source; Text to Columns to unify; TRIM |
| #REF! | col_index_num exceeds the table width | Return a column inside the range |
| Whole column #N/A | Lookup column is not first in the range | Re-anchor the range or move the key column left |
| Wrong results after dragging | Missing $ on table_array | Select the range and press F4 |
Extension Exercises
- Grade bands 0–59/60–79/80–100 with TRUE; keep the lookup column ascending and compare the behavior with FALSE.
- Fetch price and then name with two formulas that differ only in the third argument.
Summary
Four arguments: what, where (key in the first column), which column, FALSE for exact. Lock the table with $, let A2 travel. #N/A means presence or format trouble; #REF! means a bad column number; IFERROR is the final touch, not the first response. Next lesson: summarizing without any formula — the pivot table.
由在线工具箱(www.vba.net)整理制作