Convert ADDRESS() text result to cell reference

[FirstVette52]

I am using =ADDRESS(MATCH(1,E3:E5,0)+2,3) to calculate the cell reference,
but the reference is returned as text. How do I convert it to an actual
reference?

Leader Board
Name
=ADDRESS(MATCH(1,E3:E5,0)+2,3)

/ A B C D E
2 Tag Division Last Name Sponsor Rank
3 Y016 Youth Jackson AC Inc 2
4 A209 Adult Adams TTE 1
5 A902 Adult 2 Shepherd B&F 3

I want to see 'Adams', not the text 'C4'

Thanks for any help you may be able to give me

 

[Peo Sjoblom]

As long as you use it on an address that is not in a closed workbook you can
use INDIRECT

=INDIRECT(ADDRESS(MATCH(1,E3:E5,0)+2,3))


--


Regards,


Peo Sjoblom




"FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP
E-mail provider is) netzero.com> wrote in message
news:[email protected]...

 

[Max]

INDIRECT(cell ref) would return it, viz:
=INDIRECT(ADDRESS(MATCH(1,E3:E5,0)+2,3))

But perhaps just index/match would suffice, eg:
=INDEX(C3:C5,MATCH(1,E3:E5,0))

 

[Teethless mama]

=OFFSET(E1,MATCH(1,E1:E4,0)-1,-2)

 

[Spiky]

As long as you use it on an address that is not in a closed workbook you can
use INDIRECT

=INDIRECT(ADDRESS(MATCH(1,E3:E5,0)+2,3))

--

Regards,

Peo Sjoblom

And if you look up a freeware set of UDFs call "Morefunc", they have
made an INDIRECT function that works on closed workbooks.