Ask Your Question
0

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

Comments

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
0

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
0

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

pierre-yves samyn gravatar image

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.

edit flag offensive delete link more

Comments

OMG. reversed INDIRECT. why not easily:

=ADDRESS(MATCH(D1;A1:A10;0);1;4)
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 :)

Regards...

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

My last is also to overhelmed:

="A"&MATCH(D1;A1:A10;0)
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:

=CELL("address";OFFSET(A1;MATCH(D1;A1:A11;0)-1;0))
pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-04-10 12:15:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 3,044 times

Last updated: Apr 10 '15