Sep 04 2019 01:31 AM
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
Sep 04 2019 02:55 AM
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
Sep 04 2019 04:42 AM
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