在参考数组中使用通配符引用进行索引匹配 [英] Index Match Using Wild Card References Issue In Reference Array

问题描述

我希望标记数组B"中的所有订单项，如果数组A"中存在部分匹配项，则会分配标记.我想使返回在数组A"中找到的单元格的标志".

I am looking to flag all line items in "array B" where a flag is assigned if there is a partial match in "array A". I would like to make the "flag" that is returned the cell found in "array A".

我想知道使用通配符引用进行索引匹配是否是实现此目的的正确方法.我对Excel公式很陌生.

I am wondering whether or not Index Matching with a Wild Card reference is the correct way to accomplish this. I am pretty new to Excel formulas.

请在下面查看我已经完成的工作.

Please see below what I have already accomplished.

我已经尝试过在Stack Overflow上找到的多个方程式，但是它们似乎无法解决我的问题.请参阅以下有关我要使用的方程式.

I have already tried multiple equations found on Stack Overflow, but they do not seem to address my issue. Please see below for the equation that I am trying to use.

在此处查看我的文件的屏幕截图:

view the screenshot of my file here:

这是我尝试使用的公式=INDEX(B$1:B$9998,MATCH("*"&G2&"*",A$1:A$9999,0))

Here is the formula I am trying to use =INDEX(B$1:B$9998,MATCH("*"&G2&"*",A$1:A$9999,0))

我希望"flag"列的内容返回，但该公式返回值#N/A

I expected the contents of the "flag" column to return but instead, the equation returns value #N/A

我在下面提供了一个更简单的数据集作为示例

在此处查看更简单的数据集-仍无法发布图像-编辑以包含你可以的，谢谢！

View Simpler Data Set Here - Cant Post Images Yet - Edit to include if you can, thanks!

推荐答案

感谢您的其他说明.如果您想将变体Sku(G列)与主Sku(A列)匹配，我认为您想使用通配符，因为您的某些变体(例如BER-92-MP-002)可能与A列略有不同(例如"xxxBER-92-MP-002xxx")，这就是为什么您要查找:

Thanks for your additional explanation. If you want to match the Variation Sku (column G) with the Master Sku (column A), I assume that you want to use wildcards because some of your variations (e.g. BER-92-MP-002) might slightly differ from column A (e.g. "xxxBER-92-MP-002xxx"), that is why you wanted to look for:

"*"&"BER-92-MP-002"&"*"

假设这是正确的，那么您可以使用Array公式查找匹配发生的行号，例如E2单元格(Ctrl + Shift + Enter):

Assuming this is correct, then you can use an Array formula to look for the Row number where the match occurs, e.g. cell E2 (Ctrl+Shift+Enter):

=MAX(IFERROR(IF(FIND(G2,$A$2:$A$9),ROW($A$2:$A$9)),0))

然后可以按以下方式(F2单元格)检索您的标志:

Then your Flag can be retrieved as follows (cell F2):

=IF(E2,INDEX($B$1:$B$9,E2))

截屏显示的最终结果:

Screenshot with the final result:

我希望它能对&如果我误解了您的原始要求，我们深表歉意.如有必要，很高兴调整两个公式(您可以通过编辑原始帖子来发布其他屏幕截图).

I hope it helps & apologies if I misunderstood your original request. Happy to adjust both formulas if necessary (you can post additional screenshots by editing your original post).

已调整: 假设您的搜索字符串始终以"SKU"开头，后跟-"符号和一个附加字符串(例如SKU-BLUE)，则可以在单元格F2中使用以下公式:

Adjusted: Assuming that your search string always starts with "SKU" and is followed by "-" symbol and one additional string (e.g. SKU-BLUE), you can use the following formula in cell F2:

=IFERROR(MATCH(MID(H2,FIND("SKU-",H2),FIND("-",MID(H2,FIND("SKU-",H2),100),5)-1),$A$1:$A$5,0),0)

G列中的公式与我以前的帖子中的公式相同.最终结果:

Formulas in column G are the same as in my previous post. Final result:

这篇关于在参考数组中使用通配符引用进行索引匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！