'Lookup a Value from a range, and return the cell reference address

Working in Google sheets and want to lookup a value (in cell U355) and search for it in the range (F3:R650) The Value is in cell L412 and I want L412 to be the result. Played with INDEX/MATCH, LOOKUP, FIND, CELL etc. but don't seem to be able to get the correct combination as they don't like 2 dimensional ranges.

I can use COUNTIF to confirm that the value is there, but want to know exactly where it is. If it makes any difference, the value will be in Columns F,I,L,O,R

Many thanks

Solution 1:^[1]

Try this

=iferror(arrayformula(split(query(flatten(column(F3:R325)*(F3:R325=U4)&"-"&row(F3:R325)*(F3:R325=U4)),"select * where Col1<>'0-0'"),"-")),)

the result will be 15 | 55 that means value has been retrieved in column 15 (O) line 55

Solution 2:^[2]

try:

=ARRAYFORMULA(VLOOKUP(U4:U5, {FLATTEN(F3:R), 
 VLOOKUP(1*FLATTEN(COLUMN(F3:R)&IFERROR(ROW(F3:R)/0)), 
 {SEQUENCE(26), CHAR(ROW(65:90))}, 2, 0)&
 FLATTEN(ROW(F3:R)&IFERROR(COLUMN(F3:R)/0))}, 2, 0))

Solution 3:^[3]

using ADDRESS as the strategy:

=ADDRESS(SUMPRODUCT(ROW(range)*(range=value)),SUMPRODUCT(COLUMN(range)*(range=value)))

your formula would become:

=ADDRESS(SUMPRODUCT(ROW($F$3:$R$325)*($F$3:$R$325=U4)),SUMPRODUCT(COLUMN($F$3:$R$325)*($F$3:$R$325=U4)))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Mike Steelson
Solution 2	player0
Solution 3	Mr Shane

'Lookup a Value from a range, and return the cell reference address

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]