Home

Excel sorting cells with formula

%3CLINGO-SUB%20id%3D%22lingo-sub-819311%22%20slang%3D%22en-US%22%3EExcel%20sorting%20cells%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819311%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20got%20a%20question%20about%20Excel%20sorting.%20The%20function%20I%20used%20is%20Sort%20%26amp%3B%20Filter.%3C%2FP%3E%3CP%3EHere%20is%20my%20setting%20-%20see%20below%20illustration%3A%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%20-%20parameter%20input%3C%2FP%3E%3CP%3EColumn%20B-C%20-%20INDEX%20and%20MATCH%20function%20to%20%3CSTRONG%3Eretrieve%20data%20from%20another%20sheet%3C%2FSTRONG%3Ebased%20on%20column%20A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EColumn%20A%3C%2FTD%3E%3CTD%3EColumn%20B%3C%2FTD%3E%3CTD%3EColumn%20C%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERow%3C%2FTD%3E%3CTD%3EName%3C%2FTD%3E%3CTD%3EAge%3C%2FTD%3E%3CTD%3EBirth%20Month%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EPeter%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3EFebruary%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EMary%3C%2FTD%3E%3CTD%3E34%3C%2FTD%3E%3CTD%3EMay%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3EJohn%3C%2FTD%3E%3CTD%3E23%3C%2FTD%3E%3CTD%3EApril%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20sort%20column%20B%20from%20largest%20to%20smallest%2C%20the%20becomes%20as%20below%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EColumn%20A%3C%2FTD%3E%3CTD%3EColumn%20B%3C%2FTD%3E%3CTD%3EColumn%20C%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERow%3C%2FTD%3E%3CTD%3EName%3C%2FTD%3E%3CTD%3EAge%3C%2FTD%3E%3CTD%3EBirth%20Month%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EMary%3C%2FTD%3E%3CTD%3E23%3C%2FTD%3E%3CTD%3EApril%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EJohn%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3EFebruary%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3EPeter%3C%2FTD%3E%3CTD%3E34%3C%2FTD%3E%3CTD%3EMay%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%3F%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-819311%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819342%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20sorting%20cells%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819342%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396525%22%20target%3D%22_blank%22%3E%40kittysum%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ecan%20you%20post%20the%20formulas%20you%20use%20in%20B%20and%20C%3F%20Or%20even%20better%2C%20post%20a%20workbook%20with%20a%20small%20data%20sample%20that%20shows%20the%20problem.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20am%20attaching%20a%20sample%20file%20where%20the%20sort%20order%20does%20not%20change%20the%20looked%20up%20values.%20The%20Index%2FMatch%20uses%20absolute%20references%20for%20the%20lookup%20range.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(%24F%242%3A%24F%244%2CMATCH(A2%2C%24E%242%3A%24E%244%2C0))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAre%20your%20lookup%20ranges%20referenced%20with%20%24%20signs%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819406%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20sorting%20cells%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819406%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3BHere's%20a%20sample%20document.%20I%20use%20%24%20sign%20for%20columns%20but%20not%20for%20rows%20because%20I%20have%20hundreds%20sets%20of%20data.%20If%20I%20used%20%24%20sign%2C%20I%20need%20to%20revise%20the%26nbsp%3B%20formula%20in%20every%20row%2C%20right%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-820852%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20sorting%20cells%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-820852%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396525%22%20target%3D%22_blank%22%3E%40kittysum%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20your%20List%20sheet%20remove%20reference%20on%20this%20sheet%20in%20formulas%2C%20i.e.%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(Update!D%3AD%2C%20MATCH(%24C5%2C%20Update!%24C%3A%24C%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Einstead%20of%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(Update!D%3AD%2C%20MATCH(List!%24C5%2C%20Update!%24C%3A%24C%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-824097%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20sorting%20cells%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-824097%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%20It%20works!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-824191%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20sorting%20cells%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-824191%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396525%22%20target%3D%22_blank%22%3E%40kittysum%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
kittysum
New Contributor

Hi I got a question about Excel sorting. The function I used is Sort & Filter.

Here is my setting - see below illustration: 

Column A - parameter input

Column B-C - INDEX and MATCH function to retrieve data from another sheet based on column A

 

 Column AColumn BColumn C
RowNameAgeBirth Month
1Peter12February
2Mary34May
3John23April

 

If I sort column B from largest to smallest, the becomes as below:

 Column AColumn BColumn C
RowNameAgeBirth Month
1Mary23April
2John12February
3Peter34May

 

Can someone help? Thanks!

5 Replies

Hello @kittysum ,

 

can you post the formulas you use in B and C? Or even better, post a workbook with a small data sample that shows the problem.

 

I am attaching a sample file where the sort order does not change the looked up values. The Index/Match uses absolute references for the lookup range.

 

=INDEX($F$2:$F$4,MATCH(A2,$E$2:$E$4,0))

 

Are your lookup ranges referenced with $ signs??

@Ingeborg Hawighorst Here's a sample document. I use $ sign for columns but not for rows because I have hundreds sets of data. If I used $ sign, I need to revise the  formula in every row, right?

 

Thanks!

@kittysum 

In your List sheet remove reference on this sheet in formulas, i.e. use

=INDEX(Update!D:D, MATCH($C5, Update!$C:$C,0))

instead of

=INDEX(Update!D:D, MATCH(List!$C5, Update!$C:$C,0))

 

@Sergei Baklan 

 

Thanks! It works!

@kittysum , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies