Lookups Help
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 |
Lookups Help
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 |
Lookups Help
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 |
All times are GMT +1. The time now is 05:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com