doing a multiple lookup (2003)

[jepalmer]

I have chem. data as shown in columns A in the attached excerpt. I would like to do what is essentially a multiple lookup to move the data. For graphing purposes, each well should have 4 columns of data in alpha order, with the rows in ascending chronological order. In the attached, these would be rows H:P. Not all wells will have all constituents or be sampled in each quarter (or on the same date within a quarter). (I know the quarters look odd, but I work with hydrological data which uses a Water Year that starts October 1 and ends September 30; e.g., I

[HansV]

I don't know about elegant, but I have attached a possible solution. It uses an extra column and a combination of INDEX and MATCH. The formula in cell I3 can be filled down and right.

[sdckapr]

I am not sure what the "result" should be. COuld you provide the output you want?

Based on your "example" it seems a pivot table is what your are after. I don't understand what you would have to transpose, that structure can be obtained directly. You do not even need the "quarter" column, a pivot can group the dates by quarter.

Steve

[jepalmer]

this is what I was after.
Again, thanks.

[jepalmer]

Thanks for the response. I haven't played with the grouping of data and didn't realize pivot tables would do quarters. However, since I am working with a Water Year, Excel's 1st quarter is my 2nd, etc.

Also, my graphing data is set up with 4 constituents for each sampling point, as historically all 4 have been done for each sampling point. However, for the current water year a particular constituent may not have be analyzed. In this case, pivot tables would give me 3 columns of data rather than the requisite 4. Putting in zeros would not work, because I need blanks so no data will be plotted. (I'm using Grapher as my plot program.)

Hans' solution will do the job.

Again, thanks for the response.

[sdckapr]

You can "fool" a pivot table to fill the columns, by adding a "blank dataset" for each column item (does matter on the row) and one for each row item (with at least of the column items).

Steve