Summing values based on matching another column

I have the following setup: Rank names in column A and Rank values in column C (some data in between).

Based on min and max rank I need to find the average value (I2 on the image).

My best attempt is in I3 but I have a feeling that this can be done in a nicer way.

Any help would be much appreciated.

Solution 1:

You can use XLOOKUP to return the values for both of the ranks, then average the result array. Like this:

=AVERAGE(XLOOKUP($F$2:$G$2,$A$2:$A$6,$C$2:$C$6))

enter image description here

Adjust your ranges appropriately.