+ Reply to Thread
Results 1 to 6 of 6

INDEX/MATCH multiple criteria and return value w/in Employee Schedule

  1. #1
    Registered User
    Join Date
    08-02-2018
    Location
    USA
    MS-Off Ver
    MS Excel 2010
    Posts
    4

    INDEX/MATCH multiple criteria and return value w/in Employee Schedule

    I have an employee schedule that shows the status of each employee defined by their "position" (i.e. day, day lead, night, etc.) or if they are on leave. Blanks represent off days. On the second tab of the worksheet, there are two functions I would like to utilize index/match for. The first is to be able to look up a lead position such "Day Lead" (SheetSchedule!$B$3:$G$14) and a specific day (SheetSchedule!$B2:$G$2) and return the name of the employee who will be working. The second is to look up the leave or off status and a specific day and return the name of the employee(s) who will be working.

    To look up the position, I have tried the following formula in various ways with no success:
    =INDEX(SheetSchedule!$A$2:$G$14,MATCH($C$2,SheetSchedule!$B$2:$G2,0),MATCH($C$3,SheetSchedule!$B$3:$G$14,0))

    For the second portion:
    =IF(ISERROR(INDEX(SheetSchedule!$A$2:$G$14,SMALL(IF(SheetSchedule!$B$3:$B$14=$F$3,ROW(SheetSchedule!$B$3:$B$14),ROW(SheetSchedule!1:1)),1)),"",INDEX(SheetSchedule!$A$2:$G$1,SMALL(IF(SheetSchedule!$B$3:$B$14=$F$3,ROW(SheetSchedule!$B$3:$B$14)),ROW(SheetSchedule!1:1)),1))

    The issue with this is that I can't figure out how to make it look up the date as well and then look within the range "SheetSchedule!$B$3:$G$14" to find what I put in F3.

    I'm still learning how to build complicated excel formulas so any help would be greatly appreciated! I attached a simplified version of my actual schedule but it should correspond with the fields I put above as well as an example of the output I want. Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: INDEX/MATCH multiple criteria and return value w/in Employee Schedule

    I propose to you the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    08-02-2018
    Location
    USA
    MS-Off Ver
    MS Excel 2010
    Posts
    4

    Re: INDEX/MATCH multiple criteria and return value w/in Employee Schedule

    Quote Originally Posted by Czeslaw View Post
    I propose to you the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thank you! This worked for my first problem. However, I couldn't use it for the second portion. I tried using this formula:
    =IF(ISERROR(INDEX(Schedule!$A$1:$G$14,SMALL(IF(Schedule!$B$3:$G$14=$F$3,ROW(Schedule!$B$3:$G$14)),ROW(Schedule!1:1)),0)),"",INDEX(Schedule!$A$1:$G$14,SMALL(IF(Schedule!$B$3:$G$14=$F$3,ROW(Schedule!$B$3:$G$14)),ROW(Schedule!1:1)),0))

    The problem with that is that I couldn't look up the date in $F$2 so it would just return every person that fit the criteria in $F$3. Any solutions for that?

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,432

    Re: INDEX/MATCH multiple criteria and return value w/in Employee Schedule

    I tested Czeslaw's formula in F4:F7 and it yielded the same three employees (1, 2 and 11) as those that had been manually placed in column N, which I assume is the expected outcome.
    The only thing needed is to change the references from C2 and C3 to F2 and F3 as in:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    08-02-2018
    Location
    USA
    MS-Off Ver
    MS Excel 2010
    Posts
    4

    Re: INDEX/MATCH multiple criteria and return value w/in Employee Schedule

    Quote Originally Posted by JeteMc View Post
    I tested Czeslaw's formula in F4:F7 and it yielded the same three employees (1, 2 and 11) as those that had been manually placed in column N, which I assume is the expected outcome.
    The only thing needed is to change the references from C2 and C3 to F2 and F3 as in:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Thank you, it worked! Idk why it didn't the first time but I got it! I appreciate the help! I will mark this thread solved

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,432

    Re: INDEX/MATCH multiple criteria and return value w/in Employee Schedule

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Index match with multiple criteria (text not return)
    By balajisrinath87 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-31-2017, 07:18 AM
  2. Index and match formula, attempting to return results with multiple criteria.
    By mahalek1976 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-02-2017, 12:43 AM
  3. [SOLVED] Index/Match with Multiple Criteria and Multiple return values
    By Brawnystaff in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-28-2015, 10:27 AM
  4. [SOLVED] Return Multiple values from a column with index and match, and search criteria
    By marcusduton in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 02-15-2015, 09:59 PM
  5. Replies: 3
    Last Post: 06-15-2012, 04:19 PM
  6. [SOLVED] Return a value:index/match against multiple criteria on another sheet
    By Southfish in forum Excel General
    Replies: 3
    Last Post: 04-30-2012, 11:08 PM
  7. Replies: 3
    Last Post: 08-17-2010, 02:54 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