Find cell address of value found in range-CodePudding

tl;dr In Google Sheets/Excel, how do I find the address of a cell with a specified value within a specified range where value may be in any row or column?

My best guess is

=CELL("address",LOOKUP("My search value", $search:$range))

but it doesn't work. When it finds a value at all, it returns the rightmost column every time, rather than the column of the cell it found.

I have a sheet of pretty, formatted tables that represent various concepts. Each table consists of

|                             Title                               |
 ------ ------ ------- ------ ------ ------- ------ ------ ------- 
| Sub  | Prop | Name  | Sub  | Prop | Name  | Sub  | Prop | Name  |
 ------ ------ ------- ------ ------ ------- ------ ------ ------- 
|   Sub prop  | value |   Sub prop  | value |   Sub prop  | value |
 ------ ------ ------- ------ ------ ------- ------ ------ ------- 
| data | data | data  | data | data | data  | data | data | data  |
| data | data | data  | data | data | data  | data | data | data  |
⋮

I have 8 such tables of variable height arranged in a grid within the sheet 3 tables wide and 3 tables tall except the last column which has only 2 tables--see image. These fill the range C2:AI78.

Now I have a table off to the right consisting in AK2:AO11 of

| Table title   | Table title address   | ... |
 --------------- ----------------------- ----- 
| Table 1 Title |                       | ... |
| Table 2 Title |                       | ... |
⋮
| Table 8 Title |                       | ... |

I want to fill out the Table title address column. (Would it be easier to do this manually for all of 8 values? Absolutely. Did I need to in order to write this question? Yes. But using static values is not the StackOverflow way, now, is it?)

Based on very limited Excel/Google Sheets experience, I believe I need to use CELL() and LOOKUP() for this.

=CELL("address",LOOKUP($AK4, $C$2:$AI$78))

This retrieves the wrong value. For AL4 (looking for value Death Wave), LOOKUP($AK4, $C$2:$AI$78) should retrieve cell C2 but it finds AI2 instead.

| Max Levels                                    |
 ------------------ --------------- ---- -- ---- 
| UW               | Table Address |    |  |    |
 ------------------ --------------- ---- -- ---- 
| Death Wave       | $AI$3         | 3  |  | 15 |
| Poison Swamp     | $AI$30        |    |  |    |
| Smart Missiles   | $AI$56        |    |  |    |
| Black Hole       | #N/A          | 1  |  |    |
| Inner Land Mines | $AI$3         |    |  |    |
| Chain Lightning  | #N/A          |    |  |    |
| Golden Tower     | $AI$3         |    |  |    |
| Chrono Field     | #N/A          | 25 |  |    |

The error messages for the #N/A columns is

Did not find value '<Table Title>' in LOOKUP evaluation.

My expected table is

| Max Levels                                    |
 ------------------ --------------- ---- -- ---- 
| UW               | Table Address |    |  |    |
 ------------------ --------------- ---- -- ---- 
| Death Wave       | $C$2          | 3  |  | 15 |
| Poison Swamp     | $C$28         |    |  |    |
| Smart Missiles   | $C$54         |    |  |    |
| Black Hole       | $O$2          | 1  |  |    |
| Inner Land Mines | $O$28         |    |  |    |
| Chain Lightning  | $O$54         |    |  |    |
| Golden Tower     | $AA$2         |    |  |    |
| Chrono Field     | $AA$39        | 25 |  |    |

CodePudding user response：

try:

=INDEX(ADDRESS(
 VLOOKUP(A2:A3, SPLIT(FLATTEN(D2:F4&""&ROW(D2:F4)),    ""), 2, ), 
 VLOOKUP(A2:A3, SPLIT(FLATTEN(D2:F4&""&COLUMN(D2:F4)), ""), 2, ), 4))

or if you want to create jump links:

=INDEX(LAMBDA(x, HYPERLINK("#gid=1273961649&range="&x, x))(ADDRESS(
 VLOOKUP(A2:A3, SPLIT(FLATTEN(D2:F4&""&ROW(D2:F4)),    ""), 2, ), 
 VLOOKUP(A2:A3, SPLIT(FLATTEN(D2:F4&""&COLUMN(D2:F4)), ""), 2, ), 4)))

CodePudding user response：

Try this:

=QUERY(
  FLATTEN(
    ARRAYFORMULA(
      IF(
        C:AI=$AK4, 
        ADDRESS(ROW(C:AI), COLUMN(C:AI)),
        ""
        )
      )
    ), "
    SELECT 
      Col1 
    WHERE 
      Col1<>''
    "
  , 0)

Basically, cast all cells in the search range to addresses if they equal the search term. Then flatten that 2D range and filter out non-nulls.