Forum Discussion

kittysum's avatar
kittysum
Copper Contributor
Aug 23, 2019

Excel sorting cells with formula

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 ret...
excel
Formulas and Functions
IngeborgHawighorst's avatar
IngeborgHawighorst
MVP
Aug 23, 2019

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

kittysum's avatar
kittysum
Copper Contributor
Aug 23, 2019

IngeborgHawighorst 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!

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 23, 2019

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

     

Resources