# Index Match IF Trouble

#### GreggomyEggo

##### New Member
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>

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

Replies
2
Views
164
Replies
6
Views
35
Replies
2
Views
58
Replies
11
Views
75
Replies
20
Views
160

### Forum statistics

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