Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup date (Excel 97 SR2)

    A coworker asked me for help in determining the date that a maximum was hit.
    Cities are in col A. The dates are in D2:X2. The city data is in D4:x74.
    Col B has formula that reutrns max of D:X for the city.
    In column C he wants the date (from row 2) that the max occured for each city.
    I have tried a few things without success.
    I have attached the workbook.
    Anyone got a minute or so?

    Thanks,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Lookup date (Excel 97 SR2)

    Hi Chuck

    See the attached file for one method, it uses the following:

    <code>=INDIRECT(ADDRESS(2,MATCH(B4,D4:X4,0)+3))</code>

    If the maximum occurs more than once, it returns the date for the first occurrence.

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

    Re: Lookup date (Excel 97 SR2)

    Another possibility: enter the formula
    <code>
    =INDEX($D$2:$X$2,MATCH(B4,D4:X4,0))
    </code>
    in cell C4, and fill down as far as needed (or double click the fill handle in the lower right hand corner). Format the cells in column C as dates.

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup date (Excel 97 SR2)

    Tony and Hans,

    Thanks guys works GREAT!

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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