Have you ever wanted to use the IF, AND, OR logical functions together in an array in Google Sheets? Well, in a standard way, it isn’t possible. But fear not! I have two workarounds for you.
Non-Array Formula
The screenshot below shows a formula that combines the IF, AND, OR logical functions in a non-array form. It’s called the combined use of logical IF, AND, OR.
It checks if the department is “Planning” and the allocation is either “Project 1” or “Project 2”, then returns the name of the employee.
=if(and(A2=$E$2,OR(C2=$E$3,C2=$E$4)),B2,"")But what if you want a single formula that expands the results down? Well, that’s where array formulas come in. However, you can’t use the IF, AND, OR logical functions together in an array in a standard way… unless you use a lambda helper function.
IF, AND, OR in Array Using Operators Addition (OR) and Multiplication (AND)
One solution is to use the addition (OR) and multiplication (AND) operators in place of the logical IF, AND, OR functions. This allows the formula to work in an array form.
=ArrayFormula(if((A2:A7=$E$2)*((C2:C7=$E$3)+(C2:C7=$E$4))>0,B2:B7,))The above formula checks if the department matches the criteria in cell E2, and if at least one project matches the test. If both conditions are met, it returns the employee name.
MAP Lambda to Expand IF, AND, OR Formula Results in Google Sheets
Another option is to use the MAP lambda helper function. This is a great choice when you need to map multiple columns in a table.
=map(A2:A7,B2:B7,C2:C7,lambda(a,b,c, if(and(a=$E$2,OR(c=$E$3,c=$E$4)),b,"")))With the lambda function, you no longer have to rely on operators to use IF, AND, OR in an array in Google Sheets. It provides a cleaner and more efficient way to achieve the desired results.
So there you have it! Two working solutions to use the IF, AND, OR logical functions in an array in Google Sheets. Whether you choose to use operators or the lambda function, you can now expand your formulas and get the results you need.
And if you want to learn more about Google Sheets and other helpful tips, be sure to check out Crawlan.com. It’s your go-to source for all things Google Sheets and beyond. Happy sheeting!
