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

Maybe I'm overthinking this..

Here is my workbook : https://drive.google.com/open?id=0BwbUB7pydqnfemQwQW9JaFoxbGs

Anyone is an Excel guru?

回答1:

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

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

Hope this will help.

回答2:

I think you're looking for the next instock date? For example, L2 shows the next arrival date is 11/25/16, but on that date you can clearly see that it will be Not Instock. So, instead you would like to tell the customer when it will be instock - see column M - which shows the date of the next instock arrival date.

To do this, I used a 'adjusting' range INDEX() and MATCH() formula: =INDEX(E2:$E$17,MATCH("Instock",I2:$I$17,0))

Notice there are no $ dollar signs around E2 and I2. This will allow the range to adjust as the formula is copied down, making sure the previous dates are not in the formula (only present and future dates - nothing from the past).