# select the most recent value from one block of 10 columns

#### AmandaSS

##### Board Regular
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:

 company A 2019 A 2018 A 2017 A 2016 A 2015 A 2014 A 2013 A 2012 A 2011 A 2010 value for company X 52 23 256 52 Y 111 111 Z 12 12

<tbody>
</tbody>

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

##### Well-known Member
Hi Amanda,

In cell L2 you can test following array formula

Code:
``=OFFSET(A2,0,MATCH(TRUE,B2:K2<>"",0))``
Hope this will help

#### Fluff

##### MrExcel MVP, Moderator
=INDEX(B2:K2,MATCH(TRUE,(B2:K2<>""),0))

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

#### Fluff

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

#### AmandaSS

##### Board Regular
Code:
``=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

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

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

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

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### James006

##### Well-known Member
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

Last edited:

#### AmandaSS

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

Last edited: