Lookup Functions
Advanced

The ? wildcard: exactly one character

Match a code where one position varies.

Task:

Part numbers end in a revision letter that changes over time, and the order list only knows the base number. In C2:C5 return the current price for each base number from E2:F6, matching any single revision letter after it.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of Lookup with Wildcards

0/3

The same formula in the other shapes it takes at work.

  1. Lookup with Wildcards

    Use wildcard characters in lookup functions for partial matching.

    Core
    Advanced
  2. Wildcard lookup: contains, not starts with

    Wrap the search text in asterisks to find it anywhere in the cell.

    Advanced
  3. The ? wildcard: exactly one character

    Match a code where one position varies.

    Advanced

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

6 rows × 6 columns4 cells you fill in
ABCDEF
1Base partPricePart no.Price
2PX-200PX-100C14.2
3PX-310PX-200B22.5
4PX-100PX-2000A61
5PX-450PX-310D9.8
6PX-450A33.4
What this exercise teachesMay contain the answer

* is greedy: PX-200* would happily match PX-2000A, a different part with a very different price, if it came first. ? pins the pattern to exactly one extra character, so the lookup only finds revisions of the part you meant.