IF and logic formulas
Flags, pass and fail, grades, multiple conditions and hiding errors the right way. 58 answers, each checked by running it, with a live example and an exercise.
How to check if a cell contains specific text in Excel
=IF(ISNUMBER(SEARCH($E$1,A2)),"Yes","No")Combine SEARCH with ISNUMBER inside IF: =IF(ISNUMBER(SEARCH(text,cell)),"Yes","No").
How to use IF with multiple conditions in Excel
=IF(AND(B2>=$G$1,C2>=$G$2),"Bonus","No bonus")Put the conditions in AND (all must be true) or OR (any can be true) inside IF: =IF(AND(cond1,cond2),"yes","no").
How to assign letter grades with IF or IFS in Excel
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F")Use IFS with the conditions from the highest grade down: =IFS(score>=90,"A",score>=80,"B",score>=70,"C",score>=60,"D",TRUE,"F").
How to check if a cell is blank in Excel
=IF(C2="","Not shipped",C2-B2)Test the cell against "": =IF(C2="","Not shipped",C2-B2). ISBLANK(C2) works too, but only for truly empty cells.
How to replace #DIV/0! with 0 or a blank in Excel
=IFERROR(B2/C2,0)Wrap the division in IFERROR: =IFERROR(B2/C2,0). For only the zero case, test the divisor: =IF(C2=0,0,B2/C2).
How to check if a number is between two values in Excel
=IF(AND(B2>=$F$1,B2<=$F$2),"In range","Out of range")Use AND with both limits: =IF(AND(value>=low,value<=high),"In range","Out of range").
How to use IF with text in Excel
=IF(C2="Paid","Closed","Follow up")Compare the cell with the text in double quotes: =IF(C2="Paid","Closed","Follow up"). The comparison ignores upper and lower case.
How to check if a cell is not equal to a value in Excel
=IF(C2<>$G$1,"Chase","")Use the not-equal operator <>: =IF(C2<>$G$1,"Chase",""). It is TRUE for every value except the one you name.
How to show pass or fail based on a score in Excel
=IF(B2>=$F$1,"Pass","Fail")Compare the score with a pass mark kept in a cell: =IF(B2>=$F$1,"Pass","Fail").
How to check if a date is past due in Excel
=IF(AND(C2="",B2<$G$1),"Overdue","")Flag unpaid rows whose due date is before today: =IF(AND(C2="",B2<$G$1),"Overdue",""), with =TODAY() or an as-of date in G1.
How to flag dates that are due in the next 30 days in Excel
=IF(AND(B2>=$F$1,B2<=$F$1+$F$2),"Renews soon","")Test that the date is on or after today and no later than today plus the window: =IF(AND(B2>=$F$1,B2<=$F$1+$F$2),"Renews soon","").
How to write an IF formula with a date in Excel
=IF(B2>=$F$1,"New prices","Old prices")Compare the date with a cell or with DATE(), never with a date in quotes: =IF(B2>=$F$1,"New prices","Old prices").
How to compare two dates and flag late deliveries in Excel
=IF(C2="","Not delivered",IF(C2>B2,"Late","On time"))Check for a missing date first, then compare: =IF(C2="","Not delivered",IF(C2>B2,"Late","On time")).
How to check if a cell equals one of several values in Excel
=IF(OR(B2="CA",B2="OR",B2="WA"),"West","Central")List the matches inside OR: =IF(OR(B2="CA",B2="OR",B2="WA"),"West","Central"). For a long list, keep it in cells and use COUNTIF.
How to check if a value exists in a range in Excel
=IF(COUNTIF(A2:A9,D1)>0,"Yes","No")Count it with COUNTIF and test for more than zero: =IF(COUNTIF(A2:A9,D1)>0,"Yes","No").
How to use SWITCH instead of nested IFs in Excel
=SWITCH(B2,"N","North","S","South","E","East","W","West","Unknown")List each value and its result in pairs, with a default at the end: =SWITCH(B2,"N","North","S","South","E","East","W","West","Unknown").
How to replace a long nested IF with a lookup table in Excel
=XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,"Standard")Put the categories and results in a small table and look them up: =XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,"Standard").
How to check if exactly one of two conditions is true in Excel
=IF(XOR(B2="Approve",C2="Approve"),"Third review","Decided")Use XOR, which is TRUE when one test is true but not both: =IF(XOR(B2="Approve",C2="Approve"),"Third review","Decided").
How to check if any cell in a row meets a condition in Excel
=IF(COUNTIF(B2:E2,"<"&$I$1)>0,"Review","OK")Count the cells that meet it: =IF(COUNTIF(B2:E2,"<"&$I$1)>0,"Review","OK"). Any count above zero means at least one does.
How to check if all values in a range meet a condition in Excel
=IF(MIN(B2:E2)>=$I$1,"Passed all","Retake")Test the weakest value: =IF(MIN(B2:E2)>=$I$1,"Passed all","Retake"). If the lowest score passes, they all do.
How to calculate tiered commission with IF in Excel
=B2*IFS(B2>=$E$4,$F$4,B2>=$E$3,$F$3,TRUE,$F$2)Pick the rate with IFS, checking the highest tier first, and multiply: =B2*IFS(B2>=$E$4,$F$4,B2>=$E$3,$F$3,TRUE,$F$2).
How to put a calculation inside an IF formula in Excel
=IF(C2="Yes",B2*$G$1,0)Write the calculation as the result IF returns: =IF(C2="Yes",B2*$G$1,0) charges tax only on taxable items.
How to calculate overtime pay with IF in Excel
=IF(B2>$G$1,$G$1*C2+(B2-$G$1)*C2*$G$2,B2*C2)Pay regular hours at the base rate and the hours above 40 at 1.5 times: =IF(B2>$G$1,$G$1*C2+(B2-$G$1)*C2*$G$2,B2*C2).
How to show a blank instead of 0 in Excel
=LET(t,SUMIFS($C$2:$C$7,$B$2:$B$7,E2),IF(t=0,"",t))Test the result and return "" when it is 0. With LET the calculation is written once: =LET(t,SUMIFS(…),IF(t=0,"",t)).
How to flag rows with missing data in Excel
=IF(COUNTBLANK(A2:D2)>0,"Missing data","Complete")Count the empty cells in the row: =IF(COUNTBLANK(A2:D2)>0,"Missing data","Complete").
How to use a checkbox in an IF formula in Excel
=IF(C2,"Done","Open")A checkbox holds TRUE or FALSE, so use the cell itself as the test: =IF(C2,"Done","Open").
How to check if a cell is numeric in Excel
=IF(ISNUMBER(B2),"OK","Fix")Use ISNUMBER inside IF: =IF(ISNUMBER(B2),"OK","Fix"). It is TRUE only for real numbers, not text that looks like one.
How to use ISTEXT in an IF formula in Excel
=IF(ISTEXT(B2),"Check qty",B2*C2)Return a message for text and calculate otherwise: =IF(ISTEXT(B2),"Check qty",B2*C2).
How to show a message with a number in an IF formula in Excel
=IF(B2<C2,"Order "&C2-B2&" more","Stock OK")Join text and the calculation with &: =IF(B2<C2,"Order "&C2-B2&" more","Stock OK").
How to use LET to simplify a long IF formula in Excel
=LET(m,(B2-C2)/B2,IF(m<0,"Loss",IF(m<$G$1,"Thin","Healthy")))Name the repeated calculation once with LET, then use the name in the IFs: =LET(m,(B2-C2)/B2,IF(m<0,"Loss",IF(m<$G$1,"Thin","Healthy"))).
How to check if a cell contains one of several words in Excel
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,"Escalate","")Search for every word in a list at once and count the hits: =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,"Escalate","").
How to categorize transactions by keywords in Excel
=XLOOKUP(TRUE,ISNUMBER(SEARCH($E$2:$E$7,A2)),$F$2:$F$7,"Uncategorized")Keep keywords and categories in a table and return the category of the first keyword found: =XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,A2)),categories,"Uncategorized").
How to make an IF comparison case-sensitive in Excel
=IF(EXACT(B2,$F$1),"Valid","Invalid")Use EXACT as the test: =IF(EXACT(B2,$F$1),"Valid","Invalid"). The = operator ignores case; EXACT doesn't.
How to check if a number is even or odd in Excel
=IF(ISEVEN(A2),"Even side","Odd side")Use ISEVEN inside IF: =IF(ISEVEN(A2),"Even side","Odd side"). MOD(A2,2)=0 does the same in any app.
How to return 0 if a number is negative in Excel
=MAX(0,B2-C2)Take the larger of 0 and the result: =MAX(0,B2-C2). Negative results become 0 and positive ones pass through.
How to check if two cells match in Excel
=IF(B2=C2,"Match","Mismatch")Compare them with =: =IF(B2=C2,"Match","Mismatch"). Use EXACT when upper and lower case must match too.
How to write an IF formula with three outcomes in Excel
=IF(C2>B2,"Over budget",IF(C2<B2,"Under budget","On budget"))Nest a second IF in the FALSE branch: =IF(C2>B2,"Over budget",IF(C2<B2,"Under budget","On budget")).
How to use IF with AND and OR together in Excel
=IF(AND(B2="Yes",OR(C2>=$H$1,D2="Local")),"Yes","No")Nest OR inside AND (or the other way round) to match the rule: =IF(AND(B2="Yes",OR(C2>=$H$1,D2="Local")),"Yes","No").
How to use IF with VLOOKUP in Excel
=IF(VLOOKUP(B2,$F$2:$G$5,2,FALSE)>=C2,"Ship","Backorder")Use the VLOOKUP result inside the IF test: =IF(VLOOKUP(B2,$F$2:$G$5,2,FALSE)>=C2,"Ship","Backorder").
How to use IF with SUMIF to compare a total with a limit in Excel
=IF(SUMIF($A$2:$A$8,D2,$B$2:$B$8)>E2,"Over limit","OK")Total the rows with SUMIF and compare inside IF: =IF(SUMIF($A$2:$A$8,D2,$B$2:$B$8)>E2,"Over limit","OK").
How to set a status based on start and end dates in Excel
=IF(C2<>"","Done",IF(B2<>"","In progress","Not started"))Check the last stage first: =IF(C2<>"","Done",IF(B2<>"","In progress","Not started")).
How to flag the highest value in a list in Excel
=IF(B2=MAX($B$2:$B$7),"Top store","")Compare each value with the maximum of the list: =IF(B2=MAX($B$2:$B$7),"Top store","").
How to check if someone is over 18 from their date of birth in Excel
=IF(DATEDIF(B2,$F$1,"y")>=18,"Yes","No")Count whole years with DATEDIF and compare: =IF(DATEDIF(B2,$F$1,"y")>=18,"Yes","No"), with =TODAY() or an as-of date in F1.
How to group ages into ranges in Excel
=IFS(B2<18,"Under 18",B2<35,"18-34",B2<55,"35-54",TRUE,"55+")Test the upper limit of each band from the bottom up: =IFS(B2<18,"Under 18",B2<35,"18-34",B2<55,"35-54",TRUE,"55+").
How to calculate a late fee based on days overdue in Excel
=IF(C2>B2,MIN($G$2,(C2-B2)*$G$1),0)Charge the daily fee for each day late, up to a cap: =IF(C2>B2,MIN($G$2,(C2-B2)*$G$1),0).
How to convert TRUE and FALSE to 1 and 0 in Excel
=--B2Put two minus signs in front: =--B2 turns TRUE into 1 and FALSE into 0.
How to check if a range of cells is empty in Excel
=IF(COUNTA(B2:E2)=0,"No response","Answered")Count what is filled in and test for zero: =IF(COUNTA(B2:E2)=0,"No response","Answered").
How to use REGEXMATCH with IF in Google Sheets
=IF(REGEXMATCH(A2,"^\d{5}(-\d{4})?$"),"Valid","Check")REGEXMATCH returns TRUE or FALSE, so it slots straight into IF: =IF(REGEXMATCH(A2,"^\d{5}(-\d{4})?$"),"Valid","Check").
How to use IF with ARRAYFORMULA in Google Sheets
=ARRAYFORMULA(IF(B2:B9="","",IF(B2:B9>=F1,"Pass","Fail")))Give IF whole ranges and wrap it in ARRAYFORMULA: =ARRAYFORMULA(IF(B2:B9="","",IF(B2:B9>=F1,"Pass","Fail"))). One formula fills the column.
How to use IF with percentages in Excel
=IF((C2-B2)/B2>=$G$1,"On target","Below target")Compare the calculated rate with a percentage in a cell: =IF((C2-B2)/B2>=$G$1,"On target","Below target"). 10% is stored as 0.1.
How to use another cell's value if a cell is blank in Excel
=IF(C2="",B2,C2)Test the cell and fall back to the other one: =IF(C2="",B2,C2).
How to calculate shipping cost with an IF formula in Excel
=IF(B2>=$F$1,0,$F$2)Return 0 when the order reaches the free-shipping threshold and the fee otherwise: =IF(B2>=$F$1,0,$F$2).
How to label values above or below average in Excel
=IF(B2>AVERAGE($B$2:$B$9),"Above average","At or below")Compare each value with the average of the locked range: =IF(B2>AVERAGE($B$2:$B$9),"Above average","At or below").
How to check the length of text with IF in Excel
=IF(LEN(A2)=$E$1,"OK","Wrong length")Compare LEN with the required length: =IF(LEN(A2)=$E$1,"OK","Wrong length").
How to check if neither of two conditions is true in Excel
=IF(NOT(OR(B2="Yes",C2>=$G$1)),"Reject","Review")Wrap OR in NOT: =IF(NOT(OR(B2="Yes",C2>=$G$1)),"Reject","Review") is TRUE only when both conditions fail.
How to check if a time is before a cutoff time in Excel
=IF(MOD(B2,1)<$F$1,"Same day","Next day")Take the time of day with MOD(timestamp,1) and compare it with the cutoff: =IF(MOD(B2,1)<$F$1,"Same day","Next day").
How to check if a value is within a tolerance in Excel
=IF(ABS(B2-$F$1)<=$F$2,"Pass","Fail")Compare the distance from the target with the tolerance: =IF(ABS(B2-$F$1)<=$F$2,"Pass","Fail").
How to check if a column has any duplicates in Excel
=IF(SUMPRODUCT(--(COUNTIF(A2:A8,A2:A8)>1))>0,"Has duplicates","All unique")Count each value against the whole list and see if any count is above 1: =IF(SUMPRODUCT(--(COUNTIF(A2:A8,A2:A8)>1))>0,"Has duplicates","All unique").