Re: Index / Match + Sort / Filter Issue
Quote from NBVC;779998Yes, 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.
Re: Index / Match + Sort / Filter Issue
Quote from NBVC;779998Yes, 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.
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
Quote from NBVC;779982Did 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
Quote from AliGW;779973Copy 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?
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