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

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.


FT wrote:

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

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

"FT" wrote:

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