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
If I understood your logic correctly, for such mock-up
you may find serial number with
=INDEX($B$2:$J$4,MATCH(LEFT(B7,4),$A$2:$A$4,0),--RIGHT(B7))and cell address as
=ADDRESS(MATCH(LEFT(B8,4),$A$2:$A$4,0)+ROW($A$1),RIGHT(B8)+COLUMN($A$1))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
