Lookup & Reference
Return Cell Address of Highest Value or Lookup Match (ADDRESS + MATCH)
Return address text for the first maximum, minimum or matching reading ID, with explicit validation and missing-match handling.
Find WHERE the peak reading occurred—not just its numeric value!
MATCH returns a position relative to the searched range. Add ROW(B2)-1 to obtain its worksheet row, then pass that row and COLUMN(B2) to ADDRESS. Equal maxima or minima return the first occurrence in source order.
ADDRESS returns text, not a reference, hyperlink or command to select the cell. The default format includes dollar signs; argument 4 omits them. Neither form includes the sheet name here.
This example requires all five readings to be numeric; blanks, numeric text and error cells are rejected so they cannot be mistaken for a zero maximum or minimum. Zero and negative readings are valid.
| Cell Coordinate | Sensor kW | Offset in B2:B6 | ADDRESS Output |
|---|---|---|---|
| B2 | 42.5 kW (MIN) | MATCH = 1 -> Row 2 | "$B$2" (Min Location) |
| B3 | 51.0 kW | MATCH = 2 -> Row 3 | "$B$3" |
| B4 | 68.4 kW (MAX) | MATCH = 3 -> Row 4 | "$B$4" or "B4" (Max Location!) |
| B5:B6 | 59.2, 47.8 kW | Offsets 4, 5 | Updates dynamically if B6 spikes! |
Absolute Cell Address ("$B$4") of Maximum Value in Range
Computes worksheet row ROW(B2) + MATCH(MAX(B2:B6), B2:B6, 0) - 1 and worksheet column COLUMN(B2).
=IF(COUNT(B2:B6)<>5,"Invalid inputs",ADDRESS(ROW(B2) + MATCH(MAX(B2:B6), B2:B6, 0) - 1, COLUMN(B2)))Plain Relative Cell Address ("B4") Using abs_num = 4
Omits dollar signs for clean human-readable audit logs.
=IF(COUNT(B2:B6)<>5,"Invalid inputs",ADDRESS(ROW(B2) + MATCH(MAX(B2:B6), B2:B6, 0) - 1, COLUMN(B2), 4))Address of the first minimum
Use the same conversion with MIN. This returns the first minimum if several readings tie.
=IF(COUNT(B2:B6)<>5,"Invalid inputs",ADDRESS(ROW(B2) + MATCH(MIN(B2:B6), B2:B6, 0) - 1, COLUMN(B2)))Address of a matched ID's measurement
D2 is a reading ID from column A. The returned address is its measurement cell in column B. Check the MATCH position before ADDRESS to handle missing IDs. Text matching follows ordinary case-insensitive MATCH semantics; this example uses plain T1-style IDs.
=IF(COUNTBLANK(D2)>0,"Enter ID",IF(IFNA(MATCH(D2,A2:A6,0),0)=0,"No match",ADDRESS(ROW(B2)+MATCH(D2,A2:A6,0)-1,COLUMN(B2))))Try the example
Use the original values or change them to test your understanding.