Home

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
CRII3
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

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies