# Finding 2nd range in row

#### Dutch1956

##### Board Regular
Hi All,

hopefully somebody will be able to help me with the below;
 00:00 00:30 01:00 01:30 02:00 02:30 03:00 03:30 04:00 04:30 05:00 05:30 06:00 06:30 07:00 2 5 6 6 8 8 7 9

<TBODY>
</TBODY>

The below formula will return the value of the first non blank cell in row 2:
=INDEX(\$A\$1:\$O\$1,MATCH(TRUE,INDEX((\$A2:\$O2<>0),0),0)) Result: 01:00
The next formula returns he last non blank cell in row 2:
=LOOKUP(9.999999999E+307,\$A2:\$O2,\$A\$1:\$O\$1) Result: 06:00
I am looking for the start and end time of the first range and the start and end time of the second range.
Which formulas do I need to use to get
01:00-02:30 and 04:30-06:00.
any help is very much appreciated.

Bert.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Excel 2010
ABCDEFGHIJKLMNO
100:0000:3001:0001:3002:0002:3003:0003:3004:0004:3005:0005:3006:0006:3007:00
225668879
3
4Start101:00
5End102:30
6Start204:30
7End206:00
Sheet1
Cell Formulas
RangeFormula
B4=INDEX(\$A\$1:\$O\$1,MATCH(TRUE,INDEX((\$A2:\$O2<>0),0),0))
B5=INDEX(INDEX(\$A\$1:\$O\$1,MATCH(B4,\$A\$1:\$O\$1)):\$O\$1,MATCH(TRUE,INDEX(INDEX(\$A\$2:\$O\$2,MATCH(B4,\$A\$1:\$O\$1)):\$O2=0,0),0)-1)
B6=INDEX(INDEX(\$A\$1:\$O\$1,MATCH(B5,\$A\$1:\$O\$1)+1):\$O\$1,MATCH(TRUE,INDEX(INDEX(\$A\$2:\$O\$2,MATCH(B5,\$A\$1:\$O\$1)+1):\$O2<>0,0),0))
B7=LOOKUP(9.999999999E+307,\$A2:\$O2,\$A\$1:\$O\$1)

Excel 2010
ABCDEFGHIJKLMNO
100:0000:3001:0001:3002:0002:3003:0003:3004:0004:3005:0005:3006:0006:3007:00
225668879
3
4Start101:00
5End102:30
6Start204:30
7End206:00

</TBODY>
Sheet1

Worksheet Formulas
CellFormula
B4=INDEX(\$A\$1:\$O\$1,MATCH(TRUE,INDEX((\$A2:\$O2<>0),0),0))
B5=INDEX(INDEX(\$A\$1:\$O\$1,MATCH(B4,\$A\$1:\$O\$1)):\$O\$1,MATCH(TRUE,INDEX(INDEX(\$A\$2:\$O\$2,MATCH(B4,\$A\$1:\$O\$1)):\$O2=0,0),0)-1)
B6=INDEX(INDEX(\$A\$1:\$O\$1,MATCH(B5,\$A\$1:\$O\$1)+1):\$O\$1,MATCH(TRUE,INDEX(INDEX(\$A\$2:\$O\$2,MATCH(B5,\$A\$1:\$O\$1)+1):\$O2<>0,0),0))
B7=LOOKUP(9.999999999E+307,\$A2:\$O2,\$A\$1:\$O\$1)

</TBODY>

<TBODY>
</TBODY>

Andrew,

Than you so much for your quick response. You are amazing for helping me out again.
It works perfect, exactly what I was looking for. I never thought of doing it that way.
One last question related to this post:
What can I do when the data row 2 is a result of a formula?

Bert.

What's the formula?

What's the formula?

Hi Andrew,

the data is in a pivot table and my previous question might not be an issue when I leave the zeros out.
If it is ok then I will test this a little later tonight and get back to you.
Again, thank you so much.

Bert.

Hi Andrew,

the data is in a pivot table and my previous question might not be an issue when I leave the zeros out.
If it is ok then I will test this a little later tonight and get back to you.
Again, thank you so much.

Bert.

Hi Andrew,

your formulas work perfectly. The problem I encountered was that some cells were not blank. They had a value < 0.
Once again, I can not thank you enough and I dare to say that many MrExcel members would be lost without you.

Bert.

Replies
4
Views
282
Replies
11
Views
243
Replies
5
Views
142
Replies
0
Views
175
Replies
2
Views
265

1,196,515
Messages
6,015,658
Members
441,913
Latest member
Lhayden_69

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

### Which adblocker are you using?

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