excelwildcardvlookupexcel-match

Finding the index of a matching wildcard in excel

Given a sheet like so:

Sheet 1

Product Name
-----------------
Fancy Shoes
Plain Shoes
Comfy Slippers
Nice Loafers
Pressed Shirt
Tee Shirt
Collared Button-Up

and a sheet of wildcards:

Sheet 2

Product Wildcard   |   Product Category
---------------------------------------
*Shirt             |   Shirt
*Button-Up         |   Shirt
*Shoes             |   Shoes
*Loafers           |   Shoes
*Slippers          |   Shoes

I'm hoping to produce the following:

Product Name       |   Product Category
----------------------------------------
Fancy Shoes        |   Shoes
Plain Shoes        |   Shoes
Comfy Slippers     |   Shoes
Nice Loafers       |   Shoes
Pressed Shirt      |   Shirt
Tee Shirt          |   Shirt
Collared Button-Up |   Shirt

In other words, can I lookup a category for a product in Sheet 1 that matches a Product Wildcard in Sheet 2?

I've tried to use VLOOKUP('Sheet 1'!A2, 'Sheet 2'!A2:B6, 2, FALSE) and MATCH('Sheet 1'!A2, 'Sheet 2'!A2:A6, 0). Both give me #N/A. I suspect those functions expect the search text to be the only thing that can be wildcarded and my Product Wildcards are taken literally and not interpreted as wildcards.

I'm wondering if there is another way to do this with built-in Excel functions, or if I'm going to need to write some VBA?

Thanks in advance for help on this!

Solution

  • The below is an array formula (entered with Ctrl+Shift+Enter):

    =INDEX(Sheet2!$B$1:$B$5,MATCH(1,MATCH(Sheet2!$A$1:$A$5,Sheet1!A1,0),0))

    enter image description here

    You can use "Formulas" > "Evaluate Formula" on the cell containing the formula to see how it's working step by step.