select the most recent value from one block of 10 columns

AmandaSS

Board Regular
Joined
Jan 7, 2014
Messages
133
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
Joined
Apr 4, 2009
Messages
3,661
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
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
How about
=INDEX(B2:K2,MATCH(TRUE,(B2:K2<>""),0))

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
Alternatively a non-array formula
=INDEX(B2:K2,MATCH(TRUE,INDEX((B2:K2<>""),0),0))
 

AmandaSS

Board Regular
Joined
Jan 7, 2014
Messages
133
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
Joined
Jan 7, 2014
Messages
133
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
Joined
Jan 7, 2014
Messages
133
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
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
In you example there wasn't any data in col 2019, but I thought that that was what you wanted.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">company</td><td style=";">A 2019</td><td style=";">A 2018</td><td style=";">A 2017</td><td style=";">A 2016</td><td style=";">A 2015</td><td style=";">A 2014</td><td style=";">A 2013</td><td style=";">A 2012</td><td style=";">A 2011</td><td style=";">A 2010</td><td style="font-weight: bold;;">value for company</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">X</td><td style="text-align: right;;"></td><td style="text-align: right;;">52</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">256</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">52</td><td style="text-align: right;;">52</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Y</td><td style="text-align: right;;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">111</td><td style="text-align: right;;">22</td><td style="text-align: right;;">22</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Z</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">12</td><td style="text-align: right;;">12</td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Data</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M2</th><td style="text-align:left">=INDEX(<font color="Blue">B2:K2,MATCH(<font color="Red">TRUE,INDEX(<font color="Green">(<font color="Purple">B2:K2<>""</font>),0</font>),0</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L2</th><td style="text-align:left">{=INDEX(<font color="Blue">B2:K2,MATCH(<font color="Red">TRUE,(<font color="Green">B2:K2<>""</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,661
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
Joined
Jan 7, 2014
Messages
133
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:

Forum statistics

Threads
1,077,827
Messages
5,336,612
Members
399,092
Latest member
jbwatkins

Some videos you may like

This Week's Hot Topics

Top