using INDIRECT(ADDRESS(...))

[Dave F]

=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?

 

[David Biddulph]

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?

 

[Niek Otten]

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

| =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?

 

[driller]

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

 

[Tyro]

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.