cannot nest address function into another function

[Guest]

I have individually found addresses within my worksheet, however when I use
the address formula within another function (like a match function), it gives
me an invalid entry error.
Can address be nested? Is there another way to get the cell locations and
then nest them into a formula?
Any help would be great!

 

[Guest]

Perhaps example of formula(e) might help with possible solutions?

 

[Guest]

basically it needs to find a specific match within the 'c' column to the
value in the A column. Then look directly underneath the row that the 'C'
column value is located for a specified number of cells, designated by a
value in the 'f' column and if there is a match between the B32708 value to a
value that is within a specific range of the location where the match to
A32710 was found, (since there are about 40000 rows there will be many
matches to the b32708 values, but I need to just know if it is just in that
specified limited location), it would return a value and if not I will get
the standard N/A.
Here is what the formula looks like
=match(B32708,(ADDRESS(MATCH(A32710,C:C,0),1))ADDRESS(MATCH(A32710,C:C,0)+INDIRECT(ADDRESS(MATCH(A32710,C:C,0),6)),1)),0)

I have tried the =(ADDRESS(MATCH(A32710,C:C,0),1)) and I got $A$27004, which
is correct and I tried
=(ADDRESS(MATCH(A32710,C:C,0)+INDIRECT(ADDRESS(MATCH(A32710,C:C,0),6)),1))
and I got $A$27015, which is also correct
I thin tried =match(B32708,$A$27004:$A$27004,0) and I got 3, which is also
the correct answer.
This is the reason that I believe that there is a problem nesting the
address function into another.
Is there another way to get my desired result?

 

[T. Valko]

I can't figure out what this last portion is doing. As near as I can tell
it's adding a number

+INDIRECT(ADDRESS(MATCH(A32710,C:C,0),6)),1)),0)

Here's how you can do it minus that last portion above:

=MATCH(B32708,INDEX(C:C,MATCH(A32710,C:C,0)):INDEX(C:C,MATCH(A32710,C:C,0)+..........),0)

"............" represents the portion above that I can't figure out.

Biff

 

[Ragdyer]

You should take note that the Address() function returns a "Text" value,
*not* a valid cell reference!

If you enter 25 in A1,
And put this formula anywhere:

=10+A1

You'll get 35 returned.

Now, try this formula

=10+Address(1,1)

And you'll get a #Value! error.

But, you can convert it to a recognizable XL reference by using Indirect(),
which converts all kinds of text references to workable XL values.

=10+Indirect(Address(1,1))
Returns 35

 

[Guest]

Thanks guys for your time,

however it still doesn't work, T., I needed the last portion to give a
range to look up the value. There is a number in that location that will give
the range to look the value up in, (i.e. if the number is 5 it would look
between the matching spot like A55 to A60 to check if the other value that
you are looking for is in there), without it there is no range. Index just
gives the values of what is in the location rather than the location itself.
and Ragdyer, are you saying that the text itself cannot be applied into a
formula? With the middle area of the formula I do not want values (except
where I did use the indirect) rather I need the locations of where the values
are allowed to be.
Thanks again for your time and any further assistance you could offer would
be great.

 

[T. Valko]

Index just gives the values of what is in the location

rather than the location itself.

That's not how INDEX is being used in this application. Trust me, it's doing
exactly what you want it to do. We just have to work that other portion into
it. And now that I know what it is:

=MATCH(B32708,INDEX(C:C,MATCH(A32710,C:C,0)):INDEX(C:C,MATCH(A32710,C:C,0)+INDEX(F:F,MATCH(A32710,C:C,0))),0)

Here's a small sample file that demonstrates this. It does what you're
trying to do but my lookup_values and locations are different than yours.

Index-Index.xls 15 kb

http://cjoint.com/?eAui5MInhG

Ragdyer, are you saying that the text itself cannot be applied into a
formula?

Basically, yes. The ADDRESS functions return TEXT strings. Excel can't use
TEXT strings as range references. Wrapping those ADDRESS functions inside of
INDIRECT will convert those TEXT strings to usable range references that
Excel can use. This will work, however, it's not needed. This makes the
formula longer and *volatile*. That's where the technique I use comes in
handy.

Biff

 

[T. Valko]

Here's a sample file with a better comparison:

Index-Index(1).xls

http://cjoint.com/?eBh6NLpo6A

The formula I'm suggesting is in cell A1. The corrected formula you want to
use is in cell B1.

As you'll see, they both return the same result but one is more efficient
than the other.

Biff