Highlighted
New Contributor

# Excel formula

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

2 Replies
Highlighted

# Re: Excel formula

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.

Tauqeer

Highlighted

# Re: Excel formula

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