Index/Match?

MOB

Well-known Member
See grid below - 3 Columns with Date/Time, Room No (eg GF Bedroom 17) and Type of Call

In column D I need a formula that if column C contains "Reset" it will look below that row in column B for the same room number, and for the row it finds, column C must also contain "Call" - it should then calculate the difference in times in column A

Eg below - Room 20 Call was 11:19, Reset was 11:20 so it should return 1 minute in cell D4

Hope that makes sense?

TIA

Date And Time Room Number Type Of Call
25/02/2016 11:21 GF BEDROOM 17 Present
25/02/2016 11:20 GF BEDROOM 17 Priority
25/02/2016 11:20 GF BEDROOM 20 Reset
25/02/2016 11:19 GF BEDROOM 10 Reset
25/02/2016 11:19 GF BEDROOM 20 Call
25/02/2016 11:17 GF BEDROOM 10 Priority
25/02/2016 11:16 GF BEDROOM 17 Call
25/02/2016 11:16 GF BEDROOM 26 Reset
25/02/2016 11:16 GF BEDROOM 26 Present
25/02/2016 11:15 GF BEDROOM 26 Priority
25/02/2016 11:14 GF BEDROOM 10 Call
25/02/2016 11:12 GF BEDROOM 26 Call
25/02/2016 11:10 GF BEDROOM 8 Reset
25/02/2016 11:05 GF BEDROOM 8 Assistance
25/02/2016 11:03 GF BEDROOM 8 Call
25/02/2016 11:00 SYSTEM Alive Check
25/02/2016 10:39 GF BEDROOM 8 Reset
25/02/2016 10:36 GF BEDROOM 8 Call
25/02/2016 10:25 GF BEDROOM 2 Reset
25/02/2016 10:24 GF BEDROOM 2 Call
25/02/2016 10:22 GF BEDROOM 6 Reset
25/02/2016 10:21 GF BEDROOM 6 Call
25/02/2016 10:19 GF DINING ROOM Reset
25/02/2016 10:19 GF DINING ROOM Present
25/02/2016 10:19 GF DINING ROOM Call
25/02/2016 10:00 SYSTEM Alive Check
25/02/2016 09:56 GF BEDROOM 2 Reset
25/02/2016 09:54 GF BEDROOM 2 Call
25/02/2016 09:35 FF BEDROOM 55 Reset
25/02/2016 09:33 FF BEDROOM 55 Priority
25/02/2016 09:30 FF BEDROOM 55 Call
25/02/2016 09:28 GF BEDROOM 21 Reset
25/02/2016 09:28 GF BEDROOM 21 Priority
25/02/2016 09:24 GF BEDROOM 21 Call

Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Looks like...

In D2 control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````=IF(\$C2="reset",INDEX(\$A\$2:\$A\$35,MATCH(1,IF(ROW(\$A\$2:\$A\$35)>ROW(\$A2),
IF(\$B\$2:\$B\$35=\$B2,IF(\$C\$2:\$C\$35="call",1))),0))-\$A2,"")
``````
<strike></strike>

Last edited:
Perfect, thanks!

Replies
7
Views
216
Replies
11
Views
388
Replies
0
Views
246
Replies
2
Views
278
Replies
0
Views
255

1,203,094
Messages
6,053,504
Members
444,667
Latest member
KWR21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back