XLOOKUP with wildcard search
IntermediateIn inventory work, product names are not always entered consistently. Sometimes you only know part of a product name, but you still need to find the matching item quickly.
XLOOKUP can handle wildcard searches when you set the match_mode argument to 2. The asterisk (*) wildcard means "any sequence of characters", so a partial term can match a longer product name that contains that text.
The full XLOOKUP syntax is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])
For a wildcard search, you need to build a lookup value that includes wildcard characters around the search term, then use 2 for the match mode.
Your task
A product catalog is listed in rows 1 through 9. The partial search term is already entered in B11.
- In B13, use
XLOOKUPto return the product name in the catalog that contains the search term. - In B14, use
XLOOKUPagain to return the unit price for that same matched product. - In both formulas, build the lookup value yourself by adding an asterisk before and after the search term from B11.
- In both formulas, search within the product name column and use wildcard matching with
match_modeset to2. - When you're done, B13 should show the matching product name and B14 should show its unit price.
Need some help?
Hint 1
The search term in B11 is only part of the product name, so your lookup value should be built from B11 with `*` on both sides.
Hint 2
Both formulas should search the same lookup column: the product names in B2:B9. The difference between B13 and B14 is the return column.
Hint 3
For the most direct setup, build a lookup value from B11 with an asterisk on each side (using CONCAT or &), use B2:B9 as the lookup array, and return B2:B9 for B13 and D2:D9 for B14.
XLOOKUP with wildcard search
IntermediateIn inventory work, product names are not always entered consistently. Sometimes you only know part of a product name, but you still need to find the matching item quickly.
XLOOKUP can handle wildcard searches when you set the match_mode argument to 2. The asterisk (*) wildcard means "any sequence of characters", so a partial term can match a longer product name that contains that text.
The full XLOOKUP syntax is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])
For a wildcard search, you need to build a lookup value that includes wildcard characters around the search term, then use 2 for the match mode.
Your task
A product catalog is listed in rows 1 through 9. The partial search term is already entered in B11.
- In B13, use
XLOOKUPto return the product name in the catalog that contains the search term. - In B14, use
XLOOKUPagain to return the unit price for that same matched product. - In both formulas, build the lookup value yourself by adding an asterisk before and after the search term from B11.
- In both formulas, search within the product name column and use wildcard matching with
match_modeset to2. - When you're done, B13 should show the matching product name and B14 should show its unit price.
Need some help?
Hint 1
The search term in B11 is only part of the product name, so your lookup value should be built from B11 with `*` on both sides.
Hint 2
Both formulas should search the same lookup column: the product names in B2:B9. The difference between B13 and B14 is the return column.
Hint 3
For the most direct setup, build a lookup value from B11 with an asterisk on each side (using CONCAT or &), use B2:B9 as the lookup array, and return B2:B9 for B13 and D2:D9 for B14.