Google-sheets – Google Sheets: can’t sort table that use INDEX and MATCH

google sheets

I have this scheme on a Google Spreadsheet:

    A         B         C        D                E
1   text      text      text     INDEX/MATCH      INDEX/MATCH (expansion)
2   text      text      text     INDEX/MATCH      INDEX/MATCH (expansion)
3   text      text      text     INDEX/MATCH      INDEX/MATCH (expansion)
4   text      text      text     INDEX/MATCH      INDEX/MATCH (expansion)

So, columns A, B and C have "normal" text, column D has functions to get content from other worksheet if search matches (formula below) and the next columns (E, F, G, H …) are just the expansion of the formulas in column D.

This is the formula in column D, repeated for each row of the column:

=IFERROR(
   INDEX(
      'Form Responses 1'!$D$2:$V, 
      SMALL(
        IF(
          ISNUMBER(
            SEARCH(
              "My Search", 
              'Form Responses 1'!$B$2:$B
             )
          ),
          MATCH(
            ROW('Form Responses 1'!$B$2:$B), 
            ROW('Form Responses 1'!$B$2:$B)
          )
        ), 
        ROW(A1)
      )
    ),
 "")

I'd like to be able to sort my table, but when I try, data is restored in its initial sort. And if I add text to columns A, B and C, when I sort it changes to the order where it should be and it remains there (instead content of column D+ is restored in its initial order..)..

Is there a way to solve this? Having the content of columns A, B, C stick to the content of columns D+ and being able to sort the table?

The workflow I was thinking about is: adding values to columns A, B and C and then sort the table by values on column B for example. This is not possible because the order of values in columns D+ can't change (since it's getting the order of the other worksheet). If I try to sort the table, just the values in A,B and C change position, content in columns D+ is restored to its original position.

Best Answer

When ISNUMBER(SEARCH("My Search",'Form Responses 1'!$B$2:$B)) is TRUE the row values are not dependent on the row values of the column A, so sorting column A will not sort the value in column D for this cases.

To be display the sorted values, use SORT() or QUERY() instead of sorting in place.

An alternative is to copy and paste the values of Column D before sorting.

The same could be applied to Column E.

Related Topic