Find last non blank cell in a row then return column header

[Chad B]

Here's what I'm looking for. Sample file attached, rows/columns in this write may have changed.

I've created a sort of gannt chart in Excel. I'm looking to find the first and last non-blank values in row 2 and then find the corresponding value in row 1. The value from row 1 would be populated in row 2, further left of the range I'm searching for non-blank cells.

Row 1 data (start in col c) 1 1 2 2 3 4 4 5 6 6
Row 2 data (start in col c) 3 7 5 5 5 7 (2 blank cells to start under the 1's from row 1 and 2 blank cells under the 6's from row 1)

Col A should be able to return the 2 (first non blank is 3) and Col B should return 5 (last non-blank is 7).

I think I have a good formula to find the first non blank cell and return the value from row 1. I'm using:

=IF(COUNTA(C2:M2),INDEX(C$4:M$4,MATCH(TRUE,INDEX(C2:M2<>"",0),0)),"")

What I need is a formula to find the LAST non-blank and return the row 1 value (no VBA). Any help is greatly appreciated.

Thank you.

[Jonmo1]

It would help if your description matched the actual layout of the book you posted.

The numbers actually start in column F, with the formulas in B and C..

anyway, you already have a working formula for the first non blank in the row.
But I've adjusted to use consistent columns
In B6 on the TEST TEST sheet..
=IF(COUNTA(F6:BM6),INDEX(F$4:BM$4,MATCH(TRUE,INDEX(F6:BM6<>"",0),0)),"")
In C6
=LOOKUP(9.999999999E+307,F6:BM6,$F$4:$BM$4)

[Chad B]

Thanks! They worked! And sorry for the description and actual file not matching. I wasn't originally sure if I could attach a file to this forum while at the office. Sometimes, our internet rules prevent such things. I should have adjusted it Sorry about that.

Thanks again for the help!

[Jonmo1]

You're welcome.