Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    doing a multiple lookup (2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: doing a multiple lookup (2003)

    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.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: doing a multiple lookup (2003)

    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

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: doing a multiple lookup (2003)

    this is what I was after.
    Again, thanks.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: doing a multiple lookup (2003)

    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.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: doing a multiple lookup (2003)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •