+ Reply to Thread
Results 1 to 3 of 3

Lookup name with multiple criteria & between 2 date ranges

Hybrid View

  1. #1
    Registered User
    Join Date
    03-20-2020
    Location
    Maywood, USA
    MS-Off Ver
    2016 & 365
    Posts
    13

    Lookup name with multiple criteria & between 2 date ranges

    BACKGROUND: I am working on trying to calculate surgeon's credited minutes. Doctor's are able to essentially give up their credits to another doctor for the same surgery area for the same day that they give up. E.g. If Tina gives time up on 7/4 to Mark. Then mark gets her credit for 7/4.

    ISSUE: I need a formula that can lookup the name of the surgeon (RELEASE TO DR... 1) using the following info

    1) date [between "start date" & "end date"]
    2) surgeon who gave up the time [SURGEON]
    3) service location [SERVICE LOCATION]

    In the example in the workbook. IF on 7/4/2019 Tina gave up time in the AMBULATORY, the result would pull up "MARK."

    I attempted and index and match function, but it ran very slowly since the formula is 365-366(total number of days in a year)*(number of surgeons long). I am trying to avoid using array solution because of that.

    Any help or insight would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    12-04-2020
    Location
    Bangkok
    MS-Off Ver
    365
    Posts
    61

    Re: Lookup name with multiple criteria & between 2 date ranges

    Please try,

    Formula: copy to clipboard
    N3=INDEX($E$3:$E$18,MATCH(1,(K3>=$A$3:$A$18)*(K3<=$B$3:$B$18)*($C$3:$C$18=L3)*($D$3:$D$18=M3),))


    Note that in K3 to K5, I changed the year from 2020 to 2019, in order to be in the range in the raw data table.
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Lookup name with multiple criteria & between 2 date ranges

    Pl see file.
    In K3 then copied across. Without using HELPER column

    =IFERROR(INDEX(B$3:B$18,AGGREGATE(15,6,ROW($B$3:$B$18)/($E$3:$E$18<>""),ROWS($K$3:$K3))-ROW($A$2)),"")

    For faster approach.
    In K12 then copied across. Using HELPER column I

    =IFERROR(INDEX(B$3:B$18,SMALL($I$3:$I$18,ROWS($K$3:$K3))-ROW($B$2)),"")
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 12-31-2020 at 05:38 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ 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. [SOLVED] Booking Calender - Lookup multiple bookings across date ranges for
    By Smoestar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-12-2017, 07:38 AM
  2. Lookup and match multiple ranges point criteria
    By yllew in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-20-2016, 12:54 AM
  3. Lookup Function Between Multiple Date Ranges
    By nelobynature in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-20-2013, 04:56 PM
  4. LOOKUP Having one Exact matching criteria and one between 2 date ranges.
    By Grahame Hamilton in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-24-2012, 06:28 AM
  5. Replies: 2
    Last Post: 05-10-2012, 10:38 AM
  6. Replies: 2
    Last Post: 09-21-2011, 04:19 PM
  7. Count Multiple Criteria within Multiple Date Ranges
    By E6BAV8R in forum Excel General
    Replies: 3
    Last Post: 12-08-2010, 05:06 PM

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