• 548K Members
• 5,241 Online
• 654K Conversations

Highlighted
New Contributor

# 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
Highlighted

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

Highlighted

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

Highlighted

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

Highlighted

# Re: Excel sorting cells with formula

Thanks! It works!

Highlighted

# Re: Excel sorting cells with formula

@kittysum , you are welcome

Related Conversations
Average set of values if 2 criteria are met
jtorrens in Excel on
1 Replies
Refresh query to add new columns
Caesarus7 in Excel on
0 Replies
Frozen excel project
Mrkeats in Excel on
0 Replies
Macro to Import Data from PDF attachments (Outlook)
calof1 in Excel on
0 Replies
Text to Column (changing delimiters)
John11 in Excel on
0 Replies