Excel formula

Copper Contributor

Hi 

I wonder if someone could help me.

 

I have an excel sheet with a list of 205 countries and data on Education from 2000 to 2017. I would like a formula that would copy the latest year on column T and the correspondent percentage on Column U as the example below.

 

Column A              B             C  ....            M            N                S                     T                             U

Country               2000        2001             2011        2012  .......    2017                YEAR           % Gov Expenditure 

 

Country A            10.7         11.8               10.6         9.9             10.3                 2017                      10.3

Country B             9.8                                 9.1                                                   2011                        9.1  

 

Country C            14.2          13.9                                                                        2001                       13.9

 

Many thanks in advance

2 Replies

Hi @CRII3 

 

You can use below formula in column T:

=INDEX($B$1:$S$1,1,MATCH(LOOKUP(2,1/(B2:S2<>""),B2:S2),B2:S2,0))

 

and below formula in column U

=LOOKUP(2,1/(B2:S2<>""),B2:S2)

 

Sample file is also attached for your reference.

Hope it will help you

 

Tauqeer

 

@tauqeeracma 

Thanks Tauqeer, I also got some other options earlier on and have solved my problem. Many thanks for taking the time with my question.

 

Kind regards

Maria