Index Match IF Trouble

GreggomyEggo

New Member
Joined
Jun 6, 2008
Messages
6
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.

WidgetIDStartIndexWidgetIDStopIndexCurrent Formula ResultCurrent Formula
555555 195555552{=IFERROR(INDEX($B$2:$B$3,MATCH(D2,IF(E2>$B$2:$B$3,$A$2:$A$3),0)),"")}
555555 345555553019{=IFERROR(INDEX($B$2:$B$3,MATCH(D3,IF(E3>$B$2:$B$3,$A$2:$A$3),0)),"")}
5555555119{=IFERROR(INDEX($B$2:$B$3,MATCH(D4,IF(E4>$B$2:$B$3,$A$2:$A$3),0)),"")}

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
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.

ABCDEFG
1WidgetIDStartIndexWidgetIDStopIndexCurrent Formula Result
255555519555555200
355555534555555301919
4555555513434

<tbody>
</tbody>
Sheet6

Worksheet Formulas
CellFormula
E2=MAXIFS($B:$B,$A:$A,C2,$B:$B,"<"&D2)

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
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

New Member
Joined
Jun 6, 2008
Messages
6
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,362
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top