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
Bookmarks