--- title: "Excel IF function: one condition, several, and nested IFs | PracticeExcel" description: "How IF works, how to combine conditions with AND and OR, when to nest IFs or switch to IFS, and how to fix the IF mistakes that give wrong answers without an error." canonical_url: "https://practiceexcelonline.com/learn/excel-if-function" locale: en meta: "og:description": "How IF works, how to combine conditions with AND and OR, when to nest IFs or switch to IFS, and how to fix the IF mistakes that give wrong answers without an error." "og:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~RXhjZWwgSUYgZnVuY3Rpb246IG9uZSBjb25kaXRpb24sIHNldmVyYWwsIGFuZCBuZXN0ZWQgSUZz%2Cdescription_~SG93IElGIHdvcmtzLCBob3cgdG8gY29tYmluZSBjb25kaXRpb25zIHdpdGggQU5EIGFuZCBPUiwgd2hlbiB0byBuZXN0IElGcyBvciBzd2l0Y2ggdG8gSUZTLCBhbmQgaG93IHRvIGZpeCB0aGUgSUYgbWlzdGFrZXMgdGhhdCBnaXZlIHdyb25nIGFuc3dlcnMgd2l0aG91dCBhbiBlcnJvci4%2Ceyebrow_Excel%2Bguides%2Csite_practiceexcel%2Cp_Ii9sZWFybi9leGNlbC1pZi1mdW5jdGlvbiI%2Cs_u4QiK9exiM2jkZlE.png" "og:image:height": "600" "og:image:type": image/png "og:image:width": "1200" "og:title": "Excel IF function: one condition, several, and nested IFs | PracticeExcel" "twitter:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~RXhjZWwgSUYgZnVuY3Rpb246IG9uZSBjb25kaXRpb24sIHNldmVyYWwsIGFuZCBuZXN0ZWQgSUZz%2Cdescription_~SG93IElGIHdvcmtzLCBob3cgdG8gY29tYmluZSBjb25kaXRpb25zIHdpdGggQU5EIGFuZCBPUiwgd2hlbiB0byBuZXN0IElGcyBvciBzd2l0Y2ggdG8gSUZTLCBhbmQgaG93IHRvIGZpeCB0aGUgSUYgbWlzdGFrZXMgdGhhdCBnaXZlIHdyb25nIGFuc3dlcnMgd2l0aG91dCBhbiBlcnJvci4%2Ceyebrow_Excel%2Bguides%2Csite_practiceexcel%2Cp_Ii9sZWFybi9leGNlbC1pZi1mdW5jdGlvbiI%2Cs_u4QiK9exiM2jkZlE.png" --- > Index of every page on this site: [llms.txt](https://practiceexcelonline.com/llms.txt) # **Excel IF formulas: from one condition to many** How IF works, how to combine conditions with AND and OR, when to nest IFs or switch to IFS, and how to fix the IF mistakes that give wrong answers without an error. By Nicolaj Gajhede, founder of PracticeExcel · Updated October 29, 2026 · 13 min read IF is the function that lets a spreadsheet make decisions. Should this order ship for free? Did this student pass? Is this invoice overdue, and by how much? Almost every real workbook has an IF somewhere, and almost every Excel test has at least one question that needs it. IF is also where many people's formulas start to go quietly wrong. A nested IF in the wrong order, a number in quotes or a missing third argument gives an answer that looks fine and isn't. This guide starts with the basic form and works up to several conditions, several outcomes and the alternatives that are often better than another IF. On the way, it covers the mistakes that cause most wrong answers, so you can spot them in your own sheets. ## How IF works IF takes three arguments: a test, a value if the test is true, and a value if it is false. `**=IF(B2>=60,"Pass","Fail")**` The easiest way to read an IF is as a sentence: *if B2 is at least 60, show Pass, otherwise show Fail.* If you can't say your formula out loud in one sentence like that, it probably needs to be split up or rewritten. C2`=IF(1B2>=60,2"Pass",3"Fail")` | | A | B | C | | --- | --- | --- | --- | | 1 | Student | Score | | | 2 | Maya | 58 | **Fail** | **In plain words:** 1If the score in B2 is 60 or more, 2show Pass. 3Otherwise show Fail. The score in B2 is 58, so the test B2>=60 is false and IF returns its third argument. The three parts are: - **logical\_test**: anything that is either true or false. Usually a comparison such as `**B2>=60**`, but it can also be a function that returns TRUE or FALSE, such as ISBLANK or AND. - **value\_if\_true**: what the cell shows when the test is true. - **value\_if\_false**: what the cell shows when it is false. Practice the basic form with [**pass or fail with IF**](https://practiceexcelonline.com/practice/if/if-pass-fail). ## Writing the test The test is where most IF errors start, so it is worth being precise about it. Excel has six comparison operators: | **Means** | **Operator** | **Example** | | --- | --- | --- | | equal to | = | `**C2="US"**` | | not equal to | <> | `**C2<>"US"**` | | more than | > | `**B2>10000**` | | at least | >= | `**B2>=60**` | | less than | < | `**B2<0**` | | up to, at most | <= | `**B2<=30**` | The hard part is not the symbols but turning words into them. "At least 60" includes 60, so it is `**>=**`. "More than 60" does not, so it is `**>**`. "Up to 30" is `**<=**`, and "under 30" is `**<**`. Tests and real-world requirements are full of these words, and a formula with `**>**` where the task said "at least" is wrong for exactly one value, which is the value nobody checks. When a band boundary matters, test the boundary itself: type 60 into the cell and see what the formula says. Two more rules for the test: - **Text goes in double quotes**, numbers and cell references do not. `**C2="US"**` compares with the text US. `**C2=US**` makes Excel look for a name called US and returns `**#NAME?**`. - **Text comparisons ignore case.** `**C2="us"**` is true when C2 holds US, Us or us. If you need an exact, case-sensitive match, use EXACT inside the test. ## What IF can return The second and third arguments can be almost anything: text, a number, a cell reference, a whole calculation, or another function. Returning a calculation instead of text is how IF does real work. A sales commission that only applies above a threshold is one IF: C2`=IF(1B2>10000,2B2*0.05,30)` | | A | B | C | | --- | --- | --- | --- | | 1 | Rep | Sales | | | 2 | Leo | 12500 | **625** | **In plain words:** 1If the sales in B2 are over 10,000, 2pay 5% of them. 3Otherwise pay 0. Sales are over 10,000, so IF returns the calculation in its second argument: 5% of 12,500. Read it as: *if sales are more than 10,000, return sales times 5%, otherwise return 0.* The [**bonus exercise**](https://practiceexcelonline.com/practice/if/if-calculate-bonus) drills the same idea. A few return values deserve a note: - **An empty-looking cell:** use `**""**`, two double quotes with nothing between. `**=IF(B2>10000,B2*0.05,"")**` shows nothing when there is no commission. Keep in mind that the cell is not truly empty; it contains an empty text, which COUNTA counts and some formulas treat differently from a blank. - **Zero:** return 0 when the result feeds other calculations. A column of numbers with a few `**""**` in it can break formulas that multiply or compare its values. - **Nothing at all:** if you leave out the third argument, `**=IF(B2>10000,B2*0.05)**`, IF returns FALSE when the test is false. That is almost never what you want in a report, and it is the answer to the common question "why does my IF say FALSE?". ## Two conditions that must both be true: AND Real rules often have more than one condition. "Free shipping on orders of at least 50 that ship to the US" is two tests, and both must pass. That is what AND is for. AND takes any number of tests and returns TRUE only if all of them are true. Put it in IF's first argument: D2`=IF(1AND(B2>=50,C2="US"),2"Free",3"Paid")` | | A | B | C | D | | --- | --- | --- | --- | --- | | 1 | Order | Total | Country | | | 2 | 1042 | 64 | US | **Free** | **In plain words:** 1If the total in B2 is at least 50 and the order ships to the US, 2shipping is Free. 3Otherwise it is Paid. AND is true only when both tests are: the order is at least 50 and it ships to the US. The mistake to avoid is writing the condition the way you would say it. Excel does not understand `**B2>=50 AND C2="US"**` as a test; it rejects the formula before it even runs. AND is a function, so the tests go inside its parentheses, separated by commas. Practice it in [**two conditions with AND**](https://practiceexcelonline.com/practice/logical/if-and). ## Either condition: OR OR is AND's partner. It returns TRUE if at least one of its tests is true, and FALSE only when all of them are false. Use it when any one of several reasons is enough: D2`=IF(1OR(B2="Urgent",C2>7),2"Escalate",3"OK")` | | A | B | C | D | | --- | --- | --- | --- | --- | | 1 | Ticket | Priority | Days open | | | 2 | T-88 | Normal | 9 | **Escalate** | **In plain words:** 1If the priority in B2 is Urgent or the ticket has been open more than 7 days, 2Escalate it. 3Otherwise it is OK. The ticket is not urgent, but it has been open for more than 7 days, and one true test is enough for OR. Here a ticket is escalated if it is marked urgent *or* has been open for more than a week. This one isn't urgent, but it is nine days old, so it is escalated. The [**OR exercise**](https://practiceexcelonline.com/practice/logical/if-or) has the same shape. A useful way to check AND and OR is to ask what happens at the extremes. With AND, one failed test makes the whole thing false. With OR, one passed test makes the whole thing true. If your formula behaves the other way round, you used the wrong one. AND and OR work without IF too. On their own they return TRUE or FALSE, which is exactly what a conditional formatting rule needs: `**=AND($B2<$F$1,$C2="No")**` colors every invoice that is overdue and unpaid. The [**conditional formatting guide**](https://practiceexcelonline.com/learn/conditional-formatting-excel) shows how to turn a test like that into a rule. ### NOT, and AND and OR together NOT flips a test: `**NOT(C2="US")**` is true for every country except the US. In practice `**C2<>"US"**` says the same thing more simply, so NOT is mostly useful around a function, such as `**NOT(ISBLANK(B2))**`. You can also combine AND and OR in one test. "Approve if the amount is under 500, or if it is under 5,000 and a manager has signed off" is: `**=IF(OR(B2<500,AND(B2<5000,C2="Yes")),"Approve","Review")**` Build a formula like this from the inside out. Write and test the AND on its own in a spare cell, then wrap it in OR, then wrap that in IF. The [**AND and OR combined**](https://practiceexcelonline.com/practice/logical/and-or-combined) exercise takes you through one. ## More than two outcomes: nested IF IF has two outcomes. When you need three or more, such as customer tiers, grade letters or shipping bands, the classic answer is to put a second IF in the first IF's value\_if\_false: C2`=IF(1B2>=10000,2"Gold",3IF(B2>=5000,"Silver","Bronze"))` | | A | B | C | | --- | --- | --- | --- | | 1 | Customer | Spend | | | 2 | Hart & Co | 7400 | **Silver** | **In plain words:** 1If the spend in B2 is 10,000 or more, 2the customer is Gold. 3Otherwise run a second IF: Silver from 5,000, Bronze below that. 7,400 fails the first test (10,000 or more) and passes the second (5,000 or more), so the inner IF returns Silver. Read it from the outside in: *if spend is at least 10,000, Gold. Otherwise, if spend is at least 5,000, Silver. Otherwise, Bronze.* Excel stops at the first test that is true and never looks at the rest. That is why the order matters so much. Put the highest band first when you use `**>=**`. Here is what happens if you start with the lowest instead: `**=IF(B2>=5000,"Silver",IF(B2>=10000,"Gold","Bronze"))**` A customer who spent 20,000 passes the first test, `**B2>=5000**`, and gets Silver. The Gold test is never reached. No error appears, and every customer above 5,000 is silently put in the wrong tier. When you write bands, either go from the top down with `**>=**`, or from the bottom up with `**<**`, and test one value in each band before you fill the formula down. The [**nested IF exercise**](https://practiceexcelonline.com/practice/if/nested-if) asks for three outcomes, which is about where nested IFs stop being comfortable. Excel allows up to 64 levels, but nobody can read a formula with ten closing parentheses at the end, including you in six months. ## IFS: the readable chain Excel 2019 and Microsoft 365 have IFS, which takes pairs of tests and results and returns the result of the first true test. The tiers from the previous section become: C2`=IFS(1B2>=10000,2"Gold",3B2>=5000,4"Silver",5TRUE,6"Bronze")` | | A | B | C | | --- | --- | --- | --- | | 1 | Customer | Spend | | | 2 | Ortiz Ltd | 2300 | **Bronze** | **In plain words:** 1If the spend in B2 is 10,000 or more, 2show Gold. 3If it is 5,000 or more, 4show Silver. 5In every other case, 6show Bronze. IFS checks each test in turn. 2,300 fails the first two, and TRUE at the end catches everything else. It does the same job without the nesting: test, result, test, result. Two things to know: - **IFS has no "otherwise" argument.** If no test is true, it returns `**#N/A**`. The convention is to end with `**TRUE**` as the last test, which is always true, so the last result works as the else value. - **The order still matters.** IFS stops at the first true test, exactly like nested IF, so the highest band still goes first. Practice IFS with [**grades**](https://practiceexcelonline.com/practice/ifs-switch/ifs-grades) and [**shipping price bands**](https://practiceexcelonline.com/practice/ifs-switch/ifs-price-bands). If you work with people who still use Excel 2016, they will see `**#NAME?**` instead of your result, so check before you rely on it in a shared workbook. ## SWITCH: when you are matching exact values When the decision is "if the code is N, show North; if it is S, show South", you are not testing ranges but matching exact values. SWITCH is built for that: C2`=SWITCH(1B2,2"N",3"North",4"S",5"South",6"E",7"East",8"W",9"West",10"Unknown")` | | A | B | C | | --- | --- | --- | --- | | 1 | Store | Code | | | 2 | Store 14 | S | **South** | **In plain words:** Look at 1the code in B2: 2N gives 3North, 4S gives 5South, 6E gives 7East, 8W gives 9West, and 10anything else gives Unknown. SWITCH compares B2 with each code and returns the name that goes with the first match. SWITCH takes the value to check, then pairs of values and results, and an optional last argument for when nothing matches. It is shorter than the equivalent IFS because you don't repeat `**B2=**` in every test. Try it in [**turning codes into names with SWITCH**](https://practiceexcelonline.com/practice/ifs-switch/switch-codes-to-names). ## When a lookup table beats any IF Nested IF, IFS and SWITCH all put the business rules inside the formula. That is fine for three tiers that never change. It is a problem when there are ten bands, or when the thresholds change every year, because someone has to edit every copy of the formula and get every number right. The alternative is to put the bands in a small table on the sheet and look them up. For thresholds, VLOOKUP with TRUE finds the band a value falls into, as long as the first column is sorted from smallest to largest: `**=VLOOKUP(B2,$F$2:$G$4,2,TRUE)**` With 0, 5,000 and 10,000 in F2:F4 and Bronze, Silver and Gold next to them, this gives the same answers as the nested IF, and changing a threshold means changing one cell. XLOOKUP does the same with match\_mode -1. Our [**lookup guide**](https://practiceexcelonline.com/learn/vlookup-vs-xlookup-vs-index-match) covers band lookups in detail, and the [**approximate match exercise**](https://practiceexcelonline.com/practice/vlookup/vlookup-approximate-match) drills one. A good rule of thumb: if the rule fits in one sentence and has at most three outcomes, use IF or IFS. If it has more outcomes, or the numbers in it are likely to change, use a table. ## IF with blank cells Data often has gaps: a missing email, a delivery date not filled in yet, a score for a student who was absent. A formula that ignores them gives misleading results, such as an overdue warning on an order that hasn't shipped. ISBLANK returns TRUE when a cell is empty, and is a natural first test: C2`=IF(1ISBLANK(B2),2"Missing",3"OK")` | | A | B | C | | --- | --- | --- | --- | | 1 | Name | Email | | | 2 | Sam | | **Missing** | **In plain words:** 1If the email in B2 is empty, 2show Missing. 3Otherwise show OK. B2 is empty, so ISBLANK is true and the row is flagged instead of showing a misleading result. You can also write the test as `**B2=""**`, which is true for an empty cell and for a cell with an empty text, such as the `**""**` returned by another IF. ISBLANK is false for that empty text, so if the column is filled by formulas, `**B2=""**` is usually the safer test. The [**missing values exercise**](https://practiceexcelonline.com/practice/if/if-isblank-missing) practices the ISBLANK version. A common pattern is to put the blank check first and the real calculation second, so the calculation only runs when there is data: `**=IF(B2="","",B2*1.25)**`. ## IF with part of a text "If the subject contains the word refund" sounds like a job for IF with a wildcard, but IF does not understand wildcards. `**=IF(B2="*refund*","Refund","Other")**` compares B2 with the literal text `***refund***` and is almost always false. The trick is SEARCH, which returns the position of one text inside another, or the `**#VALUE!**` error when it is not there. ISNUMBER turns that into TRUE or FALSE: C2`=IF(1ISNUMBER(SEARCH("refund",B2)),2"Refund",3"Other")` | | A | B | C | | --- | --- | --- | --- | | 1 | Ticket | Subject | | | 2 | T-12 | Refund for order 1042 | **Refund** | **In plain words:** 1If the subject in B2 contains the word refund, 2show Refund. 3Otherwise show Other. SEARCH finds "refund" inside the subject and returns its position, a number, so ISNUMBER is true. SEARCH ignores case, which is usually what you want for words. Use FIND instead when case matters, for example for product codes. The [**contains a word exercise**](https://practiceexcelonline.com/practice/find-search/search-isnumber-contains) drills the pattern. If you only need to count how many cells contain a word, COUNTIF does understand wildcards: `**=COUNTIF(B2:B100,"*refund*")**`. ## IF and errors Sometimes the test is not the problem; the calculation is. A price per unit divides revenue by units, and a row with zero units gives `**#DIV/0!**`. You could write `**=IF(C2=0,0,B2/C2)**`, and that is a perfectly good formula. IFERROR is shorter when any error should be replaced: `**=IFERROR(B2/C2,0)**` IFERROR returns the calculation's result if there is no error, and its second argument if there is one. It is also the usual wrapper around lookups: `**=IFERROR(VLOOKUP(D2,A2:B6,2,FALSE),"Unknown")**`. Practice both with [**avoiding #DIV/0!**](https://practiceexcelonline.com/practice/iferror/iferror-div0) and [**hiding #N/A**](https://practiceexcelonline.com/practice/iferror/iferror-vlookup). Use IFERROR on purpose, not as a blanket. It hides *every* error, including the ones that mean your formula is broken, such as a deleted column or a typo in a range. When you only expect `**#N/A**` from a lookup, IFNA catches that one error and lets the others show, so a real mistake still stands out. ## IF on a whole column You usually write IF once at the top of a helper column and fill it down with Ctrl+D or a double-click on the fill handle. Each row gets its own copy, with the references shifted to that row. If the formula points at a fixed cell, such as a threshold in F1, lock it with `**$**`: `**=IF(B2>=$F$1,"Pass","Fail")**`. Without the dollar signs, the second row compares with F2, which is empty, and every row from there on is graded against zero. If what you really want is a count or a total of the rows that meet a condition, you may not need the helper column at all. "How many passed?" is `**=COUNTIF(B2:B30,">=60")**`, and "total commission over 10,000" is SUMIF. A helper column of IFs is great for showing the decision on every row. It is the long way round for a single summary number. ## Mistakes that give wrong answers without an error These are the IF problems that cost the most, because Excel doesn't flag any of them: - **Bands in the wrong order.** The first true test wins. Start from the top with `**>=**` or from the bottom with `**<**`, never a mix. - **The wrong operator at the boundary.** `**>**` where the rule says "at least". Test the boundary value itself. - **Numbers in quotes.** `**=IF(B2>="60","Pass","Fail")**` compares a number with the text "60". Text counts as larger than any number, so the test is false for every score and everyone fails. Quotes are for text only. - **Numbers stored as text.** The reverse problem: if B2 holds the text "75" from an import, `**B2>=60**` is true for every value, because text is larger than any number. Numbers stored as text usually sit at the left of the cell. Convert them before you trust any comparison. - **A missing third argument**, which shows FALSE in the report. - **Hard-coded thresholds** copied into hundreds of formulas. Put them in a cell and reference it with `**$**`, so a change is one edit. - **IFERROR around everything**, which hides the errors you needed to see. - **TRUE and FALSE in quotes.** `**"TRUE"**` is text, not the logical value. If a result is meant to feed another test, return TRUE or FALSE without quotes. ## IF in Excel tests IF and its partners appear in almost every employer Excel test, usually in one of these forms: - **A simple decision:** "In D2, show Yes if the target was met." One IF with a comparison, often against a target in another cell. - **Two conditions:** "Flag employees with at least five years of service and a rating of 4 or more." AND inside IF. Watch the words "at least" and "more than". - **Several outcomes:** "Assign a grade: A for 90 and above, B for 80 and above…" Nested IF or IFS, highest band first. - **A calculation in one branch:** "Calculate a 10% bonus for sales over 50,000, otherwise 0." IF returning a calculation. - **Error handling:** "Show 0 instead of an error." IFERROR around a division or a lookup. Read the task twice for the boundary words and the exact text to return. If it says to show "Yes", a formula that shows "yes" or "Y" may be marked wrong, depending on the test. Check one row on each side of every boundary before you move on. Our [**job interview guide**](https://practiceexcelonline.com/learn/excel-for-job-interviews) has more on how the timer changes the way you work. ## Summary IF makes a decision: a test, a result if it is true and a result if it is false. AND and OR combine several tests into one. For three or more outcomes, nest IFs or use IFS, and either way put the highest band first, because the first true test wins. SWITCH is for exact codes, and a lookup table is better than any IF once there are many bands or the thresholds change. Most IF errors are silent: the wrong operator at a boundary, a number in quotes, bands in the wrong order. Say each formula out loud as a sentence, test the boundary values, and the exercises below will make the patterns automatic. ![Nicolaj Gajhede](https://practiceexcelonline.com/authors/nicolaj-gajhede.webp) **About the author** ## **Nicolaj Gajhede**, founder of PracticeExcel I'm a software developer, and I built PracticeExcel to help others get comfortable with Excel, and the best way to get there is to work through the formulas yourself. [Nicolaj Gajhede on LinkedIn](https://www.linkedin.com/in/nicolaj-gajhede/) [More about Nicolaj Gajhede](https://practiceexcelonline.com/about#nicolaj-gajhede) ## **Practice what this guide covers** Graded exercises in a real grid, in the order the guide uses them. - [**Pass or fail with IF**IF →](https://practiceexcelonline.com/practice/if/if-pass-fail) - [**Calculate a bonus with IF**IF →](https://practiceexcelonline.com/practice/if/if-calculate-bonus) - [**Three outcomes with nested IF**IF →](https://practiceexcelonline.com/practice/if/nested-if) - [**Flag missing values with ISBLANK**IF →](https://practiceexcelonline.com/practice/if/if-isblank-missing) - [**Two conditions with AND**AND and OR →](https://practiceexcelonline.com/practice/logical/if-and) - [**Either condition with OR**AND and OR →](https://practiceexcelonline.com/practice/logical/if-or) - [**AND and OR in one test**AND and OR →](https://practiceexcelonline.com/practice/logical/and-or-combined) - [**Grades with IFS**IFS and SWITCH →](https://practiceexcelonline.com/practice/ifs-switch/ifs-grades) - [**Shipping price bands with IFS**IFS and SWITCH →](https://practiceexcelonline.com/practice/ifs-switch/ifs-price-bands) - [**Turn codes into names with SWITCH**IFS and SWITCH →](https://practiceexcelonline.com/practice/ifs-switch/switch-codes-to-names) - [**Hide #N/A with IFERROR**IFERROR →](https://practiceexcelonline.com/practice/iferror/iferror-vlookup) - [**Avoid #DIV/0! with IFERROR**IFERROR →](https://practiceexcelonline.com/practice/iferror/iferror-div0) - [**Check if a text contains a word**FIND and SEARCH →](https://practiceexcelonline.com/practice/find-search/search-isnumber-contains) ### **Function pages** - [**IF**](https://practiceexcelonline.com/functions/if) - [**AND**](https://practiceexcelonline.com/functions/and) - [**OR**](https://practiceexcelonline.com/functions/or) - [**NOT**](https://practiceexcelonline.com/functions/not) - [**IFS**](https://practiceexcelonline.com/functions/ifs) - [**SWITCH**](https://practiceexcelonline.com/functions/switch) - [**IFERROR**](https://practiceexcelonline.com/functions/iferror) - [**ISBLANK**](https://practiceexcelonline.com/functions/isblank) ### **Timed tests** - [Excel test for job interviews](https://practiceexcelonline.com/tests/excel-test-for-job-interview) · 20 questions in 30 minutes - [Indeed Excel assessment practice test](https://practiceexcelonline.com/tests/indeed-excel-assessment) · 20 questions in 30 minutes - [Excel test for admin jobs](https://practiceexcelonline.com/tests/excel-test-for-admin-jobs) · 20 questions in 30 minutes ## **Questions**
### How do I write an IF formula with two conditions? Put both tests inside AND when both must be true, or inside OR when one is enough, and put that inside IF. For example =IF(AND(B2>=50,C2="US"),"Free","Paid") returns Free only when the total is at least 50 and the country is US.
### How many IFs can you nest in Excel? Excel allows 64 nested IFs, but more than three or four are hard to read and easy to get wrong. Use IFS for a long chain of tests, SWITCH for a list of exact codes, and a lookup table with XLOOKUP or VLOOKUP when there are many bands.
### Why does my IF formula return FALSE? The test was false and the formula has no third argument, so IF returns FALSE. Add the value you want when the test is false, for example "" for an empty-looking cell or 0.
### Can IF check if a cell contains a word? Not on its own, because IF does not understand wildcards. Combine it with SEARCH and ISNUMBER, =IF(ISNUMBER(SEARCH("refund",B2)),"Refund","Other"), or use COUNTIF with wildcards to count matching cells.
### What is the difference between IF and IFS? IF has one test and two outcomes, so several outcomes need IFs inside IFs. IFS takes pairs of tests and results and returns the result of the first true test, which is easier to read. IFS needs Excel 2019 or later, and Google Sheets supports it too.
## **More guides** - [**Free Excel course online: learn Excel from your first formula to job-ready**An Excel course for beginners in nine modules, from cells and SUM to lookups and summary tables. You practice in the browser, and the first part is free with no account.](https://practiceexcelonline.com/learn/how-to-learn-excel) - [**VLOOKUP vs XLOOKUP vs INDEX MATCH: which lookup to use**How VLOOKUP, XLOOKUP and INDEX with MATCH work, where each one breaks, and which to use in a test, an old workbook or a sheet you are building today.](https://practiceexcelonline.com/learn/vlookup-vs-xlookup-vs-index-match) - [**Excel for job interviews: what gets tested and how to prepare**What employer Excel tests ask, the formulas that come up again and again, how the timer changes your approach, and a two-week plan to get ready.](https://practiceexcelonline.com/learn/excel-for-job-interviews) - [**Conditional formatting in Excel: highlight cells and rows with rules and formulas**How conditional formatting works, the built-in rules worth knowing, and the formulas that highlight whole rows, overdue dates, duplicates and anything a menu can't do.](https://practiceexcelonline.com/learn/conditional-formatting-excel) ```json {"@context":"https://schema.org","@graph":[{"@id":"https://practiceexcelonline.com/#identity","@type":"Organization","name":"PracticeExcel","url":"https://practiceexcelonline.com/","logo":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/#website","@type":"WebSite","inLanguage":"en-US","name":"PracticeExcel","url":"https://practiceexcelonline.com/","alternateName":["Practice Excel Online","PracticeExcelOnline.com"],"publisher":{"@id":"https://practiceexcelonline.com/#identity"},"workTranslation":{"@id":"https://excelopgaver.dk/#website"}},{"@id":"https://practiceexcelonline.com/learn/excel-if-function#webpage","dateModified":"2026-10-29","datePublished":"2026-10-29","description":"How IF works, how to combine conditions with AND and OR, when to nest IFs or switch to IFS, and how to fix the IF mistakes that give wrong answers without an error.","name":"Excel IF function: one condition, several, and nested IFs","url":"https://practiceexcelonline.com/learn/excel-if-function","@type":["WebPage","FAQPage"],"breadcrumb":{"@id":"https://practiceexcelonline.com/learn/excel-if-function#breadcrumb"},"isPartOf":{"@id":"https://practiceexcelonline.com/#website"},"mainEntity":[{"@id":"https://practiceexcelonline.com/learn/excel-if-function#/schema/question/1"},{"@id":"https://practiceexcelonline.com/learn/excel-if-function#/schema/question/2"},{"@id":"https://practiceexcelonline.com/learn/excel-if-function#/schema/question/3"},{"@id":"https://practiceexcelonline.com/learn/excel-if-function#/schema/question/4"},{"@id":"https://practiceexcelonline.com/learn/excel-if-function#/schema/question/5"}],"potentialAction":[{"@type":"ReadAction","target":["https://practiceexcelonline.com/learn/excel-if-function"]}],"primaryImageOfPage":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/learn/excel-if-function#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://practiceexcelonline.com","position":1},{"@type":"ListItem","name":"Excel guides","item":"https://practiceexcelonline.com/learn","position":2},{"@type":"ListItem","name":"Excel IF function: one condition, several, and nested IFs","position":3}]},{"@id":"https://practiceexcelonline.com/learn/excel-if-function#article","@type":"Article","dateModified":"2026-10-29","datePublished":"2026-10-29","description":"How IF works, how to combine conditions with AND and OR, when to nest IFs or switch to IFS, and how to fix the IF mistakes that give wrong answers without an error.","headline":"Excel IF function: one condition, several, and nested IFs","inLanguage":"en-US","thumbnailUrl":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~RXhjZWwgSUYgZnVuY3Rpb246IG9uZSBjb25kaXRpb24sIHNldmVyYWwsIGFuZCBuZXN0ZWQgSUZz%2Cdescription_~SG93IElGIHdvcmtzLCBob3cgdG8gY29tYmluZSBjb25kaXRpb25zIHdpdGggQU5EIGFuZCBPUiwgd2hlbiB0byBuZXN0IElGcyBvciBzd2l0Y2ggdG8gSUZTLCBhbmQgaG93IHRvIGZpeCB0aGUgSUYgbWlzdGFrZXMgdGhhdCBnaXZlIHdyb25nIGFuc3dlcnMgd2l0aG91dCBhbiBlcnJvci4%2Ceyebrow_Excel%2Bguides%2Csite_practiceexcel%2Cp_Ii9sZWFybi9leGNlbC1pZi1mdW5jdGlvbiI%2Cs_u4QiK9exiM2jkZlE.png","author":{"@id":"https://practiceexcelonline.com/#/schema/person/1"},"image":{"@id":"https://practiceexcelonline.com/#/schema/image/1"},"isPartOf":{"@id":"https://practiceexcelonline.com/learn/excel-if-function#webpage"},"mainEntityOfPage":{"@id":"https://practiceexcelonline.com/learn/excel-if-function#webpage"},"publisher":{"@id":"https://practiceexcelonline.com/#identity"}},{"@id":"https://practiceexcelonline.com/learn/excel-if-function#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"How do I write an IF formula with two conditions?","acceptedAnswer":{"@type":"Answer","text":"Put both tests inside AND when both must be true, or inside OR when one is enough, and put that inside IF. For example =IF(AND(B2>=50,C2=\"US\"),\"Free\",\"Paid\") returns Free only when the total is at least 50 and the country is US."}},{"@id":"https://practiceexcelonline.com/learn/excel-if-function#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"How many IFs can you nest in Excel?","acceptedAnswer":{"@type":"Answer","text":"Excel allows 64 nested IFs, but more than three or four are hard to read and easy to get wrong. Use IFS for a long chain of tests, SWITCH for a list of exact codes, and a lookup table with XLOOKUP or VLOOKUP when there are many bands."}},{"@id":"https://practiceexcelonline.com/learn/excel-if-function#/schema/question/3","@type":"Question","inLanguage":"en-US","name":"Why does my IF formula return FALSE?","acceptedAnswer":{"@type":"Answer","text":"The test was false and the formula has no third argument, so IF returns FALSE. Add the value you want when the test is false, for example \"\" for an empty-looking cell or 0."}},{"@id":"https://practiceexcelonline.com/learn/excel-if-function#/schema/question/4","@type":"Question","inLanguage":"en-US","name":"Can IF check if a cell contains a word?","acceptedAnswer":{"@type":"Answer","text":"Not on its own, because IF does not understand wildcards. Combine it with SEARCH and ISNUMBER, =IF(ISNUMBER(SEARCH(\"refund\",B2)),\"Refund\",\"Other\"), or use COUNTIF with wildcards to count matching cells."}},{"@id":"https://practiceexcelonline.com/learn/excel-if-function#/schema/question/5","@type":"Question","inLanguage":"en-US","name":"What is the difference between IF and IFS?","acceptedAnswer":{"@type":"Answer","text":"IF has one test and two outcomes, so several outcomes need IFs inside IFs. IFS takes pairs of tests and results and returns the result of the first true test, which is easier to read. IFS needs Excel 2019 or later, and Google Sheets supports it too."}},{"@id":"https://practiceexcelonline.com/#/schema/person/1","@type":"Person","description":"I'm a software developer, and I built PracticeExcel to help others get comfortable with Excel, and the best way to get there is to work through the formulas yourself.","jobTitle":"founder of PracticeExcel","name":"Nicolaj Gajhede","url":"https://practiceexcelonline.com/about#nicolaj-gajhede","image":{"@id":"https://practiceexcelonline.com/#/schema/image/2"},"sameAs":["https://www.linkedin.com/in/nicolaj-gajhede/"]},{"@id":"https://practiceexcelonline.com/#logo","@type":"ImageObject","caption":"PracticeExcel","contentUrl":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png"},{"@id":"https://practiceexcelonline.com/#/schema/image/1","@type":"ImageObject","contentUrl":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~RXhjZWwgSUYgZnVuY3Rpb246IG9uZSBjb25kaXRpb24sIHNldmVyYWwsIGFuZCBuZXN0ZWQgSUZz%2Cdescription_~SG93IElGIHdvcmtzLCBob3cgdG8gY29tYmluZSBjb25kaXRpb25zIHdpdGggQU5EIGFuZCBPUiwgd2hlbiB0byBuZXN0IElGcyBvciBzd2l0Y2ggdG8gSUZTLCBhbmQgaG93IHRvIGZpeCB0aGUgSUYgbWlzdGFrZXMgdGhhdCBnaXZlIHdyb25nIGFuc3dlcnMgd2l0aG91dCBhbiBlcnJvci4%2Ceyebrow_Excel%2Bguides%2Csite_practiceexcel%2Cp_Ii9sZWFybi9leGNlbC1pZi1mdW5jdGlvbiI%2Cs_u4QiK9exiM2jkZlE.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~RXhjZWwgSUYgZnVuY3Rpb246IG9uZSBjb25kaXRpb24sIHNldmVyYWwsIGFuZCBuZXN0ZWQgSUZz%2Cdescription_~SG93IElGIHdvcmtzLCBob3cgdG8gY29tYmluZSBjb25kaXRpb25zIHdpdGggQU5EIGFuZCBPUiwgd2hlbiB0byBuZXN0IElGcyBvciBzd2l0Y2ggdG8gSUZTLCBhbmQgaG93IHRvIGZpeCB0aGUgSUYgbWlzdGFrZXMgdGhhdCBnaXZlIHdyb25nIGFuc3dlcnMgd2l0aG91dCBhbiBlcnJvci4%2Ceyebrow_Excel%2Bguides%2Csite_practiceexcel%2Cp_Ii9sZWFybi9leGNlbC1pZi1mdW5jdGlvbiI%2Cs_u4QiK9exiM2jkZlE.png"},{"@id":"https://practiceexcelonline.com/#/schema/image/2","@type":"ImageObject","contentUrl":"https://practiceexcelonline.com/authors/nicolaj-gajhede.webp","inLanguage":"en-US","url":"https://practiceexcelonline.com/authors/nicolaj-gajhede.webp"}]} ``` ## Sitemap See the full [sitemap](https://practiceexcelonline.com/sitemap.md) for all pages.