+ Reply to Thread
Results 1 to 5 of 5

Multi-lookup

  1. #1
    Registered User
    Join Date
    10-27-2010
    Location
    Swansea, Wales
    MS-Off Ver
    Excel 2007
    Posts
    47

    Multi-lookup

    Hello all,

    I'm having trouble figuring out how to lookup multiple values.

    In the attached file, I have a look up in place to return values from the "data" tab based on the date entered in B4 in the "summary" tab.

    This works fine and I use this method on a larger scale for other sets of data but I now want to return a sum of values for a range of user selected dates.

    On the right hand side of the "summary" tab is pretty much what I'm after. The user will input a from and to date (1-31) and I need a formula that looks up all the data for those dates.

    All help greatly appreciated!!

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Multi-lookup

    Hello,

    change your data labels in Summary!J7:J9 to be the same text as Data!A6:A8, namely

    Time run
    Breakdown
    Output

    Then use in Summary cell M7:

    Please Login or Register  to view this content.
    copy down.

    The expected result for Output in your sample data is wrong. If you want to sum up days 1 to 5 it should result in 47,500, not 32.5.

    Alternatively, you have some explaining to do as to how to arrive at 32.5.

    cheers

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Multi-lookup

    you could try in m7
    =SUM(OFFSET(Data!B6,,$K$4-1,,$N$4-$K$4+1))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Contributor
    Join Date
    05-29-2009
    Location
    Kalgoorlie Australia
    MS-Off Ver
    Excel 2007/10
    Posts
    251

    Re: Multi-lookup

    Please let me know if this is ok
    I had to add in some working rows.
    regards
    Peter
    PS I also changed some of the data in row 13 just to make sure the formula works, so thisis why it is different to what you originally posted.
    Attached Files Attached Files
    Last edited by peterjuhnke; 10-27-2010 at 07:54 AM.

  5. #5
    Registered User
    Join Date
    10-27-2010
    Location
    Swansea, Wales
    MS-Off Ver
    Excel 2007
    Posts
    47

    Thumbs up Re: Multi-lookup

    Wow!

    Thank you all for your replies!

    Firstly let me apologise for the lack of consistency in the original spreadsheet. I cobbled it together quite quickly so rushed a few things!

    peterjuhnke - thanks for the time and effort with the spreadsheet. This is a good solution but would involve redesigning the data tab and as we have 20 lines this would be quite time consuming!

    martindwilson - this seems to work when I tested it but teylyn got there first!

    Thanks teylyn - this worked like a dream and I've been able to apply the formula to the other lines and fields I need to total - perfect!!

    I've not used match, index or offset functions before so I will be having a play around with these to see if they can help with other problems.

    Thanks again, I'd never have cracked this on my own!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1