# Match/Index first and last populated cell to corresponding column header (Gantt chart formulas)

#### Mrgoodkat

##### New Member
Hi All,

I am dealing with a document similar to a very large Gantt chart with Dates at the top of each column and non zero values in the grid below recording which projects are worked on.

I am looking for a formula that can read the corresponding date to the first and last entry within each row: thus producing a start and end date on each project. However, Zeros populate the 'empty' cells and this cannot be avoided as the data is copied and pasted by multiple other people, and so I am looking for two formulas that can take this into account.

 (A1) 09/09/2014 10/09/2014 11/09/2014 12/09/2014 Start Date End Date Project 1 0 x x 0 10/09/2014 11/09/2014 Project 2 x x 0 0 09/09/2014 10/09/2014 Project 3 0 0 0 x 12/09/2014 12/09/2014 Project 4 0 x x 0 10/09/2014 11/09/2014

<tbody>
</tbody>

I have been using the following formulas: (In this example for Project 1)

Start date: {=IF(ISERROR(INDEX(B\$1:E\$1,MATCH(FALSE,ISBLANK(B2:E2),0))),0,(((INDEX(B\$1:E\$1,MATCH(FALSE,ISBLANK(B2:E2),0))))))}

End Date: {=IF(ISERROR((LOOKUP(2,1/(1-ISBLANK(B2:E2)),B\$1:E\$1))),0,(LOOKUP(2,1/(1-ISBLANK(B2:E2)),B\$1:E\$1))}

This formula does work if the empty cells are actually empty i.e do not contain 0's but as said previously this cannot be avoided and so I am looking for a formula that can ignore 0 values.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Do you actually have "X" in those cells or those are used to represent actual numbers in there?

Do you need something like this

Excel 2010
ABCDEFGH
1(A1)09/09/201410/09/201411/09/201412/09/2014Start DateEnd Date
2Project 101020010/09/201411/09/2014
3Project 230400009/09/201410/09/2014
4Project 30001512/09/201412/09/2014
5Project 403317010/09/201411/09/2014
Sheet4
Cell Formulas
RangeFormula
G2{=INDEX(\$B\$1:E2,1,MATCH(1,IF(B2:E2<>0,1/(B2:E2<>0)),0))}
H2{=LOOKUP(9.99E+307,IF(B2:E2<>0,B2:E2),\$B\$1:\$E\$1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Hi Momentman,

Your spreadsheet mock up is also a much better explanation for others wanting a solution to this - and I can confirm that it works flawlessly.

You are welcome. Glad to help

Replies
3
Views
113
Replies
1
Views
403
Replies
3
Views
275
Replies
28
Views
507
Replies
12
Views
258

1,211,697
Messages
6,103,360
Members
447,861
Latest member
LllopezXC

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