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.

WidgetID	StartIndex		WidgetID	StopIndex	Current Formula Result	Current Formula
555555	19		555555	2		{=IFERROR(INDEX($B$2:$B$3,MATCH(D2,IF(E2>$B$2:$B$3,$A$2:$A$3),0)),"")}
555555	34		555555	30	19	{=IFERROR(INDEX($B$2:$B$3,MATCH(D3,IF(E3>$B$2:$B$3,$A$2:$A$3),0)),"")}
			555555	51	19	{=IFERROR(INDEX($B$2:$B$3,MATCH(D4,IF(E4>$B$2:$B$3,$A$2:$A$3),0)),"")}

<tbody>
</tbody>

 

[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
1	WidgetID	StartIndex	WidgetID	StopIndex	Current Formula Result
2	555555	19	555555	2	0	0
3	555555	34	555555	30	19	19
4			555555	51	34	34

<tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula E2 =MAXIFS($B:$B,$A:$A,C2,$B:$B,"<"&D2) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula F2 {=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<>))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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?