# Index/Match with Multiple Criteria Dates & Names

1. ## 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. ## 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. ## 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

4. ## 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. ## Re: Index/Match with Multiple Criteria Dates & Names

Try the following:
Formula:
`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.

6. ## 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. ## 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.
Let us know if you have any questions.

8. ## Re: Index/Match with Multiple Criteria Dates & Names

Attachment 687137

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

9. ## Re: Index/Match with Multiple Criteria Dates & Names

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

10. ## Re: Index/Match with Multiple Criteria Dates & Names

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

11. ## 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:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

12. ## 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?

13. ## 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.

14. ## 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()
Answer = MsgBox("Do you want to Validate (rotate) the list? This will take about one min.", vbYesNo)
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. ## Re: Index/Match with Multiple Criteria Dates & Names

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