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

 

[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

PS: the formula works on a separated spread sheet and for the example i had made, but not for spread sheet i am using for my analysis.....
Once i implement the Worksheet Formulas i get the figures that are relative to 2019. In all other cases (for those companies whose most recent value is before 2019) i dont get any result (the cell is blank).

 

[Fluff]

Do you have a formula in those cells?
If so what is the formula

 

[AmandaSS]

Do you have a formula in those cells?
If so what is the formula

yes, i do have a formula in those cells, and it is the same as for the cell i get the result for 2019....
i tried to fix the problem by formatting the data (format cells/number). I also copied the all table in a new spread sheet. This all didint work so far.

PS: the formula i used is =INDEX(B4:K4,MATCH(TRUE,INDEX((B4:K4<>""),0),0))

 

[Fluff]

Sorry, I obviously wasn't very clear.
using my example in post#8, do you have formulae in the cells B2:K4?
If so what is that formula?

 

[AmandaSS]

Sorry, I obviously wasn't very clear.
using my example in post#8, do you have formulae in the cells B2:K4?
If so what is that formula?

Sorry, I hadn't understood well. No, in cells B2:K4 I don't have formula, I just have numbers

 

[Fluff]

Ok, double check that you don't have anything in the "Blank" cells.
Try selecting a blank cell & press the Delete button

 

[AmandaSS]

Ok, double check that you don't have anything in the "Blank" cells.
Try selecting a blank cell & press the Delete button

SOLVED!
in each empty cell there was a space, that's why the formula was not working....crazy!

but really thanks a lot for holding on with this thread!

 

[Fluff]

You're welcome & thanks for the feedback