Excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-836098%22%20slang%3D%22en-US%22%3EExcel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-836098%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22%22%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wonder%20if%20someone%20could%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20excel%20sheet%20with%20a%20list%20of%20205%20countries%20and%20data%20on%20Education%20from%202000%20to%202017.%20I%20would%20like%20a%20formula%20that%20would%20copy%20the%20latest%20year%20on%20column%20T%20and%20the%20correspondent%20percentage%20on%20Column%20U%20as%20the%20example%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EColumn%20A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20B%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BC%26nbsp%3B%20....%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20M%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20N%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20S%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BT%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BU%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ECountry%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2000%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%202001%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2011%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%202012%26nbsp%3B%20.......%26nbsp%3B%20%26nbsp%3B%202017%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3CSPAN%3E%3CSTRONG%3EYEAR%3C%2FSTRONG%3E%3C%2FSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CSPAN%3E%3CSTRONG%3E%25%20Gov%20Expenditure%3C%2FSTRONG%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECountry%20A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2010.7%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B11.8%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B10.6%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B9.9%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B10.3%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2017%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2010.3%3C%2FP%3E%3CP%3ECountry%20B%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B9.8%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B9.1%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2011%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%209.1%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECountry%20C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2014.2%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2013.9%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%202001%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B13.9%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-836098%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-836220%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-836220%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403103%22%20target%3D%22_blank%22%3E%40CRII3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20below%20formula%20in%20column%20T%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DINDEX(%24B%241%3A%24S%241%2C1%2CMATCH(LOOKUP(2%2C1%2F(B2%3AS2%26lt%3B%26gt%3B%22%22)%2CB2%3AS2)%2CB2%3AS2%2C0))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20below%20formula%20in%20column%20U%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DLOOKUP(2%2C1%2F(B2%3AS2%26lt%3B%26gt%3B%22%22)%2CB2%3AS2)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20file%20is%20also%20attached%20for%20your%20reference.%3C%2FP%3E%3CP%3EHope%20it%20will%20help%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-836388%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-836388%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3CSPAN%3ETauqeer%2C%20I%20also%20got%20some%20other%20options%20earlier%20on%20and%20have%20solved%20my%20problem.%20Many%20thanks%20for%20taking%20the%20time%20with%20my%20question.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EKind%20regards%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMaria%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted

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

 

Highlighted

@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