Lookups Help

  • Thread starter Thread starter FT
  • Start date Start date
F

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
 
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.
 
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
 
Back
Top