Lookup & Reference
Get the name or label associated with the maximum value
Pair XLOOKUP with MAX (or filtered sub-arrays) to return the top-performing rep, product, or date to the left of your metric column.
Look left from MAX(values) back to the label column
MAX(C2:C5) tells you the highest sales figure is $114,000, not WHO closed it—and VLOOKUP cannot look leftward from column C back to Rep column A.
XLOOKUP(MAX(C2:C5), C2:C5, A2:A5) searches C2:C5 for that peak number and returns the corresponding name in A2:A5 ("Liam").
Use populated numeric measures. Validate missing regional groups before FILTER and decide how to handle missing values; blank source values can be coerced to zero. Ties return the first matching row.
| Rep (A) | Region (B) | ClosedWon (C) | Role |
|---|---|---|---|
| Nora | East | 88000 | Overall MIN |
| Liam | West | 114000 | Overall MAX |
| Tariq | East | 96000 | East MAX |
| Mei | West | 102000 |
Overall winner and conditional regional winner
For regional winners, filter both the Rep and ClosedWon columns to "East" inside LET before running XLOOKUP(MAX(subV), subV, subR) so another region sharing the same dollar amount can never collide.
=XLOOKUP(MAX(C2:C5), C2:C5, A2:A5)Region-filtered winner formula
Filtering to East first safely isolates Tariq ($96,000) as East's top rep.
=LET(subR, FILTER(A2:A5, B2:B5="East"), subV, FILTER(C2:C5, B2:B5="East"), XLOOKUP(MAX(subV), subV, subR))Try the example
Use the original values or change them to test your understanding.