+ Reply to Thread
Results 1 to 5 of 5

Need quick help with lookup with multiple criteria

  1. #1
    Registered User
    Join Date
    08-05-2014
    Location
    Kansas City, MO
    MS-Off Ver
    2007, 2010
    Posts
    3

    Need quick help with lookup with multiple criteria

    I have a spreadsheet that contains 2 sheets that I am trying to do a lookup on, I'm hung up on the greater/less than part of it, so any help I can get would be awesome

    Transactions Sheet
    Transaction Date/time
    Employee ID

    Event Sheet
    Event ID
    Employee ID
    Event Start Date/time
    Event End Date/time

    I am trying to add a new field to the Transactions Sheet that will show an Event ID from the Event Sheet if:
    Transactions Sheet-Employee ID matches Event Sheet-Employee ID
    Transactions Sheet-Transaction Date/time >= Event Sheet-Event Start Date/time
    Transactions Sheet-Transaction Date/time <= Event Sheet-Event End Date/time

    There will be multiple rows of data for each Employee ID in both sheets, even on the same date, so I am trying to use the time for each record to see if a Transaction took place during an Event from the second sheet
    Last edited by rjc88; 08-05-2014 at 12:00 PM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Need quick help with lookup with multiple criteria

    =INDEX('Event Sheet'!A:A,SUMPRODUCT(('Event Sheet'!B:B='Transaction Sheet'!B2)*('Event Sheet'!C:C<='Transaction Sheet'!A2)*('Event Sheet'!D:D>='Transaction Sheet'!A2)*ROW('Event Sheet'!B:B)))

    But this will fail if more than one event occurred in the time period.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    08-05-2014
    Location
    Kansas City, MO
    MS-Off Ver
    2007, 2010
    Posts
    3

    Re: Need quick help with lookup with multiple criteria

    I have included a sample spreadsheet of some of the data. That shows an event ID in the new field, but it is showing event IDs that would not match the lookup criteria (transaction not actually between the event start and end time)
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Need quick help with lookup with multiple criteria

    The formulas in the attached return the last match, if more than one exists. They are array formulas, which need to be entered using Ctrl-Shift-Enter instead of just enter.

    Sample Auditing 2.xlsx

  5. #5
    Registered User
    Join Date
    08-05-2014
    Location
    Kansas City, MO
    MS-Off Ver
    2007, 2010
    Posts
    3

    Re: Need quick help with lookup with multiple criteria

    Perfect, that is exactly what I needed

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Quick help with feeding/lookup
    By putitwhere in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-02-2013, 02:56 PM
  2. [SOLVED] Quick question about automatic row deletion (should be very quick and easy!!)
    By poptart141 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-09-2013, 12:56 PM
  3. quick lookup query
    By sukisuki28 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-16-2007, 06:47 PM
  4. Quick Question QUick Answer
    By ccraig61 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-25-2006, 09:34 PM
  5. Quick question - quick answer about assigning shortcut keys
    By funkymonkUK in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-13-2005, 06:05 AM

Tags for this Thread

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