Yes, MATCH returns the position of a value in a range. But if you think it only works with exact matches and sorted lists, you’re missing half its usefulness—and probably breaking VLOOKUP without realizing it.
The Setup
We’re helping Sarah Chen in Procurement at Acme Corp reconcile vendor invoices against their master supplier list. She has two sheets: InvoiceLog (A1:D10) and Suppliers (F1:G12). The problem? InvoiceLog uses old vendor IDs like VEN-782, but Suppliers now tracks them as ACME-VEN-782. She needs to pull the correct Payment Terms (column G) for each invoice—but can’t edit the source data.
| Invoice ID | Vendor ID | Amount | Date |
|---|---|---|---|
| INV-2024-001 | VEN-782 | $45,200 | 2024-03-15 |
| INV-2024-002 | VEN-911 | $12,650 | 2024-03-16 |
| INV-2024-003 | VEN-782 | $8,900 | 2024-03-17 |
| INV-2024-004 | VEN-334 | $32,100 | 2024-03-18 |
| INV-2024-005 | VEN-911 | $19,450 | 2024-03-19 |
| INV-2024-006 | VEN-782 | $6,200 | 2024-03-20 |
| INV-2024-007 | VEN-405 | $14,800 | 2024-03-21 |
| INV-2024-008 | VEN-334 | $27,300 | 2024-03-22 |
| INV-2024-009 | VEN-911 | $5,100 | 2024-03-23 |
| INV-2024-010 | VEN-782 | $38,750 | 2024-03-24 |
The Challenge
Sarah tried VLOOKUP(VendorID,Suppliers!F:G,2,FALSE)—but it failed every time. Why? Because Supplier IDs in column F are ACME-VEN-782, not VEN-782. She can’t change the master list. And she can’t use wildcards in VLOOKUP’s lookup_value. Her instinct was to add &"*" to the search term—but that won’t help unless she flips the logic entirely.
This is where MATCH shines—not as a standalone tool, but as the *position finder* that lets you bend lookup rules. It doesn’t care about full text matches. It cares about *where*. And once you know *where*, you can feed that number into INDEX or even OFFSET. That’s the pivot most people miss.
Walking Through It
Step 1: In cell E2 of InvoiceLog, enter:=MATCH("*"&B2&"*",Suppliers!F:F,0)
Press Ctrl+Enter (not just Enter—this avoids array-entry confusion).
This tells Excel: “Find B2’s value *anywhere inside* column F, case-insensitive, exact position.” Note the 0 at the end—that’s the *match_type*, and it means “exact match,” but with wildcards enabled because we wrapped B2 in asterisks.
Here’s what happens before and after:
Before: E2:E10 is blank
| E2 (MATCH result) | Explanation |
|---|---|
| blank | No formula yet |
| blank | Same |
After: E2:E10 filled with row numbers
| E2 | E3 | E4 | E5 | E6 | E7 | E8 | E9 | E10 |
|---|---|---|---|---|---|---|---|---|
| 3 | 7 | 3 | 1 | 7 | 3 | 11 | 1 | 7 |
Now E2=3 because VEN-782 appears inside ACME-VEN-782 on row 3 of the Suppliers sheet. E4=1 because VEN-334 matches ACME-VEN-334 on row 1.
Step 2: In F2, use INDEX to pull Payment Terms:=INDEX(Suppliers!G:G,E2)
Drag down. Done.
The Result
Final output in F2:F10:
| Invoice ID | Vendor ID | Amount | Date | Payment Terms |
|---|---|---|---|---|
| INV-2024-001 | VEN-782 | $45,200 | 2024-03-15 | Net 45 |
| INV-2024-002 | VEN-911 | $12,650 | 2024-03-16 | Net 30 |
| INV-2024-003 | VEN-782 | $8,900 | 2024-03-17 | Net 45 |
| INV-2024-004 | VEN-334 | $32,100 | 2024-03-18 | Net 60 |
| INV-2024-005 | VEN-911 | $19,450 | 2024-03-19 | Net 30 |
| INV-2024-006 | VEN-782 | $6,200 | 2024-03-20 | Net 45 |
| INV-2024-007 | VEN-405 | $14,800 | 2024-03-21 | Net 30 |
| INV-2024-008 | VEN-334 | $27,300 | 2024-03-22 | Net 60 |
| INV-2024-009 | VEN-911 | $5,100 | 2024-03-23 | Net 30 |
| INV-2024-010 | VEN-782 | $38,750 | 2024-03-24 | Net 45 |
What Could Go Wrong
Mistake #1: Using 1 or -1 for match_type without sorting
You’ll get a wrong row number—or worse, a number that looks right but points to the wrong vendor. If Suppliers!F:F isn’t sorted alphabetically, MATCH(B2,Suppliers!F:F,1) returns the last item ≤ B2, not the first match. It’s silent. No error. Just quietly wrong.
Mistake #2: Forgetting wildcards require match_type = 0
If you write MATCH("*"&B2&"*",F:F,1), Excel ignores the asterisks. Wildcards only work with 0. Always. Not 1, not -1. Zero.
Mistake #3: Using full-column references inside MATCH with large datasetsMATCH(B2,Suppliers!F:F,0) scans all 1,048,576 rows—even if only F1:F12 has data. On slow machines or shared files, this adds lag. Better: MATCH(B2,Suppliers!F1:F500,0). Or use Ctrl+Shift+↓ to select used range, then name it (Alt+N, M, define name SupplierIDs → refers to =Suppliers!$F$1:$F$12). Then use MATCH(B2,SupplierIDs,0).
Here’s how the three main approaches stack up for 10K rows:
| Method | Time for 10K rows | Accuracy | Difficulty |
|---|---|---|---|
| VLOOKUP + helper column (CONCATENATE) | 2.8 sec | High | Medium |
| XLOOKUP with wildcards | 1.1 sec | High | Low |
| MATCH + INDEX (wildcard) | 1.4 sec | High | Medium |
| Array formula with SEARCH | 4.3 sec | Medium | High |
One more thing: If you’re using Excel 365 or 2021, try this shortcut instead of MATCH+INDEX: =XLOOKUP("*"&B2&"*",Suppliers!F:F,Suppliers!G:G,,2). The ,,2 at the end enables wildcard matching natively. Alt+M, V opens the Formula Auditing toolbar—use it to trace the MATCH chain from E2 back to Suppliers!F:F in under 3 seconds.
