Lookup & Reference
Find the row and column of a value in a 2D matrix (TOCOL + XMATCH)
Locate any unique SKU, employee badge, or peak MAX reading inside an R x C grid and return its Row Header, Column Header, or cell ADDRESS.
Flatten with TOCOL, match 1D position, and decode (rowIndex, colIndex) with QUOTIENT & MOD
XMATCH and MATCH only search 1-dimensional rows or columns—they throw #VALUE! if handed an R x C 2D block directly.
Flattening the M x N grid row-by-row with TOCOL(B2:D4) turns the 2D search into a clean 1D lookup pos = XMATCH(Target, TOCOL(B2:D4))! Then integer division rowIndex = QUOTIENT(pos - 1, N) + 1 and remainder colIndex = MOD(pos - 1, N) + 1 recover the exact 1-based row and column coordinates!
| Shelf \ Bay | Bay-1 (Col 1) | Bay-2 (Col 2) | Bay-3 (Col 3) |
|---|---|---|---|
| Shelf-A (Row 1) | SKU-10 (pos 1) | SKU-22 (pos 2) | SKU-35 (pos 3) |
| Shelf-B (Row 2) | SKU-41 (pos 4) | SKU-88 (pos 5!) | SKU-50 (pos 6) |
| Shelf-C (Row 3) | SKU-63 (pos 7) | SKU-77 (pos 8) | SKU-99 (pos 9) |
2D matrix coordinate-lookup and cell-address formulas
For SKU-88 at pos = 5 in a 3-column grid: rowIndex = QUOTIENT(4, 3) + 1 = 2 ("Shelf-B") and colIndex = MOD(4, 3) + 1 = 2 ("Bay-2").
=LET(pos, XMATCH(F2, TOCOL(B2:D4)), w, COLUMNS(B2:D4), rowIndex, QUOTIENT(pos - 1, w) + 1, colIndex, MOD(pos - 1, w) + 1, INDEX(A2:A4, rowIndex) & " / " & INDEX(B1:D1, 1, colIndex))Return exact worksheet cell address (e.g., "$C$3") of the 2D match
Add ROW(B2) - 1 and COLUMN(B2) - 1 to convert relative grid coordinates into sheet coordinates inside ADDRESS.
=LET(pos, XMATCH(F2, TOCOL(B2:D4)), w, COLUMNS(B2:D4), rowIndex, QUOTIENT(pos - 1, w) + ROW(B2), colIndex, MOD(pos - 1, w) + COLUMN(B2), ADDRESS(rowIndex, colIndex))Inputs and interpretation
Keep TOCOL in its default row-major mode without removing blanks or errors, or the calculated offsets no longer map to the original grid. Duplicate values return the first row-major match. Missing targets return an error. ADDRESS returns text, not a clickable link.
Try the example
Use the original values or change them to test your understanding.