在Excel中查找匹配的通配符的索引 [英] Finding the index of a matching wildcard in excel

问题描述

给出如下表格:

Sheet 1

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

和一张通配符:

Sheet 2

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

我希望产生以下内容:

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

换句话说，我可以在工作表1中查找与工作表2中的产品通配符匹配的产品类别吗?

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

我尝试使用VLOOKUP('Sheet 1'!A2, 'Sheet 2'!A2:B6, 2, FALSE)和MATCH('Sheet 1'!A2, 'Sheet 2'!A2:A6, 0).两者都给我#N/A.我怀疑这些功能期望搜索文本是唯一可以通配的东西，而我的产品通配符是按字面意义使用的，而不是通配符.

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.

我想知道内置Excel函数是否还有另一种方法，或者是否需要编写一些VBA?

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?

在此先感谢您的帮助！

推荐答案

下面是一个数组公式(输入 Ctrl + Shift + Enter ):

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.

这篇关于在Excel中查找匹配的通配符的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！