 • 513K Members
• 2,085 Online
• 610K Conversations

# 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

# Re: Excel sorting cells with formula

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

# Re: Excel sorting cells with formula

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

# Re: Excel sorting cells with formula

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

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

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

# Re: Excel sorting cells with formula

Thanks! It works!

# Re: Excel sorting cells with formula

@kittysum , you are welcome

Related Conversations
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies