Index Match Large & Duplicates

I assume the first formula is straightforward, ROWS function increments by 1 every row so you get the largest n values (in order) if you copy down n rows

With this formula

=INDEX(A$2:A$8,AGGREGATE(15,6,(ROW(B$2:B$8)-ROW(B$2)+1)/(B$2:B$8=E2),COUNTIF(E$2:E2,E2)))

the AGGREGATE function lets you use multiple functions, specified by the first argument, so 15 is the equivalent of SMALL function, 6 means errors are ignored (see Excel help on AGGREGATE for all the possible functions and codes).

The next part is the array itself - ROW(B$2:B$8)-ROW(B$2)+1 gives you a simple 1 to n array of numbers where n is the number of rows in the range, so here that gives us this array

{1;2;3;4;5;6;7}

and we divide that by another array generated by (B$2:B$8=E2).....which will give us an array of TRUE or FALSE values. In your example E2 is the largest value, 10, and that only appears once in B2:B8, in the first position so that generates this array:

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

When we divide the first array by the second we get another 7 element array which is each element of the first array divided by each element (in the same order) of array two.....and when you use mathematical operations on Boolean values TRUE becomes 1 and FALSE becomes 0, so the resulting array is all errors except for the first element where 1/TRUE = 1/1 = 1 hence

{1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

[Note: you can see these arrays yourself by highlighting the relevant part of the formula in the formula bar and pressing F9 key]

Note that back at the start we said that the 6 in AGGREGATE function tells it to ignore errors, so we only have one number here, 1 and the COUNTIF formula in this row is

COUNTIF(E$2:E2,E2)....which must return 1, so our whole aggregate function looks like this:

AGGREGATE(15,6,{1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},1)

so that means we take the kth smallest value from that array, with the k value being the 1 at the end supplied by COUNTIF....so that's 1 and when we pass that 1 to the INDEX function it finds the value at that position (1) in A2:A8....i.e. James

Now this is relatively simple for James because there is only one row with 10, our aggregate functions finds that row (1) and the INDEX function gives us the name......and also for the next row where there is only one 9 and the formula will find the related name (Daniel).....but what happens on row 4 where we have the first of the three 8s in E4 and the formula looks like this:

=INDEX(A$2:A$8,AGGREGATE(15,6,(ROW(B$2:B$8)-ROW(B$2)+1)/(B$2:B$8=E4),COUNTIF(E$2:E4,E4)))

because there are 3 instances of 8 we now get 3 row numbers in the array, like this

AGGREGATE(15,6,{#DIV/0!;#DIV/0!;3;4;5;#DIV/0!;#DIV/0!},COUNTIF(E$2:E4,E4))

....and note that as we drag the formula down the range in the COUNTIF formula is expanding, so it counts all the 8s on the current row and above, on row 4 that's 1 again so from that array it still select the smallest value, which is 3, with associated name Kim, but the clever part is that on row 5 where E5 = 8 again that same array with 3, 4 and 5 is generated...but COUNTIF now results in the value 2 (because E4 and E5 are both 8) so instead of taking the value 3 again (which would result in a duplicate name) the formula this time has 2 as the k value and therefore returns 4 and INDEX returns Carli.

This will work correctly for any number of duplicate values.

Before AGGREGATE function was added in Excel 2010 you could do a similar thing with SMALL function instead of AGGREGATE but typically AGGREGATE doesn't require "array entry" so is probably preferable unless you are using an older Excel version