Lookup FunctionsAdvanced

Lookup with Wildcards

Use wildcard characters in lookup functions for partial matching.

Task:

You need to find product information based on partial names. Find products containing specific text using wildcards to locate the product name and price in columns B and C.

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.

    CoreAdvanced
  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.

9 rows × 3 columns4 cells you fill in
ABC
1ProductCategoryPrice
2Gaming LaptopElectronics1299
3Office ChairFurniture199
4Gaming MouseElectronics79
5Standing DeskFurniture399
6
7Search TermFound ProductPrice
8*Gaming*
9*Chair*
What this exercise teachesMay contain the answer

Wildcards allow for flexible pattern matching in lookup functions. The asterisk (*) matches any sequence of characters, while the question mark (?) matches exactly one character.