We can use a custom formula to custom sort by partial match in Google Sheets. There is no built-in menu command or function for this.

When might you want to custom-sort a list by partially matching keywords?

For example, you could use this to sort a list of T-shirts by their sizes. You could also use it to sort a list of customers by their location, or a list of products by their category.

If you have the T-shirt description in one column and the size in another column, you do not need to use partial matching. You can simply sort by custom order.

How to Custom Sort by Partial Match in Google Sheets with an Example

Here is an example of how to use the SORT function with XMATCH and REGEXEXTRACT to sort a list of T-shirts by size:

Please see the following spreadsheet for the list to sort in column A, the list to sort by in column C, and the expected result after sorting in column E.

To get the expected results, I’ve used the following formula in cell E2:

=LET(
   list, A2:A17,
   by, C2:C8,
     SORT(
        list,
        XMATCH(
           TRIM(REGEXEXTRACT(" "&list,"(?i) "&TEXTJOIN("| ",TRUE,by))),
           by
        ),1
     )
)

This Custom Sort By Partial Match formula is a combination of the functions TEXTJOIN, REGEXEXTRACT, TRIM, XMATCH, and SORT. The LET function is optional.

A Breakdown of the Custom Sort by Partial Match Formula in Google Sheets

To understand the Custom Sort By Partial Match formula that sorts a range by partial matching of keywords, we need to explain the role of the LET function.

This function allows us to assign names to intermediate expressions, which can make the formula more readable and easier to maintain.

In the formula above, we have used the LET function to name the ranges A2:A17 with list and C2:C8 with by. We then use these names in the formula to reference the ranges more easily.

Here is a breakdown of the Google Sheets formula:

1. REGEXEXTRACT: This function extracts the T-shirt size from the text in column A.

Syntax of the REGEXECTRACT Function in Google Sheets:

REGEXEXTRACT(text, regular_expression)

where:

• text is the text to extract the substring from. In this case, it is A2:A17.
• regular_expression is the regular expression to use to extract the substring. In this case, it is (?i) XS| S| M| L| XL| 2XL| 3XL, which matches any of the T-shirt sizes in the list, regardless of case.

The TEXTJOIN returns the major part of the regular expression, without (?i)

The TRIM function removes any extra spaces from the beginning and end of the text. In our example, it removes the extra space from the beginning of the extracted substrings, which are the T-shirt sizes.

Moving on to the next key formula in Custom Sort by Partial Match.

2. XMATCH: This function returns the position of the first matching element in an array. In this case, it is used to find the position of the T-shirt size that is returned by the REGEXEXTRACT() function in the list of sizes in column C.

Syntax of the XMATCH Function in Google Sheets:

XMATCH(search_key, lookup_range, [match_mode], [search_mode])

where:

• search_key is the result of the REGEXEXTRACT() function.
• lookup_range is the range of cells to search for the search_key in. In this case, it is C2:C8.

3. SORT: This function sorts the data in the specified range in ascending order.

Syntax of the SORT Function in Google Sheets:

SORT(range, sort_column, is_ascending, [sort_column2, …], [is_ascending2, …])

where:

• range is the range of cells to sort. In this case, it is A2:A17.
• sort_column is the column to sort by. In this case, it is the XMATCH result, which is the position of the T-shirt size in the list of sizes in column C.
• is_ascending is a boolean value that specifies whether to sort in ascending order. In this case, it is 1, which means to sort in ascending order.

The combination of the REGEXEXTRACT(), XMATCH(), and SORT() functions can be used to perform a custom sort by partial match of keywords in Google Sheets. The rest of the functions used in the formula are optional and used to avoid using helper columns.

Custom Sort by Partial Match in Multiple Column Ranges in Google Sheets

Assume you have a table, not a list, and the table contains two columns: T-shirt size in the first column and their price in the second column.

Modifying the range in the formula from A2:A17 to A2:B17 is not enough. You need to make one more change to the formula.

Here is the formula that you can use for custom sort by partial match in multiple columns:

=LET(
   list, A2:B17,
   by, C2:C8,
     SORT(
        list,
        XMATCH(
           TRIM(REGEXEXTRACT(" "&CHOOSECOLS(list,1),"(?i) "&TEXTJOIN("| ",TRUE,by))),
           by
        ),1
     )
)

I’ve replaced A2:A17 with B2:B17 and replaced the list with CHOOSECOLS(list,1). The CHOOSECOLS function returns the specified column in the range for a substring match.

When you use the above formula to custom sort by partial match in multiple columns, specify the correct column number within CHOOSECOLS.

For example, if the range is A2:C17 in the order Item Code, Item, Price, you might want to use CHOOSECOLS(list,1) or CHOOSECOLS(list,2), depending on whether you want to sort by substring match of Item Code or Item.

Conclusion

The above Custom Sort By Partial Match formula is case-insensitive. You can easily convert it to case-sensitive by removing the (?i) flag from the formula. This means that you should replace "(?i) "&TEXTJOIN("| ",TRUE,by) with " "&TEXTJOIN("| ",TRUE,by).