how can I index match if the offset is unpredicatable

[Cgarcia]

I am trying to find a way to put this information from a report into an easily readable table. I want to look for the name of a person in Column A, once I find the name I need to find the next instance of "Time" in Column B. The Final Result I need comes from Column C. So on a Separate sheet I want to get the result. the problem I am running into is the number of rows offset between the person's name and the next instance of "time" can vary each time the report is produced.

this is the formula I was using, but the row offset is unpredictable

=IFERROR(INDEX('1a'!$K:$K,<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">MATCH($A2,'1a'!$A:$A,0)+3),"")

Sheet1:
A B C
1 Robert
2
3 Time 1:25:03
4 Time 0:06:48
5
6 John
7
8
9
10 Time 1:00:38
11 Time 0:13:25


Sheet2:

A B
1 Robert 1:25:03
2 John 1:00:38

 

[Tetra201]

Try

=IFERROR(VLOOKUP("Time",INDEX('1a'!$A:$A,MATCH($A2,'1a'!$A:$A,0)):INDEX('1a'!$K:$K,10000),11,0),"")

Adjust the 10000 to match your data size. The 11 corresponds to column K.

 

[Cgarcia]

Thank You, This Seems to be working perfectly