Index Match IF Trouble

[GreggomyEggo]

I'm working from a document that contains two tabs; starts and stops. The starts tab details an index of the month/year of when a widget started. The stops tab details the same for when the widget stopped. I'm currently using a Index Match IF formula to attempt to pull the start into the stop so that I can then calculate the time between the start and stop, though my formula isn't pulling in all of the data I need. The original data has ~12k rows in each starts and stops and contains varying numbers of starts and stops for each widget id. If my array were to work properly, the second 19 in "Current Formula Result" would return the number 34 from the start index.

[TABLE="width: 983"]
<tbody>[TR]
[TD]WidgetID[/TD]
[TD]StartIndex[/TD]
[TD][/TD]
[TD]WidgetID[/TD]
[TD]StopIndex[/TD]
[TD]Current Formula Result[/TD]
[TD]Current Formula[/TD]
[/TR]
[TR]
[TD]555555[/TD]
[TD] 19[/TD]
[TD][/TD]
[TD]555555[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]{=IFERROR(INDEX($B$2:$B$3,MATCH(D2,IF(E2>$B$2:$B$3,$A$2:$A$3),0)),"")}[/TD]
[/TR]
[TR]
[TD]555555[/TD]
[TD] 34[/TD]
[TD][/TD]
[TD]555555[/TD]
[TD]30[/TD]
[TD]19[/TD]
[TD]{=IFERROR(INDEX($B$2:$B$3,MATCH(D3,IF(E3>$B$2:$B$3,$A$2:$A$3),0)),"")}[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]555555[/TD]
[TD]51[/TD]
[TD]19[/TD]
[TD]{=IFERROR(INDEX($B$2:$B$3,MATCH(D4,IF(E4>$B$2:$B$3,$A$2:$A$3),0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

If you have the MAXIFS function, try the formula in E2. (MAXIFS is available in Excel 365). If not, try the array formula in F2.

	A	B	C	D	E	F	G
WidgetID	StartIndex	WidgetID	StopIndex	Current Formula Result

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]555555[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]555555[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]555555[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]555555[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]555555[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=MAXIFS($B:$B,$A:$A,C2,$B:$B,"<"&D2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]{=MAX(IF($A$2:$A$3=C2,IF($B$2:$B$3< D2,$B$2:$B$3<d2,$b$2:$b$3< font="">)</d2,$b$2:$b$3<>))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[GreggomyEggo]

Eric,

Thank you for helping on this and for posting the array as I missed mentioning that I am running Excel 2016.

The array you posted won't work for what I am attempting to accomplish though. This is because the Widget ID for Starts is not always contained in Stops or vise versa. Thus i need to match the Widget ID in Stops, to the Widget ID in Starts and then pull over the corresponding Start but that is less than or equal to the stop and greater than the prior start.

Let me know if I can clarify any of that.

 

[Eric W]

I'm a little puzzled. How can you look up a start time for a Widget ID if the Widget ID is not in the start table? Can you show an example?