Sorry if this is a bit long-winded, just trying to paint the bigger picture.

Okay, I am running a spreadsheet collating the results of an online virtual
sailing competition. Usually, I create a table based on the order of the
entries, enter results each race, run a discard of worst result to get final
points and I rank across the whole fleet, use an
INDEX(range,MATCH(ref,range,0)) function to build a new table in rank order
and publish.

This series, we are following the new Olympic format, where the top ten
rankings after ten races and one discard are placed in the "gold" fleet, and
the remainder are in the "green" fleet. ONLY a boat in the Gold fleet can
win overall, after the following two races. The remaining boats will also
sail the same two races, but seperately.

To have the points work correctly, I need to rank races 11 and 12 within
each fleet, but without re-sorting the whole table, and hence I have
non-contiguous ranges for each fleet. Can I rank on non-contiguous ranges,
and can I make the ranges conditional on the fleet reference (this is a
column in the table using an if statement)?