Excel formula

Question

Hi&nbsp;I wonder if someone could help me.&nbsp;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.&nbsp;Column A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C&nbsp; ....&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; M&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; N&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;T&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;UCountry&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2000&nbsp; &nbsp; &nbsp; &nbsp; 2001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2011&nbsp; &nbsp; &nbsp; &nbsp; 2012&nbsp; .......&nbsp; &nbsp; 2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;YEAR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;% Gov Expenditure&nbsp;&nbsp;Country A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10.7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;11.8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10.6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9.9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10.3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10.3Country B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9.8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9.1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2011&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9.1&nbsp;&nbsp;&nbsp;Country C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 14.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 13.9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;13.9&nbsp;Many thanks in advance

tauqeeracma · Answer

Hi&nbsp;CRII3&nbsp;&nbsp;You can use below formula in column T:=INDEX($B$1:$S$1,1,MATCH(LOOKUP(2,1/(B2:S2&lt;&gt;""),B2:S2),B2:S2,0))&nbsp;and below formula in column U=LOOKUP(2,1/(B2:S2&lt;&gt;""),B2:S2)&nbsp;Sample file is also attached for your reference.Hope it will help you&nbsp;Tauqeer&nbsp;

crii3 · Answer

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

Forum Discussion

Excel formula

2 Replies

Resources