select the most recent value from one block of 10 columns

[AmandaSS]

Hi everybody,

I have one block of 10 columns. The 10 columns contain data ordered by year (from the most recent to the oldest - 2019 - 2018 .... - 2010).

Example of block of 10 columns:

[TABLE="width: 500"]
<tbody>[TR]
[TD]company
[/TD]
[TD]A 2019
[/TD]
[TD]A 2018
[/TD]
[TD]A 2017
[/TD]
[TD]A 2016
[/TD]
[TD]A 2015
[/TD]
[TD]A 2014
[/TD]
[TD]A 2013
[/TD]
[TD]A 2012
[/TD]
[TD]A 2011
[/TD]
[TD]A 2010
[/TD]
[TD]value for company
[/TD]
[/TR]
[TR]
[TD]X
[/TD]
[TD][/TD]
[TD]52
[/TD]
[TD]23
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]256
[/TD]
[TD][/TD]
[TD][/TD]
[TD]52
[/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]111
[/TD]
[TD]111
[/TD]
[/TR]
[TR]
[TD]Z
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12
[/TD]
[/TR]
</tbody>[/TABLE]

What i need is to find the most recent "value for each company" and visualized in the last column.

Thanks for the help!
Amanda

 

[James006]

Hi Amanda,

In cell L2 you can test following array formula

=OFFSET(A2,0,MATCH(TRUE,B2:K2<>"",0))

Hope this will help

 

[Fluff]

How about
=INDEX(B2:K2,MATCH(TRUE,(B2:K2<>""),0))

This needs to be confirmed with Ctrl Shift Enter, not just Enter

 

[Fluff]

Alternatively a non-array formula
=INDEX(B2:K2,MATCH(TRUE,INDEX((B2:K2<>""),0),0))

 

[AmandaSS]

=OFFSET(A2,0,MATCH(TRUE,B2:K2<>"",0))

Hi and thanks for helping!
i tried your formula, but it didn't work.
I have to find the most recent data available along the row B2 to M2 (where in B2 there is data for 2019 and in M2 the data refers to 2010).

cheers

 

[AmandaSS]

hi and thanks for helping to you too!

i tried also both formula but the first one doesn't give any result and the second one gives back the data in column 2019. It seams like it doesn't select from the row.

cheers,
Amanda

 

[AmandaSS]

Alternatively a non-array formula
=INDEX(B2:K2,MATCH(TRUE,INDEX((B2:K2<>""),0),0))

Hi Fluff,
hiand thanks for helping to you too!

i tried also both formula but the first one doesn't give any result and the second one gives back the data in column 2019. It seams like it doesn't select from the row.

cheers,
Amanda

 

[Fluff]

In you example there wasn't any data in col 2019, but I thought that that was what you wanted.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	company	A 2019	A 2018	A 2017	A 2016	A 2015	A 2014	A 2013	A 2012	A 2011	A 2010	value for company
2	X		52	23					256			52	52
3	Y	22									111	22	22
4	Z				12							12	12
Data

Cell Formulas
Range	Formula
M2	=INDEX(B2:K2,MATCH(TRUE,INDEX((B2:K2<>""),0),0))
L2	{=INDEX(B2:K2,MATCH(TRUE,(B2:K2<>""),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[James006]

Hello,

For an Array Formula ... intead of using the ' standard ' Enter key ...

you need to use simultaneously the three keys : Control Shift Enter

Hope this clarifies

 

[AmandaSS]

thanks for your feedbacks, James006 and Fluff, but i still dont understand why it does not work..

here a print screen of what i have done.

thanks,
Amanda


PS: unfortunately the web site does not allow to insert pic from a desktop directory