using INDIRECT(ADDRESS(...))
 

=ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns AK8.

=T8-ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns #VALUE!

=T8-INDIRECT(ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25))) returns the
appropriate calculation.

Why?

using INDIRECT(ADDRESS(...))
 

What result except #VALUE! did you expect when you tried to subtract the
text string "AK8" from the value in T8?

Surely you'd fathomed that out from the fact that INDIRECT did the trick?
--
David Biddulph

"Dave F" wrote in message
...
=ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns AK8.

=T8-ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns #VALUE!

=T8-INDIRECT(ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25))) returns the
appropriate calculation.

Why?

using INDIRECT(ADDRESS(...))
 

Because, as HELP explains, ADDRESS() returns a text string, not an address. INDIRECT() converts a text string into an address.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Dave F" wrote in message ...
| =ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns AK8.
|
| =T8-ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns #VALUE!
|
| =T8-INDIRECT(ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25))) returns the
| appropriate calculation.
|
| Why?

using INDIRECT(ADDRESS(...))
 

Perhaps that's the appropriate way to write it with a desired result.
--
regards



"Dave F" wrote:

=ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns AK8.

=T8-ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns #VALUE!

=T8-INDIRECT(ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25))) returns the
appropriate calculation.

Why?

using INDIRECT(ADDRESS(...))
 

The ADDRESS function returns the cell address, AK8 in this case as text. You
cannot do the second formula because you are saying =T8-"AK8". Using the
INDIRECT function gets the value at address AK8 and subtracts it from T8.

"Dave F" wrote in message
...
=ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns AK8.

=T8-ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25)) returns #VALUE!

=T8-INDIRECT(ADDRESS(8,MATCH(AO5,Z5:AK5,0)+25))) returns the
appropriate calculation.

Why?