Lookup & Reference
Position & Exact Cell Address of Max or Min Value (XMATCH & ADDRESS)
Find both the relative list position (1..N) and exact worksheet cell address (such as "B4") of the maximum or minimum value in a column using XMATCH(MAX(range), range, 0) and ADDRESS.
Locate WHERE peak revenue or minimum pressure occurred—not just its magnitude!
MAX(B2:B6) tells you peak monthly output was 940 units—but WHICH row in the table produced 940, and what is its exact cell address on the sheet? Nesting MAX(B2:B6) inside exact-match XMATCH(MAX(B2:B6), B2:B6, 0) returns the 1-based relative offset inside B2:B6 (and if two rows tie for the maximum, XMATCH's 6th argument search_mode = -1 even lets you find the LAST peak occurrence instead of the first!)!
To convert that relative offset into an exact worksheet coordinate like "B4" for audit reports, combine ROW(B2) + offset - 1 with COLUMN(B2) inside ADDRESS(row_num, col_num, 4) (where abs_num = 4 outputs clean relative A1 notation without dollar signs, while abs_num = 1 outputs "$B$4")!
| Station ID (Col A) | Peak Output (Col B) | Relative Position in B2:B5 | Worksheet Cell Address |
|---|---|---|---|
| Line-North | 620 units | Position 1 | B2 |
| Line-East | 810 units | Position 2 | B3 |
| Line-South (MAX!) | 940 units | Position 3 (XMATCH = 3) | B4 (ROW(B2)+3-1 = 4) |
| Line-West (MIN!) | 550 units | Position 4 (Min pos = 4) | B5 |
1-Based Relative Position of the Maximum Value in List
Validates at least one numeric entry via COUNT(B2:B5) > 0, then locates MAX(B2:B5) inside B2:B5.
=IF(COUNT(B2:B5) = 0, "No Numeric Values", XMATCH(MAX(B2:B5), B2:B5, 0))Exact Worksheet Cell Address (Relative A1 Style) of the Maximum Value
Converts relative XMATCH offset into sheet row ROW(B2) + offset - 1 and formats via ADDRESS(..., 4).
=IF(COUNT(B2:B5) = 0, "No Numeric Values", ADDRESS(ROW(B2) + XMATCH(MAX(B2:B5), B2:B5, 0) - 1, COLUMN(B2), 4))Exact Worksheet Cell Address of the Minimum Value
Locates MIN(B2:B5) and returns its relative A1 cell coordinate.
=IF(COUNT(B2:B5) = 0, "No Numeric Values", ADDRESS(ROW(B2) + XMATCH(MIN(B2:B5), B2:B5, 0) - 1, COLUMN(B2), 4))Try the example
Use the original values or change them to test your understanding.