Why does this Formula work?

[Kevin Vaughn]

I have the following formula in cell I2 (copied down as far as needed and
copied across to AA2):

=INDEX($A2:$A$253,MATCH(I$1, $D$2:$D$253,0))

which when copied to J2 becomes (for the sake of discussion):
=INDEX($A2:$A$253,MATCH(J$1, $D$2:$D$253,0))

In cells I1 through AA1 are numbers (representing Districts) A small sample
of the Districts is:
1;2;4;5 and a small sample of the lookup table is:
Store;Description;Zone;District
184;Chula Vista South;11;1
559;Mission Gorge;11;1
157;Orange - Chapman;11;2
520;Lake Forest;11;2
523;Newport-N/Wpc;11;2
519;Encino;11;4
548;Porter Ranch, CA;11;4
167;Studio City;11;5
188;Rolling Hills Estates;11;5
196;San Pedro CA;11;5

What I wanted to do was to get all of the stores that belonged to a district
(rather than doing a copious amount of copying and pasting)

I started with a very similar formula, which did not work. I ended up with
the 1st store found, repeated when copied down. Formula is the same as the
first formula showed, but with an absolute reference for the Index row
instead of relative as it is now:

=INDEX($A$2:$A$253,MATCH(I$1, $D$2:$D$253,0))

I thought that if I made the row relative, it would work for the first
column, but when I copied the formula across and down, I would end up with
the same problem in the subsequent columns (I would get the correct first
store, but all of the stores under that would be the same as the first.) My
plan was, once I had the first column working, I would experiment with
different parts of the formula to try to get subsequent columns working;
maybe using something like the Offset of the match from the previous row.

Much to my surprise, this formula basically did what I wanted. Since the
number of stores per District varied, my plan was to copy the formula across
and then down to a point where I figured I would start getting #N/A errors
because, for a given column, there were no more stores in that District.

However, what I found was that when all of the stores for a District were
returned, it would start displaying stores for the next District. I could
quickly identify where I needed to start deleting formulas by looking at the
first value in the next column and seeing it was the same as the store in the
column I was looking at.

So my question is, why does this formula work (for subsequent columns?)
Also, why don’t I get #N/A values when there are no more stores for a given
district? And, are there alternative formulas that would also work?

BTW, I am eventually going to create named ranges from the different
districts, so will be changing the # in row 1 to District# (i.e. District1)
since you apparently can’t use numbers for Named Ranges. Also, I discovered
(but should have realized before I tried) that D# won’t work (i.e. D1, D2,
etc.) as those look like cell references. So, so far, my new formula looks
like:

=INDEX($A2:$A$253,MATCH(VALUE(RIGHT(K$1,LEN(K$1) - 8)), $D$2:$D$253,0))

--
Kevin Vaughn

[JMB]

I know it's ugly, but seems to work. Enter with Control+Shift+Enter

=INDEX($A$2:$A$253,MATCH(SMALL(IF($D$2:$D$253=I$1,($D$2:$D$253=I$1)*(ROW(INDIRECT("1:"&ROWS($D$2:$D$253)))),""),ROW(I2)-ROW(I$2)+1),IF($D$2:$D$253=I$1,($D$2:$D$253=I$1)*(ROW(INDIRECT("1:"&ROWS($D$2:$D$253)))),""),0))

"Kevin Vaughn" wrote:

> I have the following formula in cell I2 (copied down as far as needed and
> copied across to AA2):
>
> =INDEX($A2:$A$253,MATCH(I$1, $D$2:$D$253,0))
>
> which when copied to J2 becomes (for the sake of discussion):
> =INDEX($A2:$A$253,MATCH(J$1, $D$2:$D$253,0))
>
> In cells I1 through AA1 are numbers (representing Districts) A small sample
> of the Districts is:
> 1;2;4;5 and a small sample of the lookup table is:
> Store;Description;Zone;District
> 184;Chula Vista South;11;1
> 559;Mission Gorge;11;1
> 157;Orange - Chapman;11;2
> 520;Lake Forest;11;2
> 523;Newport-N/Wpc;11;2
> 519;Encino;11;4
> 548;Porter Ranch, CA;11;4
> 167;Studio City;11;5
> 188;Rolling Hills Estates;11;5
> 196;San Pedro CA;11;5
>
> What I wanted to do was to get all of the stores that belonged to a district
> (rather than doing a copious amount of copying and pasting)
>
> I started with a very similar formula, which did not work. I ended up with
> the 1st store found, repeated when copied down. Formula is the same as the
> first formula showed, but with an absolute reference for the Index row
> instead of relative as it is now:
>
> =INDEX($A$2:$A$253,MATCH(I$1, $D$2:$D$253,0))
>
> I thought that if I made the row relative, it would work for the first
> column, but when I copied the formula across and down, I would end up with
> the same problem in the subsequent columns (I would get the correct first
> store, but all of the stores under that would be the same as the first.) My
> plan was, once I had the first column working, I would experiment with
> different parts of the formula to try to get subsequent columns working;
> maybe using something like the Offset of the match from the previous row.
>
> Much to my surprise, this formula basically did what I wanted. Since the
> number of stores per District varied, my plan was to copy the formula across
> and then down to a point where I figured I would start getting #N/A errors
> because, for a given column, there were no more stores in that District.
>
> However, what I found was that when all of the stores for a District were
> returned, it would start displaying stores for the next District. I could
> quickly identify where I needed to start deleting formulas by looking at the
> first value in the next column and seeing it was the same as the store in the
> column I was looking at.
>
> So my question is, why does this formula work (for subsequent columns?)
> Also, why don’t I get #N/A values when there are no more stores for a given
> district? And, are there alternative formulas that would also work?
>
> BTW, I am eventually going to create named ranges from the different
> districts, so will be changing the # in row 1 to District# (i.e. District1)
> since you apparently can’t use numbers for Named Ranges. Also, I discovered
> (but should have realized before I tried) that D# won’t work (i.e. D1, D2,
> etc.) as those look like cell references. So, so far, my new formula looks
> like:
>
> =INDEX($A2:$A$253,MATCH(VALUE(RIGHT(K$1,LEN(K$1) - 8)), $D$2:$D$253,0))
>
> --
> Kevin Vaughn

[JMB]

Slightly better after removing some redundancies:

=INDEX($A$2:$A$253,MATCH(SMALL(IF($D$2:$D$253=I$1,ROW(INDIRECT("1:"&ROWS($D$2:$D$253))),""),ROW(I2)-ROW(I$2)+1),IF($D$2:$D$253=I$1,ROW(INDIRECT("1:"&ROWS($D$2:$D$253))),""),0))

"Kevin Vaughn" wrote:

> I have the following formula in cell I2 (copied down as far as needed and
> copied across to AA2):
>
> =INDEX($A2:$A$253,MATCH(I$1, $D$2:$D$253,0))
>
> which when copied to J2 becomes (for the sake of discussion):
> =INDEX($A2:$A$253,MATCH(J$1, $D$2:$D$253,0))
>
> In cells I1 through AA1 are numbers (representing Districts) A small sample
> of the Districts is:
> 1;2;4;5 and a small sample of the lookup table is:
> Store;Description;Zone;District
> 184;Chula Vista South;11;1
> 559;Mission Gorge;11;1
> 157;Orange - Chapman;11;2
> 520;Lake Forest;11;2
> 523;Newport-N/Wpc;11;2
> 519;Encino;11;4
> 548;Porter Ranch, CA;11;4
> 167;Studio City;11;5
> 188;Rolling Hills Estates;11;5
> 196;San Pedro CA;11;5
>
> What I wanted to do was to get all of the stores that belonged to a district
> (rather than doing a copious amount of copying and pasting)
>
> I started with a very similar formula, which did not work. I ended up with
> the 1st store found, repeated when copied down. Formula is the same as the
> first formula showed, but with an absolute reference for the Index row
> instead of relative as it is now:
>
> =INDEX($A$2:$A$253,MATCH(I$1, $D$2:$D$253,0))
>
> I thought that if I made the row relative, it would work for the first
> column, but when I copied the formula across and down, I would end up with
> the same problem in the subsequent columns (I would get the correct first
> store, but all of the stores under that would be the same as the first.) My
> plan was, once I had the first column working, I would experiment with
> different parts of the formula to try to get subsequent columns working;
> maybe using something like the Offset of the match from the previous row.
>
> Much to my surprise, this formula basically did what I wanted. Since the
> number of stores per District varied, my plan was to copy the formula across
> and then down to a point where I figured I would start getting #N/A errors
> because, for a given column, there were no more stores in that District.
>
> However, what I found was that when all of the stores for a District were
> returned, it would start displaying stores for the next District. I could
> quickly identify where I needed to start deleting formulas by looking at the
> first value in the next column and seeing it was the same as the store in the
> column I was looking at.
>
> So my question is, why does this formula work (for subsequent columns?)
> Also, why don’t I get #N/A values when there are no more stores for a given
> district? And, are there alternative formulas that would also work?
>
> BTW, I am eventually going to create named ranges from the different
> districts, so will be changing the # in row 1 to District# (i.e. District1)
> since you apparently can’t use numbers for Named Ranges. Also, I discovered
> (but should have realized before I tried) that D# won’t work (i.e. D1, D2,
> etc.) as those look like cell references. So, so far, my new formula looks
> like:
>
> =INDEX($A2:$A$253,MATCH(VALUE(RIGHT(K$1,LEN(K$1) - 8)), $D$2:$D$253,0))
>
> --
> Kevin Vaughn

[Kevin Vaughn]

Thanks. I thought this post had disappeared (when I went to look for it this
morning, but it was there (I just couldn't find it using my name as the
search criteria for some reason.)

This works. I don't know why, but now I have a working example of it.
Also, since it returned errors when a match wasn't found, it was a simple
matter to get rid of the errors (as opposed to how I was getting rid of the
extraneous formulas before.)

--
Kevin Vaughn


"JMB" wrote:

> Slightly better after removing some redundancies:
>
> =INDEX($A$2:$A$253,MATCH(SMALL(IF($D$2:$D$253=I$1,ROW(INDIRECT("1:"&ROWS($D$2:$D$253))),""),ROW(I2)-ROW(I$2)+1),IF($D$2:$D$253=I$1,ROW(INDIRECT("1:"&ROWS($D$2:$D$253))),""),0))
>
> "Kevin Vaughn" wrote:
>
> > I have the following formula in cell I2 (copied down as far as needed and
> > copied across to AA2):
> >
> > =INDEX($A2:$A$253,MATCH(I$1, $D$2:$D$253,0))
> >
> > which when copied to J2 becomes (for the sake of discussion):
> > =INDEX($A2:$A$253,MATCH(J$1, $D$2:$D$253,0))
> >
> > In cells I1 through AA1 are numbers (representing Districts) A small sample
> > of the Districts is:
> > 1;2;4;5 and a small sample of the lookup table is:
> > Store;Description;Zone;District
> > 184;Chula Vista South;11;1
> > 559;Mission Gorge;11;1
> > 157;Orange - Chapman;11;2
> > 520;Lake Forest;11;2
> > 523;Newport-N/Wpc;11;2
> > 519;Encino;11;4
> > 548;Porter Ranch, CA;11;4
> > 167;Studio City;11;5
> > 188;Rolling Hills Estates;11;5
> > 196;San Pedro CA;11;5
> >
> > What I wanted to do was to get all of the stores that belonged to a district
> > (rather than doing a copious amount of copying and pasting)
> >
> > I started with a very similar formula, which did not work. I ended up with
> > the 1st store found, repeated when copied down. Formula is the same as the
> > first formula showed, but with an absolute reference for the Index row
> > instead of relative as it is now:
> >
> > =INDEX($A$2:$A$253,MATCH(I$1, $D$2:$D$253,0))
> >
> > I thought that if I made the row relative, it would work for the first
> > column, but when I copied the formula across and down, I would end up with
> > the same problem in the subsequent columns (I would get the correct first
> > store, but all of the stores under that would be the same as the first.) My
> > plan was, once I had the first column working, I would experiment with
> > different parts of the formula to try to get subsequent columns working;
> > maybe using something like the Offset of the match from the previous row.
> >
> > Much to my surprise, this formula basically did what I wanted. Since the
> > number of stores per District varied, my plan was to copy the formula across
> > and then down to a point where I figured I would start getting #N/A errors
> > because, for a given column, there were no more stores in that District.
> >
> > However, what I found was that when all of the stores for a District were
> > returned, it would start displaying stores for the next District. I could
> > quickly identify where I needed to start deleting formulas by looking at the
> > first value in the next column and seeing it was the same as the store in the
> > column I was looking at.
> >
> > So my question is, why does this formula work (for subsequent columns?)
> > Also, why don’t I get #N/A values when there are no more stores for a given
> > district? And, are there alternative formulas that would also work?
> >
> > BTW, I am eventually going to create named ranges from the different
> > districts, so will be changing the # in row 1 to District# (i.e. District1)
> > since you apparently can’t use numbers for Named Ranges. Also, I discovered
> > (but should have realized before I tried) that D# won’t work (i.e. D1, D2,
> > etc.) as those look like cell references. So, so far, my new formula looks
> > like:
> >
> > =INDEX($A2:$A$253,MATCH(VALUE(RIGHT(K$1,LEN(K$1) - 8)), $D$2:$D$253,0))
> >
> > --
> > Kevin Vaughn