Excel-查找第n个匹配项 [英] Excel - find nth match

问题描述

我创建了一个喜欢跟踪物品清单的物品.

I created a like to follow the inventory on an item.

我要查找下个可用日期是什么日期?"

I'm looking to find "What's the next date of availability?"

我找到了带有索引匹配功能的答案，但问题是:

I found my answer with a index match function, but the problem is :

对于每个订单，有什么可以保证的...当下一个进货"未涵盖我的运行总额时，如何找到第二"最佳匹配(下次到货)..

For each orders, what's available to promise... when my Running Total is not covered by the next "stock Arrival" how to find the "2nd" best match (next arrival)..

也许我在想这个..

这是我的工作簿: https://drive.google.com/open?id=0BwbUB7pydqnfemQwQW9JaFoxbGs

有人是Excel专家吗?

Anyone is an Excel guru?

推荐答案

您可以检查已编辑文件.这个公式非常复杂，但是考虑到第二个

You can check edited file. This formula is very comlicated, but it takes into account that, what would be if the second

放置此数组公式，然后按 CTRL + SHIFT + ENTER 并填写:

Put this array formula and press CTRL+SHIFT+ENTER and fill down:

=IF(K2=0,INDEX(A3:E$17,MATCH("05 - arrival",A3:A$17,0),5),IF(SUM($G$2:G2)+INDEX($G$2:$G$17,SMALL(IF($G$2:$G$17>0,ROW($G$2:$G$17)),1)-1)+INDEX($G$2:$G$17,SMALL(IF($G$2:$G$17>0,ROW($G$2:$G$17)),2)-1)>0,INDEX($E$2:$E$17,SMALL(IF($G$2:$G$17>0,ROW($G$2:$G$17)),2)-1),INDEX($E$2:$E$17,SMALL(IF($G$2:$G$17>0,ROW($G$2:$G$17)),3)-1)))

希望这会有所帮助.

这篇关于Excel-查找第n个匹配项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！