It’s 3:12 PM on a Tuesday. You’re pasting last month’s sales figures into your dashboard when Finance drops a new vendor list—147 rows, no IDs, just names and payment terms. Your VLOOKUP formula in D2 returns #N/A. You check spelling. You trim spaces. You retype the lookup value. Still broken. You copy the formula down—and suddenly row 7 pulls data from row 12. You don’t know why. And your deadline is in 87 minutes.
Quick Answer
VLOOKUP searches for a value in the first column of a table array, then returns a value from the same row in a specified column—but only if the lookup column is sorted ascending or you use FALSE for exact match. It stops at the first match, ignores duplicates, and can’t look left. If you omit the range_lookup argument, Excel defaults to TRUE—and silently gives you the wrong answer unless your data is sorted.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| VLOOKUP (exact match) | =VLOOKUP(A2,Sheet2!A:D,3,FALSE) | One-time lookups where lookup column is leftmost and unsorted | Can’t look left; breaks if column order changes; #N/A if not found |
| XLOOKUP | =XLOOKUP(A2,Sheet2!B:B,Sheet2!D:D,"Not found") | Modern workflows—works left/right, handles errors cleanly, no sorting needed | Not available in Excel 2016 or earlier; requires Microsoft 365 or Excel 2021+ |
| INDEX + MATCH | =INDEX(Sheet2!D:D,MATCH(A2,Sheet2!B:B,0)) | Legacy Excel users needing flexibility and reliability | Slightly longer formula; requires understanding two functions |
| FILTER (dynamic arrays) | =FILTER(Sheet2!A:D,Sheet2!B:B=A2,"No match") | Returning multiple matching rows (e.g., all orders for one customer) | Only works with dynamic arrays; spills across cells; not backward-compatible |
| Power Query Merge | Home > Get Data > Combine Queries > Merge Queries | Large datasets, recurring reports, or when cleaning is needed | Steeper learning curve; overkill for one-off lookups |
Method 1 Deep Dive
Let’s fix that Friday panic using VLOOKUP—properly. Open Sheet1. In A1:C6, paste this raw sales data:
| Order ID | Customer Name | Amount |
|---|---|---|
| ORD-7821 | Sarah Chen | $12,450 |
| ORD-7822 | Acme Corp | $8,920 |
| ORD-7823 | Brightline Ltd | $15,600 |
| ORD-7824 | Sarah Chen | $3,210 |
| ORD-7825 | TechNova Inc | $22,750 |
Now go to Sheet2. Enter this vendor master list in A1:E8:
| Vendor ID | Vendor Name | Payment Terms | Credit Limit | Last Updated |
|---|---|---|---|---|
| V-104 | Sarah Chen | Net 30 | $50,000 | 2024-03-15 |
| V-218 | Acme Corp | Net 45 | $125,000 | 2024-02-28 |
| V-309 | Brightline Ltd | Net 15 | $75,000 | 2024-03-05 |
| V-441 | TechNova Inc | Net 60 | $200,000 | 2024-01-19 |
| V-522 | Nexus Labs | Net 30 | $35,000 | 2024-03-10 |
You want to pull Payment Terms (column C) from Sheet2 into Sheet1, based on Customer Name. In Sheet1, cell D1, type Terms. In D2, enter:
=VLOOKUP(B2,Sheet2!B:E,3,FALSE)
Note: We used B:E, not A:E. Why? Because VLOOKUP always looks in the first column of the table array. Since Vendor Name is in column B on Sheet2, we start there. Column C (Payment Terms) is the third column in B:E—so 3 is correct.
Press Enter. You’ll see Net 30. Drag down to D6. All five values populate correctly—even though Sarah Chen appears twice in Sheet1, and her Vendor Name matches V-104 in Sheet2.
The counterintuitive tip: If you’d used TRUE instead of FALSE, Excel would have tried to find an approximate match—and returned nonsense unless Sheet2’s Vendor Name column was sorted A–Z. Try it: change FALSE to TRUE and sort Sheet2 by Vendor Name. Then change it back. You’ll see how fragile TRUE mode really is.
Also—don’t forget Alt+=. That shortcut auto-sums, but here’s what most miss: if you select a blank cell next to a column of numbers *and* a column of text (like B2:C6), pressing Alt+= inserts SUBTOTAL(109,...)—not SUM. It’s rarely useful for VLOOKUP, but knowing when shortcuts misfire saves time.
Method 2 Deep Dive
Now let’s handle the case where you need to look up by Vendor ID—but your sales sheet only has Customer Name. You can’t use VLOOKUP to search Sheet2’s Vendor ID column and return Vendor Name, because Vendor ID is column A, and Vendor Name is column B… which is to the right. VLOOKUP can’t do that.
Enter INDEX + MATCH. It’s two functions working together. INDEX grabs a value from a range. MATCH finds a position. Together, they’re bulletproof.
In Sheet1, E1, type Vendor ID. In E2, enter:
=INDEX(Sheet2!A:A,MATCH(B2,Sheet2!B:B,0))
This says: “Find B2 (Sarah Chen) in Sheet2!B:B. Return its row number. Then grab the value from Sheet2!A:A in that same row.”
Result: V-104. Drag down. It works—even with duplicates, unsorted data, or blanks. No #N/A unless the name truly doesn’t exist.
Here’s the surprise: MATCH(0,range,0) finds the first zero—but MATCH(1,range,0) doesn’t find the first 1. It finds the first non-zero value if you use 1 as lookup_value and 0 as match_type? No. That’s a myth. MATCH only finds exact matches when match_type = 0. The ‘1’ here is just a placeholder—it means “find the first occurrence of whatever’s in the first argument.” So if you typed MATCH("Sarah*",Sheet2!B:B,0), it would fail—wildcards don’t work with exact match. You’d need MATCH("Sarah*",Sheet2!B:B,1) and sorted data. Which is why wildcards + VLOOKUP are dangerous.
Pro move: Press F2 to edit any formula, then hold Ctrl and click a range reference like Sheet2!B:B. Excel jumps to that sheet and selects the column. Saves scrolling.
Cheat Sheet
| Task | Formula / Shortcut | Notes |
|---|---|---|
| Exact-match VLOOKUP | =VLOOKUP(A2,Data!A:D,2,FALSE) |
Always lock ranges: Data!$A$1:$D$100 |
| Find row number of lookup value | =MATCH(A2,Data!B:B,0) |
Returns #N/A if not found; use IFERROR to handle |
| Get value using row/column position | =INDEX(Data!C:C,MATCH(A2,Data!B:B,0)) |
Safer than VLOOKUP—no column index drift |
| Edit formula & jump to referenced sheet | F2, then Ctrl+Click range | Works on any cell reference—not just tables |
| Convert VLOOKUP to XLOOKUP (if available) | =XLOOKUP(A2,Data!B:B,Data!C:C,"Not found",0) |
Fifth argument 0 = exact match; no sorting required |
| Check for hidden spaces before VLOOKUP | =TRIM(CLEAN(A2)) |
CLEAN removes non-printing chars; TRIM removes extra spaces |
