# Return Cell address from a lookup result

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 close merge delete

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

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

Sort by » oldest newest most voted

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


more

OMG. reversed INDIRECT. why not easily:

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

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

( 2015-04-10 10:27:54 +0200 )edit

My last is also to overhelmed:

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

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

( 2015-04-10 12:15:17 +0200 )edit

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

more

## Stats

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

Seen: 3,042 times

Last updated: Apr 10 '15