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

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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