using INDIRECT(ADDRESS(...))

D

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

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

=ADDRESS(8,INDEX(MATCH(AO5,E5:P5,0),,)+4) 2
return a cell address based on a matched value.. 4
ADDRESS function 2
Formula to Identify Column Number 4
Cell Address 3
using OFFSET(INDEX(MATCH))) 3
Get Current Row for use in INDIRECT function 7
cell returning totals of 2 cols+ sending this value to another col 5

Top