Excel INDEX MATCH looking between two dates of my choosing

I'm using Excel to log a list of items (name of items in column D) and then how long that item took to produce in column G.

I'm doing an INDEX MATCH to search for the worst performing product of the week (or between two dates of my choice) by using

=MAXIFS($G:$G,$E:$E,">="&O67,$E:$E,"<"&O68)

This returns the length of time of the longest production between the two dates in cell O67 and O68. What I then want to do is return the name of the item (column D) that was the longest to make.

I've tried

=INDEX(D:F,MATCH(O69,G:G,0),1)

But this just looks at the time recorded and returns the first occasion this time has turned up in the list. It's quite a long list so a lot of the times repeat, so this wouldn't be the way to go, I just want it to reference the two dates in O67 and O68 and look between each of these for the INDEX MATCH.

I think the way to go may be an array, but I've not done those before so if this is the way to go then can you take it slowly with me so I can understand!

Thanks in advance!

2 Answers

You could try this array formula, entered not using Enter, but Ctrl+Shift+Enter:

=INDEX($D$2:$D$1000,MATCH(1,($G$2:$G$1000= (ABSOLUTE REFERENCE TO MAXIFS CELL) *($E$2:$E$1000>=$O$67) *($E$2:$E$1000<$O$68),0))

In array formulas it is better performance-wise to use references of wisely chosen ranges, not full columns. Because if you choose full columns, the array formula will be actually working on every row, even when it is not supposed to be used, making the workbook slow.

I do not have Excel 2019 to test it, and in Excel 365 array formulas can only be viewed, but not entered.

Your issue can be solved using few Helper Cells, are Start & End Date and their Count.

• Enter this Formula to get the Count of Start & End Date in cell E193.

  =SUMPRODUCT(($C$179:$C$190>=C193)*($C$179:$C$190<=D193))

• Enter this Array (CSE) formula in Cell B195, finish with Ctrl+Shift+Enter and fill down.

{=IF(ROWS(B$195:B195)>$E$193,"",INDEX(B$179:B$190,SMALL(IF(($C$179:$C$190>=$C$193)*($C$179:$C$190<=$D$193),ROW(B$179:B$190)-ROW($B$179)+1),ROWS(B$195:B195))))}

N.B.

• Count of Start and End Date avoids use of IFERROR function as well as justifies the list of Product as answer, must be equals to the Count value.

Adjust Cell references in the Formula as needed.