MATRIX

Information

The MATRIX custom formula is a combination of three build-in formulas from Google Sheets:

  1. ARRAYFORMULA / QUERY
  2. INDEX
  3. SORT

The main reason to combine them is that all of them have a way of returning an array (matrix). Combining them allows you to do all (and more...) with just one formula, potentially making things less convoluted.

Do's / Dont's

The MATRIX formula is meant to return a range, but it is not possible to perform ARRAYFORMULA like operations, for example a boolean comparison within the MATRIX range: 

=MATRIX(A1:A2=B1:B2)

It is possible to do this, on the other hand:

=MATRIX(ARRAYFORMULA(A1:A2=B1:B2))

Syntax (JsDocs text from script)

/**
 * Create a matrix, that's optionally restricted by row's and column's and optionally sorted by a range of column's
 *
 * @param {A2:B26} range The data to be sorted
 * @param {(number|object|string)} opt_row  Include by inputting a row number (1) or range of row numbers ({1,2,3}), 
                                            exclude by turning the include input as a string ("1" or "{1,2,3}")
 * @param {(number|object|string)} opt_col  Include by inputting a column number (1) or range of column numbers ({1,2,3}), 
                                            exclude by turning the include input as a string ("1" or "{1,2,3}")
 * @param {(boolean|number)}       opt_sort Use TRUE|FALSE or 1,TRUE,2,FALSE
 * @return The modified range
 * @customfunction
 */

Examples

=MATRIX(A1:C2,,{2,3},2,false)                        // only show columns 2 and 3
                                                     // and sort descendingly by column 2
=MATRIX(ARRAYFORMULA(VALUE(A1:C2)),,{2,3},2,false)   // only show columns 2 and 3 
                                                     // and sort descendingly by column 2
=MATRIX(A1:C2,,,false)                               // sort whole range descendingly
=MATRIX(A1:C2,,"3")                                  // exclude column 2 from the range
=MATRIX(A1:Z20,"{1,2,7,8}","{4,8}")                  // exclude row and column series from range

Video

Screenshot