SOLVED

Formula to yield the cell address which contains the search value?

Copper Contributor

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.

5 Replies

@newguy70 

If I understood your logic correctly, for such mock-up

image.png

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))

@Sergei Baklan 

 

Not quite. I've modified your example below. I also tried entering your 2nd formula, but I'm getting #N/A.

best response confirmed by newguy70 (Copper Contributor)
Solution

@newguy70 

I see, you need it in opposite direction. 

image.png

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

@Sergei Baklan 

 

I believe I owe you a beer sir!  That did the trick, much thanks!

@newguy70 

Glad to help without a beer, you are welcome

1 best response

Accepted Solutions
best response confirmed by newguy70 (Copper Contributor)
Solution

@newguy70 

I see, you need it in opposite direction. 

image.png

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

View solution in original post