Ask Your Question

Return Cell address from a lookup result

asked 2015-04-09 17:15:02 +0200

MAGRFA gravatar image

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

edit retag flag offensive close merge delete


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

karolus gravatar imagekarolus ( 2015-04-09 21:01:12 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2015-04-09 23:59:04 +0200

Charlie gravatar image

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

edit flag offensive delete link more

answered 2015-04-10 09:23:56 +0200

pierre-yves samyn gravatar image


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


See Adresse.ods example.

edit flag offensive delete link more


OMG. reversed INDIRECT. why not easily:

karolus gravatar imagekarolus ( 2015-04-10 10:20:05 +0200 )edit

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


pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-04-10 10:27:54 +0200 )edit

My last is also to overhelmed:

karolus gravatar imagekarolus ( 2015-04-10 10:42:29 +0200 )edit

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:

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-04-10 12:15:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-04-09 17:15:02 +0200

Seen: 3,074 times

Last updated: Apr 10 '15