ExcelBanter - Convert ADDRESS() text result to cell reference

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Convert ADDRESS() text result to cell reference (https://www.excelbanter.com/excel-worksheet-functions/185573-convert-address-text-result-cell-reference.html)

Convert ADDRESS() text result to cell reference

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

Answer: Convert ADDRESS() text result to cell reference

To convert the text result of the ADDRESS() function to an actual cell reference, you can use the INDIRECT() function.

Here's an example formula that uses both functions to return the value in the cell referenced by the ADDRESS() function:

Formula:




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

In this formula, the ADDRESS() function returns the text representation of the cell reference, and the MATCH() function is used to find the row number of the desired cell. The +2 is added to the result of the MATCH() function to account for the fact that the data starts in row 3 (not row 1).

The INDIRECT() function then takes the text result of the ADDRESS() function and converts it to an actual cell reference. This allows the formula to return the value in the desired cell (in this case, "Adams").

Use the ADDRESS() function to return the text representation of the cell reference
Use the MATCH() function to find the row number of the desired cell
Add +2 to the result of the MATCH() function to account for the fact that the data starts in row 3 (not row 1)
Use the INDIRECT() function to convert the text result of the ADDRESS() function to an actual cell reference

Convert ADDRESS() text result to cell reference

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

Convert ADDRESS() text result to cell reference

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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FirstVette52" wrote:
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
--
FirstVette52

Convert ADDRESS() text result to cell reference

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

"FirstVette52" wrote:

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

Convert ADDRESS() text result to cell reference

On Apr 29, 3:47 pm, "Peo Sjoblom" wrote:
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.