Bring header value in cell for first non-blank cell in row

[sunrise06]

I would like to find a way to complete the last column using a formula. The results should match year started column.

Any suggestions?

Book1
	A	B	C	D	E	F	G	H	I
1	Name	2014	2015	2016	2017	2018	Grand Total	Year Started	Year Started using formula
2	Name 1			18			18	2016
3	Name 2				12	15	27	2017
4	Name 3			29	42		71	2016
5	Name 4					29	29	2018
6	Name 5		12		12			2015
7	Name 6	41	20		10			2014
Sheet1

 

[rpaulson]

in H2

=INDEX($B$1:F$1,MATCH(FALSE,ISBLANK(B2:F2),0))

use array to enter - CTRL-SHIFT-ENTER

fill down as needed

hth,

ROss

 

[sunrise06]

Awesome, thank you so much!

 

[sunrise06]

Are you able to explain for me how this formula knows to grab the top row cell value?

 

[rpaulson]

the top row if defined by this part: INDEX($B$1:F$1)

this is why it has the absolute reference with the $ signs

 

[sunrise06]

Thanks

 

[sunrise06]

I am wondering if you could also provide a formula that would show Year End? Thanks

 

[Peter_SSs]

In relation to the original question, in case you, or another reader, might have the year values in that table populated by a formula I have provided an alternative formula below in H2.
The formula that has been suggested already in this thread would return incorrect results if those values are formula-driven whereas this formula will work with both formulas and constants.
In addition, if you don't like the Ctrl+Shift+Enter requirement, that can be eliminated with a small change to that H2 formula
=INDEX($B$1:F$1,MATCH(TRUE,INDEX(B2:F2<>"",0),0))

I am wondering if you could also provide a formula that would show Year End? Thanks

Is what I have in column I what you mean?

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Name	2014	2015	2016	2017	2018	Grand Total	Year Started	Year Ended
2	Name 1			18			18	2016	2016
3	Name 2				12	15	27	2017	2018
4	Name 3			29	42		71	2016	2017
5	Name 4					29	29	2018	2018
6	Name 5		12		12			2015	2017
7	Name 6	41	20		10			2014	2017
Lookup Heading

 

[sunrise06]

Perfect, thank you! Can you explain your I2 formula?

 

[Peter_SSs]

Can you explain your I2 formula?

9.99E307 is a very big number. When LOOKUP cannot find that number in B2:F2 it uses the last number in that range (18 in cell D2) and then goes to the range B$1:F$1 in the same position - so gets 2016 in that case.