Filtering by Multiple Criteria/Conditions

I have a column in TableA that uses FILTER with multiple conditions that are OR-ed together. I am in Table A but need to filter Table B. It essentially goes like this:

A column in TableA =FILTER(TableB, "([Standard]=TableB[Item1][Standard] OR [Standard]=TableB[Item2][Standard] OR [Standard]=TableB[Item3][Standard] OR [Standard]=TableB[Item4][Standard]) AND [Name]=TableB[Name]")

This worked for some rows in TableB and not for others....not sure why. I'm also assuming that writing [Standard] is the same as TableA[Standard] as TableA is where I am writing the Filter function. However, I noticed that the Filter function returns a different result if I just write [Standard] vs TableA[Standard]. So, I do not think I am understanding the item referencing correctly...

To try to debug, I removed all of the [Standard]=TableB[ItemN][Standard] conditions and added them back into the filter one by one and noticed that when I add in certain conditions it actually removes results that are returned. For example, I will add in 3 of the conditions to the filter and that will return 5 results. But when I add the 4th condition back in I only get 3 results. Since these are OR-ed together, I do not understand why adding a condition would decrease the number of results returned because even if the condition is false, as long as at least one of the conditions is true then a result should be returned.

So, it seems that my ultimate problem is that having certain conditions gets rid of the results that are returned from the filter and I don't understand why. Perhaps I am misunderstanding the OR or AND operator?

I hope all of that makes sense. Thank you!

Hi @Lily-535e, welcome to the community! :smiley: :honeybee:

Thanks for your details here. If I'm understanding your goals correctly, you're looking to filter Table B where both a name and standard of each item match those defined in TableA.

If that be the case, your filter formula in TableA wouldn't need the ORs to list each item, since the filter will check each row to see if the criteria matches. Meaning your formula would be:

=FILTER(TableB,"TableB[Standard]=[Standard] AND TableB[Name]=[Name]")

Let us know if that helps, and if you have any other questions! :slight_smile:

1 Like

A post was split to a new topic: Automation formula help