using Address reference command
 

I seem to have a problem using the address function in excel 2003
professional within the match function. Each of the parts do what they need
to do individually, just not together. The current form looks like this:

=match(B32708,ADDRESS(MATCH(A32710,C:C,0),1):ADDRE SS(MATCH(A32710,C:C,0)+INDIRECT(ADDRESS(MATCH(A327 10,C:C,0),6)),1),0)

where match will look at the value in the column headings (in this case
b32708), in the array, this starts from the point represented the first
address and ends with the second. These both give the correct values when
done individually (for the first its $A$27004, for the second its $A$27015)
and finally the match should be exact.

Basically my question is, can address be used in this fashion, and if not is
there another way to get my desired results (i.e. to know if the value in
b32708 is also in the array above)?

using Address reference command
 

Are you trying to return a value from a table where you're trying to pick out a
column by matching a value in the header row (and maybe matching a value in one
of the other columns (like the first column))?

If yes, take a look at Debra Dalgleish's site:
http://www.contextures.com/xlFunctions03.html
especially examples 2 and 3



Ian wrote:

I seem to have a problem using the address function in excel 2003
professional within the match function. Each of the parts do what they need
to do individually, just not together. The current form looks like this:

=match(B32708,ADDRESS(MATCH(A32710,C:C,0),1):ADDRE SS(MATCH(A32710,C:C,0)+INDIRECT(ADDRESS(MATCH(A327 10,C:C,0),6)),1),0)

where match will look at the value in the column headings (in this case
b32708), in the array, this starts from the point represented the first
address and ends with the second. These both give the correct values when
done individually (for the first its $A$27004, for the second its $A$27015)
and finally the match should be exact.

Basically my question is, can address be used in this fashion, and if not is
there another way to get my desired results (i.e. to know if the value in
b32708 is also in the array above)?

--

Dave Peterson

using Address reference command
 

Basically yes, however the problem remains that the index of where I am
looking has about 40000 rows with about 4000 subgroups and the function needs
to first find the subgroup row within A and then look in the next certain
amount of spaces which I found and are in column f and only look in that
limited range to return a value if the value that I am search for is there.
That is why I think I need the specific address.
The site though has helped me solve another problem, so thank you for your
time and if you have any other suggestions let me know.
"Dave Peterson" wrote:

Are you trying to return a value from a table where you're trying to pick out a
column by matching a value in the header row (and maybe matching a value in one
of the other columns (like the first column))?

If yes, take a look at Debra Dalgleish's site:
http://www.contextures.com/xlFunctions03.html
especially examples 2 and 3



Ian wrote:

I seem to have a problem using the address function in excel 2003
professional within the match function. Each of the parts do what they need
to do individually, just not together. The current form looks like this:

=match(B32708,ADDRESS(MATCH(A32710,C:C,0),1):ADDRE SS(MATCH(A32710,C:C,0)+INDIRECT(ADDRESS(MATCH(A327 10,C:C,0),6)),1),0)

where match will look at the value in the column headings (in this case
b32708), in the array, this starts from the point represented the first
address and ends with the second. These both give the correct values when
done individually (for the first its $A$27004, for the second its $A$27015)
and finally the match should be exact.

Basically my question is, can address be used in this fashion, and if not is
there another way to get my desired results (i.e. to know if the value in
b32708 is also in the array above)?

--

Dave Peterson