索引匹配跨多列匹配 [英] Index Match Match across multiple columns

问题描述

我有一个索引匹配"匹配问题，但在研究中找不到答案.尽管该解决方案实际上可能不同于索引匹配匹配"公式-但我愿意尝试比当前解决方法更有效的方法.

I have an Index Match Match question that I have not been able to find the answer for in researching. Although the solution may actually might be different than an Index Match Match formula - I'm open to try something more efficient than my current workaround.

我有一个工作表，上面有我公司的数据.我们出售产品(我们将其称为零可乐)，并跟踪促销的周数以及将其出售给零售商所获得的利润.例如，零可乐的促销活动从1月的第一周开始，到3周后结束，促销活动每周可赚取$ 100的毛利.然后，我有了一个外部数据库，该数据库每周都会格式化销售数据，以告诉我每周我售出了多少个可乐零.我的内部数据有成千上万条这样的行，包含数十种产品，但是，促销活动被整合为一行，无论它是否运行了一周以上，都很难与外部数据库进行匹配.我需要针对促销的每个星期的毛利润创建一个查询.

I have one worksheet with data from my company on it. We sell a Product (let's call it Coke Zero) and we track the weeks that we put a promotion on and how much profit we make by selling it to the retailer. For example a promotion for Coke Zero starts the first week of Jan and ends 3 weeks later and we make a gross profit of $100 each week the promotion runs. I then have an external database with sales data formatted on a weekly basis to tell me how many units of Coke Zero I sold in each week. My internal data has thousands of lines like this with dozens of products, however the promotions are consolidated on one single row regardless of if it runs for more than one week, making matching up to the external database difficult. I need to create a lookup for what our Gross Profit was for each week of the promotion.

我附上了工作簿的示例图片+我尝试做的两个数据表，总结如下. 在内部数据表上，我在右侧创建了额外的列，列出了促销活动所针对的所有星期，并将它们与产品代码连接起来，以便能够每周与外部数据表中的数据进行匹配.然后，我的查询基本上逐一检查每个列，直到找到Week_Product Code的串联匹配的一个列.

I have attached an example image of the workbook + two data sheets of what I've tried to do, summarised below. On the Internal Data Sheet I've created additional columns to the right with all of the weeks listed that the promotion is on for, and concatenated them with the Product Code to be able to match week by week to the data in the External data sheet. Then my lookup basically checks every column one after another until it finds one where the concatenate of Week_Product Code concatenate matches.

我当前的解决方案在技术上可行，但考虑到多个零售商的数据可能在1万至20万行之间，我的最终公式确实很慢且麻烦.我希望找到一个更有效的公式来完成查找.

My current solution technically works but my final formula is really slow and cumbersome given the data can be anywhere from 10K-200K lines when looking at multiple retailers. I was hoping to find a more efficient formula to complete the lookup.

外部数据表"列E上的当前解决方案:

Current solution on the External Data Sheet Column E:

=IF(ISNUMBER(MATCH(D2,'Internal Data'!$E:$E,0)),INDEX('Internal Data'!$D:$D,MATCH(D2,'Internal Data'!$E:$E,0)),
IF(ISNUMBER(MATCH(D2,'Internal Data'!$F:$F,0)),INDEX('Internal Data'!$D:$D,MATCH(D2,'Internal Data'!$F:$F,0)),
IF(ISNUMBER(MATCH(D2,'Internal Data'!$G:$G,0)),INDEX('Internal Data'!$D:$D,MATCH(D2,'Internal Data'!$G:$G,0)),
"0")))

推荐答案

我在J2中使用此公式使用了SUMPRODUCT:

I got SUMPRODUCT to work using this formula in J2:

=SUMPRODUCT(--($B$2:$D$3=H2)*--($E$2:$E$3=I2)*$F$2:$F$3)

并且，您不需要那些串联的查找列:

And, you don't need those concatenated lookup columns:

好吧，那很有趣.

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