Using the address formula to create a reference

[pdthakkar]

Hello


I am using the address formula to give me the cell reference, but the output which i am getting is the row and the column address as text.


eg


=(ADDRESS(MATCH(MIN(N2:N288),N2:N288,0)+1,10,4,))


The output which it displays is "J50" in the cell containing the formula, but i want to display the value containing in that cell reference. Seeking help.


Thanks & Regards


Prashant

 

[SirJB7]

Hi, pdthakkar!

Try this:

=INDIRECT(ADDRESS(MATCH(MIN(N2:N288),N2:N288,0)+1,10,4,)))

Regards!

 

[Debraj]

Hi pdthakkar!


To use the ADDRESS as range use INDIRECT, as suggested by SirJB7.


Regards,

Deb.


PS: If you using this just for knowledge.. then its OK..

but if you are using this to search the Minimum value in N2:N288, and then correspondent Value in Column J, then why not Index & Match..

=INDEX($J$2:$J$288,MATCH(MIN($N$2:$N$288),$N$2:$N$288,0))

 

[SirJB7]

@Debra Roy

Hi!

Thanks for the catch, I actually didn't even look after the parenthesis following the ADDRESS, just realized that INDIRECT was missing and copied & pasted.

Regards!

 

[pdthakkar]

Thanks SirJB and Deb both the suggestion has helped me solve my practical problem and also enchance my Knowledge.


Regards!

 

[shrivallabha]

It is always better to use INDEX over INDIRECT.


INDIRECT can be used like this:

=INDIRECT("J"&MATCH(MIN(N2:N288),N2:N288,0))