Forum Discussion
Solved
Formula to yield the cell address which contains the search value?
Background: we have a large number of sensors deployed to the field. Each has a unique 64 bit serial number (EUI). To make things easier on field staff, we've started to assign an alias to each senso...
I see, you need it in opposite direction.
It could be
=INDEX($A$2:$A$4,SUMPRODUCT(($B$2:$J$4=$B12)*ROW($B$2:$J$4))-ROW($A$1))&(SUMPRODUCT(($B$2:$J$4=$B12)*COLUMN($B$2:$J$4))-COLUMN($A$1))assuming values within the range are unique
Not quite. I've modified your example below. I also tried entering your 2nd formula, but I'm getting #N/A.
I see, you need it in opposite direction.
It could be
=INDEX($A$2:$A$4,SUMPRODUCT(($B$2:$J$4=$B12)*ROW($B$2:$J$4))-ROW($A$1))&(SUMPRODUCT(($B$2:$J$4=$B12)*COLUMN($B$2:$J$4))-COLUMN($A$1))assuming values within the range are unique
Glad to help without a beer, you are welcome