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!

回答1:

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))

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

回答2:

Based on my reading here (specifically the note at the bottom), this seems to not be possible using VLOOKUP alone. If all of your wildcards don't have spaces, you could do it like this: (not an explicit formula, just a list of algorithmic steps)

• Reverse the string, with something like this (this is an array formula, so Ctrl+Shift+Enter is required to use it)
• Use FIND to get the location of the first space in the reversed version, subtracting that from the LEN of the string gives you the amount of string before the wildcard bit.
• Use RIGHT to truncate the string to the wildcard, and use that with VLOOKUP.

回答3:

If there are only two words in each cell and the word that determines the category (ex.: Shoes, Slippers, Shirt, etc...) is always second, you can use the following INDEX/MATCH to get the category:

=INDEX('Sheet 2'!B$2:B$6,MATCH(MID('Sheet 1'!A2,FIND(" ",'Sheet 1'!A2)+1,LEN('Sheet 1'!A2)-FIND(" ",'Sheet 1'!A2)),'Sheet 2'!A$2:A$6,0))