return valuve
 

hi all,
help please
if the ADDRESS of a cell is produced by the formula
=ADDRESS(MATCH(A2&"*",B:B,0),2,4) . Say for example B251 , how can I return
the value of this address.
many thanks

return valuve
 

=INDEX(B:B,MATCH(A2&"*",B:B,0))

Looks like it should work.



excelFan wrote:

hi all,
help please
if the ADDRESS of a cell is produced by the formula
=ADDRESS(MATCH(A2&"*",B:B,0),2,4) . Say for example B251 , how can I return
the value of this address.
many thanks

--

Dave Peterson

return valuve
 

Hi!

Either:

=INDEX(B:B,MATCH(A2&"*",B:B,0))

=INDIRECT(ADDRESS(MATCH(A2&"*",B:B,0),2,4))

Biff

"excelFan" wrote in message
...
hi all,
help please
if the ADDRESS of a cell is produced by the formula
=ADDRESS(MATCH(A2&"*",B:B,0),2,4) . Say for example B251 , how can I
return
the value of this address.
many thanks

return valuve
 

thanks Dave, works fine

"Dave Peterson" wrote:

=INDEX(B:B,MATCH(A2&"*",B:B,0))

Looks like it should work.



excelFan wrote:

hi all,
help please
if the ADDRESS of a cell is produced by the formula
=ADDRESS(MATCH(A2&"*",B:B,0),2,4) . Say for example B251 , how can I return
the value of this address.
many thanks

--

Dave Peterson

return valuve
 

Some others

=Indirect(ADDRESS(MATCH(A2&"*",B:B,0),2,4))

or
=Offset($B$1,MATCH(A2&"*",B:B,0)-1,0)

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
=INDEX(B:B,MATCH(A2&"*",B:B,0))

Looks like it should work.



excelFan wrote:

hi all,
help please
if the ADDRESS of a cell is produced by the formula
=ADDRESS(MATCH(A2&"*",B:B,0),2,4) . Say for example B251 , how can I
return
the value of this address.
many thanks

--

Dave Peterson

return valuve
 

thanks Biff, that's also work fine

"Biff" wrote:

Hi!

Either:

=INDEX(B:B,MATCH(A2&"*",B:B,0))

=INDIRECT(ADDRESS(MATCH(A2&"*",B:B,0),2,4))

Biff

"excelFan" wrote in message
...
hi all,
help please
if the ADDRESS of a cell is produced by the formula
=ADDRESS(MATCH(A2&"*",B:B,0),2,4) . Say for example B251 , how can I
return
the value of this address.
many thanks

return valuve
 

thanks Tom, this also do the job

"Tom Ogilvy" wrote:

Some others

=Indirect(ADDRESS(MATCH(A2&"*",B:B,0),2,4))

or
=Offset($B$1,MATCH(A2&"*",B:B,0)-1,0)

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
=INDEX(B:B,MATCH(A2&"*",B:B,0))

Looks like it should work.



excelFan wrote:

hi all,
help please
if the ADDRESS of a cell is produced by the formula
=ADDRESS(MATCH(A2&"*",B:B,0),2,4) . Say for example B251 , how can I
return
the value of this address.
many thanks

--

Dave Peterson

return valuve
 

You're welcome!

Biff

"excelFan" wrote in message
...
thanks Biff, that's also work fine

"Biff" wrote:

Hi!

Either:

=INDEX(B:B,MATCH(A2&"*",B:B,0))

=INDIRECT(ADDRESS(MATCH(A2&"*",B:B,0),2,4))

Biff

"excelFan" wrote in message
...
hi all,
help please
if the ADDRESS of a cell is produced by the formula
=ADDRESS(MATCH(A2&"*",B:B,0),2,4) . Say for example B251 , how can I
return
the value of this address.
many thanks