• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error when sorting table with Index & Match formula

mf1

mf1

Member
Hello,

Objective
I am trying to sort data within an excel table that has formulas and Index & match lookups.

Problem
The index & match formulas are not moving with the sort.

Note: I changed the index & match to a vlookup in another file and everything sorted correctly.


This has me stumped and I'm hoping its a simple solution, as I much prefer using index & match.

I have attached a sample file to view.

Thanks,

mf1
 

Attachments

I am not sure I understand this question properly. But the sort sure seems wrong. You can fix it by removing the reference to Report worksheet in your formulas. For example,

Instead of this formula in Sales column (E)

=IFERROR(INDEX(DeptData[SALES],MATCH(Report!A6,DeptData[Helper],0)),"")

Use this:

=IFERROR(INDEX(DeptData[SALES],MATCH(Report!A6,DeptData[Helper],0)),"")
 
Thats perfect, thankyou.

When I write the first part of the reference for the index and go to another worksheet and then start writing the match reference, I didn't know it was using the worksheet name. This had been bugging me for a while, Thankyou:)
 
You must log in or register to reply here.
Back
Top