Help with Index, Match, Match formula results

[kershaw]

Hello,

I'm using the index, match, match formula to pull up values from a table with a lot of information so I use this formula to drag only the desired values into a new table. The information is dynamic and changes every now and then and this is why I don't to just copy paste values on the new table. This said, everything works fine until I try to sort my new table.

To make things clearer, I'll try to explain with an example. Let's say in my 'information table' I have labels from B to M and then labels from 2 to 15 (A1 is just blank). In each intersection there are values, from B3 to M15. Now, in my "new table" I only want some of these values. For example, I want to keep all of the column labels (B to M) but only to bring the values on rows 3, 5, 8 and 12. Up to here, I'm great. The problem comes when I try to sort this "new table" by column D. All the information seem to mix up when I do this. Until now, I've been solving this by coping and pasting the whole "new table" values, but since I mentioned the the "information table" is dynamic I would like to skip this step if possible.

If anyone knows how to solve this or maybe I'm doing something wrong and can guide me to correct my mistake, I appreciate the help.

[BadlySpelledBuoy]

If you can post a sample workbook it you'll get a solution far quicker.

BSB.

[kershaw]

look at sheet 1 and 2

[JeteMc]

If I am understanding your request correctly then I think that adding another table (sheet 3) does what you want.

Update of test.xlsx

Hope this helps, let me know if you have questions.

[kershaw]

so you just added a sheet with a table referencing the cells in the previous one?

[JeteMc]

Yes, it will automatically do the copy/paste process, which lets you skip that step, so that you can filter and sort without getting the information mixed up.

If this solution is satisfactory, please use the thread tools to mark the thread "Solved". If anyone has provided help, please add to their reputation.

[kershaw]

That worked fine, however, before trying that, it occurred tome to name the arrays and referencing them this way in the formulas and it worked. Thank you for you help!

[FDibbins]

How exactly are you doing the sort - based on what?

[Flyboy65]

Could you not format your Sheet1 data as a table and do all the selecting of rows and sorting from within the table?

[kershaw]

No, because I want to sort the table for the values in the rows so I first need to transpose the table. I wanted to sort the table by any of the letters (a, b, c)test (2).xlsx

If you look in sheet3, that's how it was solved