Feb 07 2020 09:01 AM
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.
Feb 07 2020 09:52 AM
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))
Feb 07 2020 10:08 AM
Not quite. I've modified your example below. I also tried entering your 2nd formula, but I'm getting #N/A.
Feb 07 2020 10:23 AM
SolutionI 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
Feb 07 2020 10:38 AM
Feb 07 2020 12:38 PM
Glad to help without a beer, you are welcome
Feb 07 2020 10:23 AM
SolutionI 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