Excel track · Exercise 36 of 86 Paid
VLOOKUP with a wildcard
Match a name you half know, and keep the pattern in a cell so it can change.
Advanced
Supplier lead times
Read-only data · answer in B7
In B7, look up the lead time of the first Northwind supplier in A1:C6, using the partial name in A7 with a wildcard.
| A | B | C | |
|---|---|---|---|
| 1 | Supplier | Country | Lead time (days) |
| 2 | Baltic Freight | Latvia | 12 |
| 3 | Baltic Couriers | Latvia | 4 |
| 4 | Northwind Air | Ireland | 7 |
| 5 | Northwind Rail | Ireland | 19 |
| 6 | Northwind Sea | Ireland | 26 |
| 7 | Northwind |
Reference answer
What it does
A wildcard turns the lookup value into a pattern. A7 holds Northwind, so A7&"*" asks for the first supplier whose name starts with it. Air sits in row 4, and column 3 of A1:C6 gives its lead time, 7 days.
Without the star the lookup value is the bare word Northwind, which no cell holds, so exact match stops at #N/A. Three suppliers begin with Northwind, and the first in table order wins: Air at 7 days, and Rail's 19 never comes up.
FALSE is still required. Approximate match compares text in sort order and never reads the pattern. A longer prefix such as Northwind R narrows the field when the order matters.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Worked example
On Supplier lead times, =VLOOKUP(A7&"*",A1:C6,3,FALSE) returns 7.
=VLOOKUP(A7&"*",A1:C6,3,FALSE)Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | The pattern matched no supplier, usually because the star is missing or the prefix is wrong. |
#VALUE! | The column number is a word such as lead, which cannot be counted to. |
#REF! | The column number runs past column C, the last one in A1:C6. |
At work
A purchase ledger where supplier names arrive abbreviated and only the first few letters can be trusted.