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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi Amanda,

In cell L2 you can test following array formula

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

Hope this will help
 
Upvote 0
How about
=INDEX(B2:K2,MATCH(TRUE,(B2:K2<>""),0))

This needs to be confirmed with Ctrl Shift Enter, not just Enter
 
Upvote 0
Alternatively a non-array formula
=INDEX(B2:K2,MATCH(TRUE,INDEX((B2:K2<>""),0),0))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
In you example there wasn't any data in col 2019, but I thought that that was what you wanted.


Book1
ABCDEFGHIJKLM
1companyA 2019A 2018A 2017A 2016A 2015A 2014A 2013A 2012A 2011A 2010value for company
2X52232565252
3Y221112222
4Z121212
Data
Cell Formulas
RangeFormula
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.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top