How do I sort data by size and not in ABC order

Question

I am new to Google Sheets and have only used Excel a little, so Google Sheets is a bit different. I want to sort our band uniforms in size order from smallest to largest but Google Sheets only sorts them alphabetically so my XS ends up at the end. I'd like to sort them from smallest (2XS) to largest (4XL). After reading answers to other questions on this site and trying what was suggested, here is what I have so far.

I've created an extra column (on the page I want to sort) with the Jacket sizes in the order I want them and a second column with the numeric order I want them to sort:

JACKET    Sort Order
2XS-Short   1
2XS-Regular 2
XS-Short    3
XS-Regular  4
SM-Short    5
SM-Regular  6
SM-Tall     7
MD-Short    8
MD-Regular  9
MD-Tall     10
LG-Short    11
LG-Regular  12
LG-Tall     13
XL-Short    14
XL-Regular  15
XL-Tall     16
2XL-Short   17
2XL-Regular 18
2XL-Tall    19
3XL-Short   20
3XL-Regular 21
4XL-Regular 22
4XL-Tall    23
DM XS-Short 24
DM SM-Short 25
DM SM-Regular 26
DM MD-Regular 27

This is a formula I have created so far based on previous examples:
=sort(A2:G201,match(B2:G201,{"2XS-Short","2XS-Reg","XS-Short","XS-Regular","SM-Short","SM-Regular","SM-Tall","MD-Short","MD-Regular","MD-Tall","LG-Short","LG-Regular","LG-Tall","XL-Short","XL-Regular","XL-Tall","2XL-Short","2XL-Regular","2XL-Tall","3XL-Short","3XL-Regular","4XL-Regular","4XL-Tall","DM XS-Short","DM SM-Short","DM SM-Regular","DM SM-Regular"},

I don't know what comes next. Is this even going to work? If so, where do I type the formula so Google Sheets knows I want them sorted in this order?

Answer 1

I believe this simplified version of what you are doing may help...

With a Sheet1 that looks like this...

and a Sheet 2 with the sort order definitions (this is a simpler version of your sort list for illustrative purposes)

Then I used the formula (shown in the first image)

=VLOOKUP(B2,Sheet2!A:B,2,false)

• B2 is the value I need to find in the list of sort-orders
• Sheet2!A:B contains the entire list of sizes vs sort order
• 2 means pull in the second column from my range A:B (which in this case is 2 columns wide)
• false means the data on Sheet 2 is not in alphabetical order (you need this to pull in the correct unique value)

Now, column C on the first Sheet contains the values that you can now use to sort on. Now you can sort Sheet 1 on column C.

Answer 2

I don't know what comes next. Is this even going to work? If so, where do I type the formula so Google Sheets knows I want them sorted in this order?

Type the formula on an empty cell that has enough empty cells to the right and below of it to show the results (200 rows by 7 columns)

Answer 3

• you can do it like this:

  =ARRAY_CONSTRAIN(SORT({A2:G, 
   IFERROR(VLOOKUP(B2:B, {Q2:Q, ROW(Q2:Q)}, 2, 0))}, 8, 1), ROWS(A:A), 7)