Search a 2D Table Grid for a Value & Return Its Exact Cell Address

Flatten the grid without moving positions

B2:D4 is scanned by rows. G2 uses TOCOL with ignore=0 and scan_by_column=FALSE, then exact XMATCH. Do not omit blanks from the flattened vector: doing so would shift the recovered coordinates.

This exercise uses nonblank text bin IDs. Empty queries receive a prompt. Matching is case-insensitive and wildcard characters are literal. Repeated IDs return the first match in row-major order, not every matching cell.

=IF(F2="","Enter a bin ID",IFNA(XMATCH(F2,TOCOL(B2:D4,0,FALSE),0),"Not Found"))

Recover the worksheet address

For flat position k and width W, QUOTIENT(k-1,W) is the zero-based row offset and MOD(k-1,W) is the column offset. Add the starting row and column and pass 4 to ADDRESS for an A1-style address without dollar signs.

BIN-50 is the fifth position and returns C3. This is address text, not a clickable hyperlink or a live reference to the value.

=IF(ISNUMBER(G2),ADDRESS(QUOTIENT(G2-1,COLUMNS(B2:D4))+ROW(B2),MOD(G2-1,COLUMNS(B2:D4))+COLUMN(B2),4),G2)

Return the aisle label

The same row offset chooses the label from A2:A4. BIN-50 belongs to Aisle-B. An absent bin produces Not Found for both outputs. IFNA handles missing matches; other formula failures are not intentionally masked.

=IF(ISNUMBER(G2),INDEX(A2:A4,QUOTIENT(G2-1,COLUMNS(B2:D4))+1),G2)

Check the boundaries

Try the first and last grid cells, a duplicate ID earlier in the grid, literal * or ? identifiers and an empty grid cell before the match. The empty cell must not change the result for BIN-50.

Further reading

• Microsoft Support — TOCOL function
• Microsoft Support — ADDRESS function