What I am trying to do is find a formula that I have been unsuccessful in locating and alter it to what i need it for. What I have is a list where names are duplicated throughout with different values on either side of the name. I would like to be a way where specific thing happen.
I will try to explain and give an example.
In Column A I have all of the names, not in any particular order, just all jumbled. In column B I have a time or the value "off" if someone is not scheduled for that day. What I would like to do is is find each instance in which "John" is working and return the value if it does not say that he is off or vacation.
The formula I have that shows every instance is here. This would reside in D2: Code:
={IFERROR(INDEX('$B:$B,MATCH(0,IF($C$2=$A:$A,COUNTIF($D$1:D1,$B:$B),""),0)),"")}
The formula I tried but have not had any success is here:
Code:
={IFERROR(INDEX('$B:$B,MATCH(0,IF(NOT(OR("Off"=$B:$B,"Vacation"=$B:$B)),COUNTIF($D$1:D1,$B:$B),""),0)),"")}
Any help would be appreciated!
Made some changes in your formula:
=IFERROR(INDEX($B:$B,MATCH(0,IF(($C$2=$A:$A)*("Off"<>$B:$B)*("Vacation"<>$B:$B),COUNTIF($D$1:D1,$B:$B),""),0)),"")
This is an array formula so commit it by pressing Ctrl+Shift+Enter.
Referring whole column (like $A:$A or $B:$B) in array formula makes execution very slow and hence is not advisable, instead use the range with data such as
=IFERROR(INDEX($B$1:$B$9,MATCH(0,IF(($C$2=$A$1:$A$9)*("Off"<>$B$1:$B$9)*("Vacation"<>$B$1:$B$9),COUNTIF($D$1:D1,$B$1:$B$9),""),0)),"")