# Thread: select the most recent value from one block of 10 columns Thanks: 0 Likes: 0

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

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

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

Thanks for the help!
Amanda

2. ## Re: select the most recent value from one block of 10 columns

Hi Amanda,

In cell L2 you can test following array formula

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

3. ## Re: select the most recent value from one block of 10 columns

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

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

4. ## Re: select the most recent value from one block of 10 columns

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

5. ## Re: select the most recent value from one block of 10 columns

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

6. ## Re: select the most recent value from one block of 10 columns

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

7. ## Re: select the most recent value from one block of 10 columns

Originally Posted by Fluff
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

8. ## Re: select the most recent value from one block of 10 columns

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

ABCDEFGHIJKLM
1companyA 2019A 2018A 2017A 2016A 2015A 2014A 2013A 2012A 2011A 2010value for company
2X52232565252
3Y221112222
4Z121212

Data

Worksheet Formulas
CellFormula
M2=INDEX(B2:K2,MATCH(TRUE,INDEX((B2:K2<>""),0),0))

Array Formulas
CellFormula
L2{=INDEX(B2:K2,MATCH(TRUE,(B2:K2<>""),0))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

9. ## Re: select the most recent value from one block of 10 columns

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

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

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•