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.