google-sheets

Sort range by order of values in separate column

Given a Google Sheet with the following data:

table 1

  | A | B | C
1 | q | w | e
2 | a | s | d
3 | z | x | c

table 2

  | A
1 | a
2 | z
3 | q

What formula(s) can sort the rows in 'table 1' according to the stored order of values in 'table 2'?

Edit: How can this be done using a formula in only one cell?

Solution

  • Does this formula work as you want (assuming your tables are named ranges):

    =SORT(table1,MATCH(FILTER(A:A,LEN(A:A)),table2,0),1)

    See this example Sheet to see it working: https://docs.google.com/spreadsheets/d/16vUSSZ3lqnwWTVCw37vV0CiFDu8aaZc-gDpaUIqNrzM/edit?usp=sharing