Forum Discussion
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 retrieve data from another sheet based on column A
| Column A | Column B | Column C | |
| Row | Name | Age | Birth Month |
| 1 | Peter | 12 | February |
| 2 | Mary | 34 | May |
| 3 | John | 23 | April |
If I sort column B from largest to smallest, the becomes as below:
| Column A | Column B | Column C | |
| Row | Name | Age | Birth Month |
| 1 | Mary | 23 | April |
| 2 | John | 12 | February |
| 3 | Peter | 34 | May |
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??
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!
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))
