CODEWITHSUNDEEP
excelexcel-formulaarray-formulas
Kevin Turi
Kevin Turi

Reputation: 51

Array formula to show value if it does not match

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.

Example

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!

Upvotes: 0

Views: 796

Answers (2)

Pavel_V
Pavel_V

Reputation: 1230

I found slightly different formula than what you are trying to use, but it might help you. (in the codes below "John" is in C2)

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B:B)/((B:B<>"off")*(A:A=$C$2)),COUNTA($A$1:A1))),"")

if you want it to remove also "vacation" then

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B:B)/((B:B<>"off")*(B:B<>"vacation")*(A:A=$C$2)),COUNTA($A$1:A1))),"")

in my localization I am using ";" instead of your "," (I already changed that in the examples) so there might be some other minor changes you need to apply to the code... btw you dont need to enter this as array formula.

Upvotes: 0

Mrig
Mrig

Reputation: 11702

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)),"")

Upvotes: 1

Related Questions