Excel sorting cells with formula

[kittysum]

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!

[#2]

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

[#3]

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

[#4]

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

[#5]

@Sergei Baklan 

Thanks! It works!

[#6]

@kittysum , you are welcome