Aug 22 2019 08:42 PM
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!
Aug 22 2019 10:01 PM - edited Aug 22 2019 10:07 PM
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??
Aug 22 2019 11:17 PM
@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!
Aug 23 2019 01:54 PM
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))