Is it possible to create a filter in Excel across multiple columns with AND/ORs, or what's the suggested way to do this? For example:
WHERE(Name = "Tom" AND country in ['US', 'CA']) OR (Age > 14 and country not in ['FR', 'DE'])If not, how does one normally do a SQL-like filter clause in Excel, or is this considered too much of an edge case for Excel to consider?
Best Answer
You may try:
=OR(AND(Name="Tom", OR(country="US", country="CA")),AND(Age > 14, AND(country<>"FR", country<>"DE")))The trick is that using AND/OR with Excel requires nesting the logic, which is different than how SQL does it.
You may replace Name and country with the actual Excel cells which contain that particular data.
In Excel, AND and OR are functions. See Excel / Formulas and functions.
Example:
=OR(AND(B2="Tom", OR(B3="US", B3="CA")), AND(B4>14, B3<>"FR", B3<>"DE")))
With the new Dynamic Array formulas (available in most versions of Office 365), the list can be filtered with the Filter() function and the result is displayed outside of the list.
The screenshot shows Tim Biegeleisen's formula with conditional formatting to highlight the TRUE values before filtering the table on that column. Next to that is the result of ONE formula in cell H2, which has automatically spilled to the right and down. For ease of identification, I have included an index column.
Note that AND logic is built with the multiplication operator, whereas OR logic uses the addition operator.
=FILTER(Table1,((Table1[Name]="Tom")*((Table1[Country]="US")+(Table1[Country]="CA")))+((Table1[Age]>14)*(Table1[Country]<>"FR")*(Table1[Country]<>"DE")))