How to Use IF, AND, OR in Array in Google Sheets

In a standard way, it’s not possible to use the IF, AND, OR in Array in Google Sheets. But we have two workarounds.

The workaround solutions are using operators (+ [addition] and * [multiplication]) or a lambda helper function.

I’m talking about how to use the IF, AND, OR logical functions together in an Array.

I’ve already tutorials on using the OR, AND logical functions in an array in two separate posts. But those are individual array use. Here are them.

How to Use IF, AND, OR in Array in Google Sheets

Non-Array Formula

The formula in the screenshot below shows the non-array use of IF, AND, OR logical functions in combined form.

It’s called the combined use of logical IF, AND, OR.

In cell F2, I have the following combined IF, AND, OR logical test formula, which returns the name of the employee whose department is “Planning” and whose allocation is either “Project 1” or “Project 2”.

=if(and(A2=$E$2,OR(C2=$E$3,C2=$E$4)),B2,"")

I’ve copied it down.

I want a single formula in cell F2 that expands results down.

To create such an array formula, you should know how to use IF, AND, OR in Array Formula in Google Sheets. But before that, let me explain the above non-array formula for newbies.

The above formula is like this. If “Department” is Planning (A2=$E$2) and “Allocation” is either Project 1 or Project 2 (OR(C2=$E$3, C2=$E$4)), I want the formula to return the name of that employee in B2.

The formula in the above screenshot works perfectly. But in an array form, it won’t work! We can’t use the above IF, AND, OR logical functions in array form as below.

=ArrayFormula(if(and(A2:A7=$E$2,OR(C2:C7=$E$3,C2:C7=$E$4)),B2:B7,""))

Below you can find two working solutions. One uses the addition (OR) and multiplication (OR) operators, and the other uses the MAP lambda function.

IF, AND, OR in Array Using Operators Addition (OR) and Multiplication (AND)

There are some benefits of using an Array formula.

If you copy a single formula in many cells, when you want to make changes to it, the editing can cause a headache for you.

Below is an array formula that expands the results.

=ArrayFormula(if((A2:A7=$E$2)*((C2:C7=$E$3)+(C2:C7=$E$4))>0,B2:B7,))

As I’ve mentioned at the beginning of this Google Sheets Tutorial, you can’t use logical IF, AND, OR together in an array in a standard way except using a lambda.

This array formula is an example of how to use IF, AND, OR in Array in Google Sheets.

It takes multiplication and addition signs instead of AND, OR in IF.

I know I should shed some light on this formula so you can use it.

Anatomy of the Formula

See the extracted part of the formula IF, AND, OR array formula in Google Sheets.

=(A2:A7=$E$2)*((C2:C7=$E$3)+(C2:C7=$E$4))>0

Note: If you use the extracted formula independently for testing, use it as below.

=ArrayFormula((A2:A7=$E$2)*((C2:C7=$E$3)+(C2:C7=$E$4))>0)

It is the core of the above IF, AND, OR combined logical array formula.

The vivid cyan-blue part of the above formula returns TRUE or FALSE based on the match.

The value of Boolean TRUE is 1, and 0 is FALSE. It checks the field “Department” with criteria in E2.

If the department matches, this part will return TRUE, and the value is 1. We are expecting here the value 1.

Now about the vivid red colored part in the formula. It checks the “Allocation” field with two criteria: Project 1 and Project 2.

The result can be TRUE+FALSE, FALSE+TRUE, or FALSE+FALSE, which means 1+0, 0+1, or 0+0.

Here again, we are expecting the value 1. That means at least one project should match the test.

So our final formula multiplies these two logical parts (vivid cyan-blue and vivid-red). As already said, we want a 1*1=1 result.

The IF logical part tests whether the above output is >0.

If >0, the formula returns the employee name, else blank.

This way, we can use logical IF, AND, OR in Array in Google Sheets. Enjoy.

MAP Lambda to Expand IF, AND, OR Formula Results in Google Sheets

The MAP lambda helper function would be our perfect pick here, not the BYROW, as we have to map multiple columns in the table.

You are now not required to depend on the operators to use IF, AND, OR in an array in Google Sheets.

The AND and OR is capable of expanding using it.

Pick our non-array formula (see the image above) and convert it to a lambda formula as below.

=map(A2:A7,B2:B7,C2:C7,lambda(a,b,c, if(and(a=$E$2,OR(c=$E$3,c=$E$4)),b,"")))

That’s all!

33 COMMENTS

Prashanth January 1, 2023 At 12:03 pm

Hi, Les Mint,

Thanks for your valuable feedback.

Happy New Year 2023 – may you have good health, experiences, and company.

Benny Harris October 19, 2022 At 12:33 am

Hi Prashanth,

Any advice for getting this ARRAY to work? The formula works, but the ARRAY cannot be used with AND/OR as you’ve described.

=ARRAYFORMULA(IF(OR(ISBLANK(B2),ISBLANK(C2),ISBLANK(E2), ISBLANK(G2),ISBLANK(H2)),0,1))

Thanks!

- Prashanth October 20, 2022 At 3:14 pm
  
  Hi, Benny Harris,
  
  This formula may do the trick.
  
  =ArrayFormula(if(len(B2:B10)*len(C2:C10)*len(E2:E10)* len(G2:G10)*len(H2:H10)>0,1,0))
  
  You can also use a MAP lambda formula.
  
  =map(B2:B10,C2:C10,E2:E10,G2:G10,H2:H10,lambda(b,c,e,g,h, if(or(isblank(b),isblank(c),isblank(e),isblank(g),isblank(h)),0,1)))
  
  I’ve updated my tutorial to include a MAP solution w.r.t. the example there.
  
Allison B August 9, 2022 At 9:44 pm

Hi Prashanth!

I’m hoping you can help.

I have a Google Sheet with multiple tabs.

If the ID number in column D of one tab matches an ID number in column E of another tab, I want a YES or TRUE or anything in column G of the first tab.

How do I do this?

- Prashanth August 10, 2022 At 10:48 am
  
  Hi, Allison B,
  
  Assume the tabs in question are Sheet1 and Sheet2.
  
  Empty Sheet1 column G and enter the following IF and MATCH combo in G1.
  
  =ArrayFormula(ifna(if(match(D:D,Sheet2!E:E,0),"YES",)))
  
Wyatt August 6, 2022 At 3:07 am

I know I cannot use AND() in an Array Formula, but if I wanted to turn the formula below into an Array, any tips on how to do that?

=(IF(AND(J:J="- Not Field Trained via CFT -", I:I="- Currently not Peer Supported -"),"No","Yes"))

- Prashanth August 8, 2022 At 10:41 am
  
  Hi, Wyatt,
  
  You can try this formula.
  
  =ArrayFormula(if(len(J:J&I:I),if((J:J="- Not Field Trained via CFT -")* (I:I="- Currently not Peer Supported -"),"No","Yes"),))
  
  - Wyatt August 8, 2022 At 6:56 pm
    
    That worked!!! Thank you! What is LEN, if you do not mind me asking?
    
    - Prashanth August 9, 2022 At 9:33 am
      
      Hi, Wyatt,
      
      To test whether the cell has any character. The LEN returns the length of the string in that cell.
      
Chris May 18, 2022 At 11:47 pm

I feel like this is the exact place I need to find out how to turn this IF AND into an ARRAY, but I keep coming up short.

Here is the formula that I want to convert:

=IF(AND(J2>0,U20)*(U2:U0,""))

I’d appreciate any help!

- Prashanth May 21, 2022 At 7:19 am
  
  Hi, Chris,
  
  I’m not clear. Can you please leave the URL of your example sheet, if any, below in your reply?
  
Paul April 29, 2022 At 9:43 pm

Hello Prashanth,

Here’s a screenshot of my google sheet derived from a google form: — link removed by admin —

I need to auto-populate the ‘Remarks” column with either a “Pass” or “Fail” value based on the answers on all Q columns.

I tried arrayformula for IF, AND, OR. But as you mentioned, they don’t work.

Please enlighten me on how? 🙂

Thanks!

- Prashanth May 1, 2022 At 10:28 am
  
  Hi, Paul,
  
  Thanks for the screenshot.
  
  I have noted the values/answers in columns B, C, D, E, and F.
  
  In column G, you want the Pass/Fail in each row.
  
  But what are the conditions for Pass or Fail?
  
  - Paul May 2, 2022 At 12:32 pm
    
    Thank you for the reply Prashanth.
    
    Answers to questions should be satisfied.
    
    Example: Q1=Yes, Q2=NSW, Q3>=59, Q4=Yes, Q5=No/Unsure.
    
    If all is true, remarks will be “Passed.”
    
    Thanks,
    
    Paul
    
    - Prashanth May 2, 2022 At 3:44 pm
      
      Hi, Paul,
      
      Thanks for your explanation.
      
      Empty the range G2:G. Then, please insert the following array formula in G2.
      
      =ArrayFormula(if(B2:B="",, if((B2:B="Yes")*(C2:C="NSW")*(D2:D=59)*(E2:E="Yes")* ((F2:F="No")+(F2:F="No/Unsure")),"Passed","Failed") ))
      
Brian Koehn April 13, 2022 At 11:23 pm

Brilliant! Thank you.

Les Sullivan January 27, 2022 At 8:27 pm

Prashanth, that worked. Thank you.

Les Sullivan January 24, 2022 At 9:06 am

Hi Scott,

The simple formula I had was working fine. But now we have added another factor to it.

I am currently using =Countifs($i$3:$I$154, L10, $g$3:$g$154, ">=500")

I cannot figure out how to manipulate it to pump it against column J to take only = “Loss.”

Any help would be greatly appreciated.

- Prashanth January 25, 2022 At 8:36 am
  
  Hi, Les Sullivan,
  
  Try this first.
  
  =Countifs(I3:I154, L10, G3:G154, ">=500",J3:J154,"Loss")
  
  If not working as expected, please share the URL of the sheet in problem or a sheet with sample data below “Reply to this comment”.
  
  I will keep your “reply” unpublished.
  
Scott Miller August 11, 2021 At 8:43 am

Hello,
I’m trying to create an if/and formula. Based on the criteria select, I want it to display a table. Not sure how to make that work with an array formula.

- Prashanth August 12, 2021 At 4:33 pm
  
  Hi, Scott Miller,
  
  Can you share an example sheet below?
  N.B. – I won’t publish it.
  
Steve March 21, 2021 At 12:46 pm

Hi Prashanth,

Thanks for this site, it has been so incredibly helpful, my Sheets skills have improved dramatically thanks to your very clear and concise explanations. Things that other sites have suggested very complicated solutions to, or just said are not possible, you have offered a simple solution.

Really appreciate it.

- Prashanth March 21, 2021 At 2:42 pm
  
  Hi, Steve,
  
  Thanks for your feedback!
  
AW January 6, 2021 At 1:42 am

This guide came in clutch. Thanks!!

Viri October 8, 2020 At 10:43 pm

Hi Prashanth,

I have the day divided into 2 sections “Turno 1 and “Turno 2” depending on the time printed on the ticket at the moment the order was made.

So Im trying the formula to return in witch one is it, 1 or 2 I have it l like this:

=arrayformula(if(and(AD2:AD>time(11,0,0),AD2:ADtime(17,0,0)),"Turno 2")))

Based on what I’ve found on google I’ve modified a couple of times but nothing seems to work it keeps coming wrong or with an error.

Do you think there’s a way to make it work? or should I give up?

Thanks!

- Prashanth October 9, 2020 At 9:35 am
  
  Hi, Viri,
  
  Please clarify the time period.
  
  Like;
  
  11:00:00 to 17:00:00 – Turno 2
  17:00:00 to 22:00:00 – Turno 1
  
  So that I can write the formula. You might want to use multiple IF instead of AND within IF.
  
ERIKA RUPERT September 2, 2020 At 1:26 am

I would like to create an ArrayFormula which adds up hours based on words in a cell for the row, not the column, but I do want it to do the same function for the entire sheet.

So I would like it to look at all the rows starting in column F10 - AG10 for the words MOECS Upload or CofC and then add the hours the person would receive located in row 6 of the same column those words are in. I can share my sheet if you need to see it.

- Prashanth September 2, 2020 At 12:14 pm
  
  Hi, Erika Rupert,
  
  The hours are in row # 6 (F6:AG6).
  
  In row # 10 (AH10), you want to sum the above hours if the values in row # 10 (F10:AG10) are MOES or CofC.
  
  The same you want to repeat in row # 11, row # 12, and so on.
  
  I’ve two options. One is a drag-drop formula and the other is an array formula. But first, you must format your row # 6. Instead of 3 Hrs, just enter 3, so that we can sum.
  
  Drag-drop Formula (Formula should be keyed in cell AH10 and then drag-down (copy-paste down):
  
  I think, here instead of using IF, AND, OR logical functions in array use, we can use FILTER and REGEXMATCH combo.
  
  =sum(ifna(filter($F$6:$AG$6,regexmatch(F10:AG10,"MOECS|CofC"))))
  
  Self Expanding Formula (Formula should only be keyed in cell AH10 (AH11:AH should be empty):
  
  =ArrayFormula(mmult(if((F10:AG343="MOECS")+(F10:AG343="CofC")=1,F6:AG6,0),transpose(column(F6:AG6)^0)))
  
  In this formula, the MMULT matrix1 is actually a combination of IF, OR logical in array use.
  
Evan November 26, 2019 At 9:18 am

Hi there,

I just want to do an array on only IF/ AND.

My formula looks like this =ArrayFormula(IF(AND(AK$2:AK=Key!C$7, AL2:AL="No",AM2:AM="Easy"),Key!F$37))

How would I make it work, please?

E

- Prashanth November 26, 2019 At 10:00 am
  
  Hi, Evan,
  
  The AND logical function won’t work with the ArrayFormula as above. Here is the correct approach.
  
  =ArrayFormula(IF((AK$2:AK=Key!C$7)*(AL2:AL="No")*(AM2:AM="Easy")>0,Key!F$37,))
  
  I have written this formula without seeing your data. So if not working you can share a screenshot of your data.
  
  Best,
  
Sri Ram November 6, 2019 At 10:16 pm

Hi,

I want to find the cash spent by a person. I’m not sure what formula I need to use?

Can you please help with that?

Requirement:
Column A, Column B
Name1, 100
Name2, 300
Name1, 500

I want to find the total amount spent by a person (say Name 1)… How can I calculate it?

- Prashanth November 7, 2019 At 9:20 am
  
  Hi, Sri Ram,
  
  It has nothing to do with IF, AND, OR functions or combination. Still, here are my solutions.
  
  There are several functions that you can use to solve your said problem. To name a few, SUMIF, QUERY, and FILTER.
  
  Formula based on SUMIF:
  
  =sumif(A1:A,"Name 1",B1:B)
  
  Based on Filter:
  
  =sum(filter(B1:B,A1:A="Name 1"))
  
  The last formula based on Query:
  
  =query(A1:B,"Select Sum(B) where A='Name 1'")
  
  Pick the one that you may find simple to understand. Actually, all these functions, except Query, are easy to learn. Please check my Google Sheets Function Guide.
  
  Best,
  
S K Srivastava October 8, 2019 At 1:29 pm

Thanx for the post.

Now, I know where to look if stuck in Google Sheet.