Lookups Help

[FT]

Can anyone help me figure this out:

I have data that comes in sets of 8 like so:

AZ 0 1 2 3
AZ 4 5 6 7
AZ 8 9 10 11
AZ 12 13 14 15
AZ 16 17 18 19
AZ 20 21 22 23
AZ 24 25 26 27
AZ 28 29 30 31
TX 32 33 34 35
TX 36 37 38 39
TX 40 41 42 43
TX 44 45 46 47
TX 48 49 50 51
TX 52 53 54 55
TX 56 57 58 59
TX 60 61 62 63


How can I set a lookup to one value in column A (which is either AZ or
TX in this case) and have it return all 8 values for a specific column
for that lookup value (so for ex, column 3, which would be
1,5,9,13,17,21,25,29).

Any insight is appreciated. Thanks

 

[Dave Peterson]

If the data is always in groups of 8, I'd use 9 cells.

The first cell would hold the row of the first match:

With the state abbreviation in column A1 and the table of data in Sheet2:

=match(a1,sheet2!a:a,0)
(say in B1)

Then in the next cell (C1):
=index(sheet2!c:c,b1)

D1: =index(sheet2!c:c,b1+1)
E1: =index(sheet2!c:c,b1+2)
F1: =index(sheet2!c:c,b1+3)
....
and so forth.

 

[JCS]

Hi,

I was able to do what you asked with Advanced Filtering. You will have to
add filed names to your data columns. You already have the data, now you
will need a criteria range and then a copy to range.

HTH,
John