Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup Values (2002)

    I'm having problems trying to sort out a lookup formula that will collect values based on selected dates. The attached spread sheet explains it. I'm sure it is a simple exercise for a Guru out there - Thanks for your help

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

    Re: Lookup Values (2002)

    Try
    =HLOOKUP(C$18,$AD$3:$AP$9,3,FALSE)
    This looks up the month (C18) in the first row of AD3:AP9, and returns the corresponding value from the 3rd row of this range. For Bolts, replace 3 by 4 etc. If you would like the formula to look this up too, use
    =HLOOKUP(C$18,$AD$3:$AP$9,MATCH($B19,$C$3:$C$9,0), FALSE)

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Values (2002)

    Hans, That's great Thanks. The other thing I would like to do is to sum the total number of Bolts for the 12 month period ie, Jan-03 to Jan-04 based on the lookup date. Any thoughts?

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Values (2002)

    Try this in cell AD19 and copy it down for each item:

    =SUM(OFFSET($C$3,MATCH($B19,$C$5:$C$9,0)+1,MATCH($ C$18,$D$3:$AP$3,0),1,-12))

    You can make that last -12 refer to another cell if you want to vary the number of months to sum.

    By the way, 12 months is really February to January, not January to January.

  5. #5
    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: Lookup Values (2002)

    Try this array (confirm with ctrl-shift-enter)
    =SUM(IF(($AD$3:$AP$3>DATE(YEAR($C$18)-1,MONTH($C$18),DAY($C$18)))*($AD$3:$AP$3<=$C$18),I NDEX($AD$5:$AP$9,MATCH($B19,$C$5:$C$9,0),0)))

    Expand the ranges as needed

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Values (2002)

    Thanks for all your 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
  •