Matching values

The MATCH() function let's you find the position of cells that match a particular criterion. It's a little tricky, so bear with this.

It works best when the data is already sorted (that's the case we'll consider here). The first argument is the limit value, the second argument is the data range, and the third argument is 1 if the column is sorted in ascending order and -1 for descending.

If a column of data, A2:A100 was sorted in ascending order, MATCH(1000, A2:A100, 1) would find the position of the largest value in A2:A100 that was less than or equal to 1000.

If B2:B100 was sorted in descending order, MATCH(1000, B2:B100, -1) would find the position of the smallest value in B2:B100 that was greater than or equal to 1000.

The dataset has been sorted by descending number of Skippers.