Lookup & Reference
Return the A1 cell address of the MAX value or lookup match (ADDRESS & XMATCH)
Locate the exact "$C$6" or "C6" coordinate of the highest number or a matched lookup key inside any offset range using ADDRESS, ROW, COLUMN, and XMATCH.
Why XMATCH alone isn't a sheet row—add ROW(FirstCell) - 1 inside ADDRESS!
XMATCH(MAX(C3:C7), C3:C7, 0) returns the RELATIVE position inside C3:C7 (4th item), NOT worksheet Row 6! Whenever your lookup range starts below Row 1, convert relative index k into true worksheet row via ROW(C3) + k - 1.
Passing that row and COLUMN(C3) into ADDRESS(row_num, col_num, abs_num) returns "$C$6" when abs_num = 1 (default absolute) or clean relative "C6" when abs_num = 4—essential for audit trails that tell reviewers the exact cell coordinate of an anomaly!
XMATCH returns the first match in source order when maxima or lookup labels tie. ADDRESS returns text, not a clickable link or live cell reference. Without a sheet-name argument the result is only a coordinate within this worksheet; it is not a unique workbook address.
This maximum example requires all five revenue entries to be numeric. Zero and negative values are valid; missing or text entries report Invalid inputs instead of being silently omitted. Lookup mode returns the first exact label match; the returned value cell can itself be empty.
| Worksheet Cell | Branch (B3:B7) | Revenue (C3:C7) | Relative k vs True Sheet Row |
|---|---|---|---|
| Row 3 (C3) | North | 120 | k = 1 -> Row 3 + 1 - 1 = 3 |
| Row 4 (C4) | South | 340 | k = 2 -> Row 4 |
| Row 5 (C5) | East | 290 | k = 3 -> Row 5 |
| Row 6 (C6) | West | 410 (MAX!) | k = 4 -> Row 6 ("$C$6" / "C6") |
| Row 7 (C7) | Central | 180 | k = 5 -> Row 7 |
Absolute "$C$6" or relative "C6" address of maximum value
Use 1 as the 3rd argument of ADDRESS for "$C$6" or 4 for "C6".
=IF(COUNT(C3:C7)<>5,"Invalid inputs",ADDRESS(ROW(C3) + XMATCH(MAX(C3:C7), C3:C7, 0) - 1, COLUMN(C3), 4))Get cell address of any text lookup match (e.g., "East" -> "$C$5")
Matches E3 ("East") in B3:B7 and reports the corresponding value cell coordinate in column C.
=IF(COUNTBLANK(E3)>0,"No match",LET(matchPosition,IFNA(XMATCH(E3,B3:B7,0),0),IF(matchPosition=0,"No match",ADDRESS(ROW(C3)+matchPosition-1,COLUMN(C3),1))))Try the example
Use the original values or change them to test your understanding.