Return Cell address from a lookup result

[MAGRFA]

I have a list of data and wish to use a lookup formula to find a value but instead of returning anything in that row I want it to return the cell address e.g. if the lookup result is in E25 I want to return E25 in my formula Cell

[karolus]

Why ? — for what reason exactly do you want the Celladdress?

[Charlie3]

I suggest that you post the spreadsheet (data changed) so that we can all have a look. You can use the ‘Indirect’ and ‘Concatenate’ commands to access cells. The following works =INDIRECT(CONCATENATE(“B” &Place)). Place could be replaced with a number ending up with “B6”, “Place” being a named cell.

All is possible…

[PYS]

Hi

With D1 containing the search value, and A1:A10 containing the data, you can use:

=CELL("address";INDIRECT("A"&MATCH(D1;A1:A10;0)))

See Adresse.ods example.

[karolus]

OMG. reversed INDIRECT. why not easily:

=ADDRESS(MATCH(D1;A1:A10;0);1;4)

[PYS]

OMG indeed ! What was I thinking? Why complicate life to do simply while it is so simple to be complicated

Regards…

[karolus]

My last is also to overhelmed:

="A"&MATCH(D1;A1:A10;0)

[PYS]

I remember now what I was thinking … but my answer was still incorrect. I wanted a “dynamic” formula to anticipate the possibility of inserting/deleting rows/column.
A possibility:

=CELL("address";OFFSET(A1;MATCH(D1;A1:A11;0)-1;0))