Once we have that value, we plug it into a standardINDEX and MATCHformula to retrieve the associated name.
In other words, we use the nth largest value like a “key” to retrieve associated information.
TheLARGE functionis a straightforward way to get the nth largest value in a range.
the top score), which is 93.
Retrieve group
The same basic formula will work to retrieve any associated information.
Note: withExcel 365, you’re able to use theFILTER functiontolist top or bottom results dynamically.
Handling ties
Duplicate values in the numeric data will create a “tie”.
If there is the possibility of ties, you may want to implement some kind of tie-breaking strategy.
One approach is to createa new helper column of values which have been adjusted to break ties.
Then use the helper column values to rank and retrieve information.
This makes the logic used to break ties clear and explicit.
Another approach is to break ties based on position only (i.e.
the first tie “wins”).
Notice the expanding range starts on theprior row, to get to avoid a circular reference.
This approach works in this example because there are no duplicate names in the name column.
However, if duplicatenamesoccur in ranked values, the approach needs to be adjusted.
The easiest solution is to see to it that names are unique.
you’re able to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
XLOOKUP supports approximate and exact matching, wildcards (* ?)
