+ Reply to Thread
Results 1 to 15 of 15

Index/Match with Multiple Criteria Dates & Names

  1. #1
    Registered User
    Join Date
    04-29-2020
    Location
    Schenectady
    MS-Off Ver
    365
    Posts
    10

    Index/Match with Multiple Criteria Dates & Names

    Hello, I have a rotation list of employees and another column that lists out the employees who are out of office on a particular date. I need to create a Index/match function that will retrieve any random employee from the rotation list that does not match the name of the employees that are out of the office on that particular day.

    I believe one of these array formulas are very close but it is still not retrieving the desired value:
    =IFNA(INDEX($F$3:$F$8,MATCH(TRUE,(C3<>$F$3:$F$8)*(B3&" "&C3<>$I$3:$I$4),0)),"")

    =INDEX($E$3:$E$8,MATCH(FALSE,(B4&" "&C4<>$H$3:$H$8)*0))

    See the image below & Example Document of what I've got so far:

    Example Index-Match - Copy.xlsx

    Example Index - Match.JPG

  2. #2
    Registered User
    Join Date
    03-17-2010
    Location
    karachi
    MS-Off Ver
    Excel 2003
    Posts
    82

    Re: Index/Match with Multiple Criteria Dates & Names

    =INDEX($E$3:$E$8,SMALL(IF($F$3:$F$8=B3,ROW($F$3:$F$8)-ROW($F$3)+1),COUNTIF($B$3:B3,B3))) put in d3

  3. #3
    Registered User
    Join Date
    04-29-2020
    Location
    Schenectady
    MS-Off Ver
    365
    Posts
    10

    Re: Index/Match with Multiple Criteria Dates & Names

    I think this is somewhat close but I actually want it to retrieve a name from E3:E8 that does not match someone who is out of the office on that date in G3:G8. So it will select the first person who is available (randomly selected is fine) and will retrieve that name.

    I was thinking maybe that concatenating the value in column H could be the Look up value we don't want the return result to equal.

    Really appreciate your help have been trying to figure this out for a while
    Last edited by johncap; 07-15-2020 at 03:33 PM.

  4. #4
    Registered User
    Join Date
    04-29-2020
    Location
    Schenectady
    MS-Off Ver
    365
    Posts
    10

    Re: Index/Match with Multiple Criteria Dates & Names

    Maybe something like this just don't know how to call on the look up value any thoughts?
    =INDEX($F$3:$F$8,MATCH(1,(B5=G3:G4)*(Lookup.Value<>H3:H4),0))

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,800

    Re: Index/Match with Multiple Criteria Dates & Names

    Try the following:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that this will sometimes produce duplicate outputs, so that you may want to press the F9 key until a list without duplicates is displayed.
    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.

  6. #6
    Registered User
    Join Date
    04-29-2020
    Location
    Schenectady
    MS-Off Ver
    365
    Posts
    10

    Re: Index/Match with Multiple Criteria Dates & Names

    Thank you for your response. This is super close Is there any way to add more criteria to the value it pulls? Even after toggling F9 it retrieves a value of someone who is out of the office. For instance If there are multiple employees out on 7/3 the value the formula should retrieve cannot equal any of those names that are out of the office, rather it would pull someone from the rotation list that are not out of the office on that day. For 7/3 the values it cannot equal are Jack,Frank,Fred,Tammy. See the attached photo:Attachment 687061

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,800

    Re: Index/Match with Multiple Criteria Dates & Names

    Selecting Attachment 687061 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"
    I can not replicate where the formula "retrieves a value of someone who is out of the office" using the formula and the file attached to post #1.
    Please upload an .xlsx file that illustrates the issue.
    Let us know if you have any questions.

  8. #8
    Registered User
    Join Date
    04-29-2020
    Location
    Schenectady
    MS-Off Ver
    365
    Posts
    10

    Re: Index/Match with Multiple Criteria Dates & Names

    Attachment 687137

    Please see attached, the formula is in D4. Thanks again!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,843

    Re: Index/Match with Multiple Criteria Dates & Names

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  10. #10
    Registered User
    Join Date
    04-29-2020
    Location
    Schenectady
    MS-Off Ver
    365
    Posts
    10

    Re: Index/Match with Multiple Criteria Dates & Names

    I believe the file should be fixed now sorry about that.

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,800

    Re: Index/Match with Multiple Criteria Dates & Names

    Still no file attached other than the original in post #1.
    Looking back at post #6 I thought that only Frank and Tammy were excluded for 7/3.
    I thought that Jack and Fred were excluded for 7/2
    I mistakenly posted the formula for cell D4. The formula for cell D3 should read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  12. #12
    Registered User
    Join Date
    04-29-2020
    Location
    Schenectady
    MS-Off Ver
    365
    Posts
    10

    Re: Index/Match with Multiple Criteria Dates & Names

    Its still retrieving values of people that are out of office. The list of days/names will also update overtime, It needs to assign someone from the rotation list to be on duty that is not out of the office for that day.

    I was thinking maybe something like this: =INDEX(E$3:E$8,AGGREGATE(14,6,(ROW(G$3:G$8)-ROW(G$2))/(F$3:F$8<>B3)*(G3:G8<>D3),RANDBETWEEN(1,COUNT(F$3:F$8)-COUNTIFS(F$3:F$8,B3,G3:G8,"<>D3")))) In Cell D3

    But this still is not returning the desired value. Maybe would Index/Match/Rand produce the proper value?

    Thanks again for your help

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,800

    Re: Index/Match with Multiple Criteria Dates & Names

    Here is the file with the formula applied.
    Please let us know if there are names in column D which should not be there.
    Let us know if you have any questions.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    04-29-2020
    Location
    Schenectady
    MS-Off Ver
    365
    Posts
    10

    Re: Index/Match with Multiple Criteria Dates & Names

    Thank you for all your help your answer did work but I ended up creating a loop using a offset function it would add a offset value to it until the criteria were met (the one column had a count if referencing the out of office list) which avoids duplicate values and then hard codes it on to the schedule.

    Sub Loop()
    Dim Answer As VbMsgBoxResult
    Answer = MsgBox("Do you want to Validate (rotate) the list? This will take about one min.", vbYesNo)
    If Answer = vbYes Then
    Range("BN4:BN125").Value = 0

    Dim MyRange As Range, cell As Range

    Set MyRange = Range("BN4:BN125")

    For Each cell In MyRange

    If cell.Offset(0, -1).Value <> 0 And cell.Offset(0, -4).Value = 1 Then

    Do
    cell.Value = cell + 1
    Loop Until cell.Offset(0, -1).Value = 0
    Dim MyRange2 As Range
    Set MyRange2 = cell.Offset(0, -4)
    Dim MyRange3 As Range
    Set MyRange3 = cell.Offset(0, -3)
    MyRange2.Copy
    MyRange3.PasteSpecial Paste:=xlPasteValues
    End If

    Next cell
    End If
    End Sub

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,800

    Re: Index/Match with Multiple Criteria Dates & Names

    Glad that you got a solution. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. 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 and Match help with names and calendar dates
    By janger in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-19-2019, 09:01 PM
  2. Sumif or Index/Match on Multiple Criteria including Dates to Month?
    By patrick1024 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2015, 11:00 PM
  3. [SOLVED] Index match dates and mult criteria
    By lejanco in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-03-2015, 12:38 PM
  4. [SOLVED] Index Match with dates and multiple criteria
    By lejanco in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-22-2014, 12:43 PM
  5. [SOLVED] Sum Index Match Dates Between Multiple Date Criteria
    By garrett.grillo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-30-2014, 08:54 PM
  6. [SOLVED] Combining 3 Formulas: Return all Names that Match Criteria Using Index/Match
    By bchilme in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-29-2014, 09:28 AM
  7. Multiple Criteria Index/Match Function in VBA Looking Up Dates/Strings/Numbers
    By nadstradamus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-21-2014, 12:35 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