The INDEX formula doesn’t recognize column letters, so you must use numbers (counting from the left). The syntax for the MATCH function is: MATCH(lookup_value, lookup_array, [match_type]).

SORT()’s array argument is the entire FILTER() function. The sort_index value of 2 sorts by the Value column, the second column in array. The sort_index value is -1, which applies a descending sort.

With INDEX, you select an array, and then tell it a number, and INDEX returns that element. Walter Hickey / BI So here, INDEX gave us the 2nd element of the B2:B9 array, which is New York.

The array form of the LOOKUP function is useful for large datasets organized in a rectangular range with multiple rows or columns. It searches for a value in the first row (for horizontally ...