Matching down a column

[Tsunami3169]

Hi all and thanks ahead of time for all help and suggestions provided.
Typically I would search for the answer but unfortunately time is not
on my side.

Here's part of my sheet1 set up.
D3:Q3 I have headers for my columns.
A2 has the name of a district that can be selected. (I used
data.validation to create a drop down)

Here's the results I'm after.

In cells B4:B15 I need to get the store numbers that match the
district in A2 by referencing sheet2. (sheet2 has a column with the
districts and their matching stores in the next column)

I tried to use "Index,Match" but the results are only the first store
that matches the district.

Again Thanks for any suggestions provided.

also if I've missed some detail, sorry about that. Again I'm in a bit
of a rush, not at all by choice.

 

[Max]

Here's one way to set it up to return the multiple matches that you're after
...

In Sheet2, where you have your source data, assume that the districts are
running in B2 down, with the store#s in C2 down

In Sheet1,
your DV for selection of the district is in A2

Put in B4:
=IF(ROWS($1:1)>COUNT(C:C),"",INDEX(Sheet2!C:C,SMALL(C:C,ROWS($1:1))-2))

Put in C4:
=IF(A$2="","",IF(Sheet2!B2=A$2,ROW(),""))
Leave C1:C3 blank

Select B4:C4, copy down to cover the max expected extent of source data in
Sheet2, say down to row100. Hide away col C. Col B will return the required
results, all neatly bunched at the top.

 

[Tsunami3169]

Here's one way to set it up to return the multiple matches that you're after
..

In Sheet2, where you have your source data, assume that the districts are
running in B2 down, with the store#s in C2 down

In Sheet1,
your DV for selection of the district is in A2

Put in B4:
=IF(ROWS($1:1)>COUNT(C:C),"",INDEX(Sheet2!C:C,SMALL(C:C,ROWS($1:1))-2))

Put in C4:
=IF(A$2="","",IF(Sheet2!B2=A$2,ROW(),""))
Leave C1:C3 blank

Select B4:C4, copy down to cover the max expected extent of source data in
Sheet2, say down to row100. Hide away col C. Col B will return the required
results, all neatly bunched at the top.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik











- Show quoted text -

Thanks tons!
I had to tweak it a little, I'm not sure why. Once I removed the -2 it
worked like a charm.

Thanks again

 

[Max]

The suggested set-up was to closely suit what you described in your post.
The "-2" is a required arithmetic adjustment as the criteria col C's formula
starts in C4 down (as you said the results were to be in B4 down) while the
source data in Sheet2 was assumed to start in row 2 down (this was assumed
as it wasn't mentioned in your post). Anyway I'm glad you tweaked it to suit
what you really had over there <g>.