Forum Discussion
Filter list with OR condition
=FILTER(A2:A22,BYROW(A2:A22,LAMBDA(r,COUNTIF(B2:B15,r)+COUNTIF(C2:C17,r)))=1)
Alternatively you can use this formula which returns the numbers that are either in list 2 or list 3. According to the sample data the assumption is that there are no duplicates within a list.
Did you mean "filter the numbers from list one, with condition that it is present in list two OR list three"?
Or "filter the numbers from list three, with condition that it is present in list one OR list two"?
Or something else?
- Hi Hans,
I'm sorry I meant filter the numbers from list one, with condition that it is present in list two OR three.=FILTER(A2:A22,BYROW(A2:A22,LAMBDA(r,COUNTIF(B2:B15,r)+COUNTIF(C2:C17,r)))=1)
Alternatively you can use this formula which returns the numbers that are either in list 2 or list 3. According to the sample data the assumption is that there are no duplicates within a list.
You can use this formula:
=FILTER(ListOne, ISNUMBER(XMATCH(ListOne, ListTwo))+ISNUMBER(XMATCH(ListOne, ListThree)), "")
In your example, it will return all items of list one.
HansVogelaar A variant for OR criteria in this particular scenario 😉
=FILTER(List1, ISNUMBER(XMATCH(List1, VSTACK(List2, List3))))EDIT/UPDATE:
MenneW If you what you're really asking for is to FILTER with exclusive OR criteria (present in List1 OR List2, but NOT both), the UNIQUE function can be used with the optional [exactly_once] argument set to TRUE:
=FILTER(List1, ISNUMBER(XMATCH(List1, UNIQUE(VSTACK(List2, List3),, TRUE))))If there's a risk that duplicate values can exist within either List2 or List3, you may also want to consider using the UNIQUE function on each list individually (to return distinct items only) before merging the two lists with VSTACK:
=FILTER(List1, ISNUMBER(XMATCH(List1, UNIQUE(VSTACK(UNIQUE(List2), UNIQUE(List3)),, TRUE))))
