Index / Match + Sort / Filter Issue

    Hi


    I have built a spreadsheet with a large data table using Index / Match formulas eg =INDEX(Workings!$GC$10:$GC$50,MATCH($C46,Workings!$R$10:$R$50,0),FALSE)


    When I try to sort the table the formulas do not follow and the table is wrong. Any ideas how I can fix this so that I can sort the data?


    Thanks

    Re: Index / Match + Sort / Filter Issue


    Copy and paste the values to a new sheet and do your sorting and filtering there: formulae and sorting don't mix.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Index / Match + Sort / Filter Issue


    Quote from AliGW;779973

    Copy and paste the values to a new sheet and do your sorting and filtering there: formulae and sorting don't mix.


    Unfortunately I can't do that. I need the table to be able to be sorted with the formulae intact. Is this possible?

    Re: Index / Match + Sort / Filter Issue


    I don't think so, certainly not without VBA code. You will have to wait for one of the coding experts to comment.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Index / Match + Sort / Filter Issue


    Did you notice that the range in the Match function is not absoluted for the rows... ie.. they are missing the $ before the row indicators...


    Workings!$R10:$R50 s/b Workings!$R$10:$R$50


    That may mess things up...

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Index / Match + Sort / Filter Issue


    Quote from NBVC;779982

    Did you notice that the range in the Match function is not absoluted for the rows... ie.. they are missing the $ before the row indicators...


    Workings!$R10:$R50 s/b Workings!$R$10:$R$50


    That may mess things up...


    Sorry, in the actual formula it is correct. I typed the formula out and forgot to add the absolute. Will edit the original post.

    Re: Index / Match + Sort / Filter Issue


    Are you able to attach your workbook, if there is no confidential info.


    I don't see why you should not be able to filter/sort and keep the data using that formula..

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Index / Match + Sort / Filter Issue


    I believe I've figured out the cause for the sorting issue.


    When the Match lookupvalue references a sheet name, the lookup sticks to the cell as if it were an absolute. ie a formula showing "MATCH('Summary'!$B13" has issues while "MATCH($B13" does not have sorting issues.


    Does this make sense? Is this logic correct?

    Re: Index / Match + Sort / Filter Issue


    Yes, that makes sense and is true, but your original formula didn't show that.... so it was not mentioned.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Index / Match + Sort / Filter Issue


    Quote from NBVC;779998

    Yes, that makes sense and is true, but your original formula didn't show that.... so it was not mentioned.


    Yes you're right. Thanks very much.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login