Forum Discussion
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 sensor. Each alias is composed of a 4 letter name/word and a single digit is appended to that. The names range from ABBY to ZEUS (290 in total). I laid out our master spreadsheet with the 4 letter names arranged in column A. Across Row 1 is the digit to be appended to the name, 1 - 9. This 2D array is filled with the 64 bit serial numbers. For example, row 9, column 2's alias is "ARCH2" and it contains the serial number of the unit which has been named "ARCH2"
Given a serial number, how can I search this 2D array to yield the cell address which contains that serial number? Given a list of serial numbers only, I'm trying to get the column, row coordinates so that I can match that serial number with the proper alias.
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
5 Replies
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
