To use IFS in multiple cells, write the formula for the first row, then copy or fill it down or across so each cell evaluates its own inputs. For example, with scores in A2:A10 and letter grades in B2:B10, enter an IFS formula in B2 and fill it down; use MAP in Microsoft 365 if you want one formula to return results for an entire range.
What IFS Does in Excel
IFS checks conditions in the order you provide them. When it finds the first condition that is TRUE, it returns the value paired with that condition and stops checking. This makes IFS useful when a result depends on several possible cases, such as assigning grades, labeling order sizes, or categorizing dates.
The function takes alternating logical tests and results: IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, ...). Each test must have a corresponding result. IFS does not mean “test every condition and return every matching result”; it returns one result for each calculation, based on the first match.
IFS is available in Microsoft 365 and Excel 2019 and later. It is not available in Excel 2016, so a workbook using it may need an alternative formula for people on that version. The function is also available in current Excel for the web and supported Mac versions.
Enter a Formula and Fill It Through Multiple Cells
The usual approach is to calculate one row at a time. Suppose each row contains a score in column A and should show a grade in column B. With scores in A2:A10, enter a formula in B2 and copy it to B3:B10. The formula uses a relative reference, so the row number changes as it is copied.
With the score for the current row in A2 and the grade result in B2, enter:
=IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",A2>=60,"D",TRUE,"F")
For this example, a score of 84 in A2 returns B. A score of 67 returns D. The final TRUE acts as a catch-all: if none of the earlier tests is true, Excel returns F. Without a catch-all, a value that matches no condition produces the #N/A error.
- Enter the formula in the first result cell, such as B2.
- Press Enter and confirm the displayed result.
- Select B2:B10 and use Home > Fill > Down, or drag the fill handle at the lower-right corner of B2 down to B10.
In Windows Excel, Ctrl+D fills the selected cells with the top cell’s contents. In Excel for Mac, Control+D is the corresponding Fill Down shortcut. If you are filling a formula across columns, adjust the references and use the fill handle or the Home > Fill commands as appropriate.
Order Conditions from Most Specific to Broadest
Because IFS stops at the first TRUE test, condition order changes the answer. For numeric bands, test the highest threshold first and work downward. If the 70-point test comes before the 90-point test, a score of 95 already satisfies the 70-point test and will never reach the A condition.
For the grade formula above, the thresholds are mutually exclusive in practice because the first matching test wins. A score of 90 meets both the 90 and 80 thresholds, but the 90 threshold appears first, so it returns A. When ranges have lower and upper boundaries, write conditions that make the intended bands clear and check the boundary values, such as 59, 60, 69, 70, and 90.
Text conditions need the same care. An exact comparison such as A2=”North” checks whether the cell matches that text. For more complex tests, Excel’s normal comparison and logical functions can be used inside IFS. If text may contain accidental leading or trailing spaces, clean the source data rather than assuming IFS will treat near-matches as identical.
Combine Several Tests for Each Row
IFS can also classify a row using more than one input column. For example, a label might depend on both a region and a sales amount. Use AND when all conditions in a case must be true, or OR when any one of them is enough. These logical functions test values for the current row; when the formula is filled down, their row references move too.
With the region in B2, the order amount in C2, and the label in D2, enter this formula in D2:
=IFS(AND(B2="North",C2>=1000),"North priority",C2>=1000,"Large order",TRUE,"Standard")
If B2 contains North and C2 contains 1,200, the result is North priority. If the region is South and the amount is 1,200, the result is Large order. Other rows return Standard. The specific North-and-large case comes first so that it is not caught by the broader amount test. Fill D2 down to apply the same decision structure to the remaining rows.
For a rule that accepts either of two regions, OR can appear inside a test, for example OR(B2="North",B2="West"). Keep the paired IFS structure intact: each logical test needs a result, and later cases should not accidentally make earlier cases unreachable.
Make One Formula Return Results for a Range
Filling down creates a separate formula in each result cell. In Microsoft 365, MAP offers another option: it applies a calculation to each item in an input array and returns a dynamic array of results. This can be convenient when you want one formula to populate a range, although it is not required for ordinary row-by-row work.
With scores in A2:A6 and empty output cells below B2, enter this formula in B2:
=MAP(A2:A6,LAMBDA(score,IFS(score>=90,"A",score>=80,"B",score>=70,"C",score>=60,"D",TRUE,"F")))
Excel returns five grades from B2 downward, one for each score in A2:A6. The variable name score represents the current item being processed. MAP and LAMBDA require Microsoft 365 or Excel 2024; they are not a dependable choice for a workbook that must work in older Excel releases. Keep the cells below the formula empty, because existing content in the spill area can cause a #SPILL! error. Dynamic array formulas also cannot spill from inside an Excel table.
For most shared workbooks, a formula filled down is easier to inspect and is more compatible. Use a single MAP formula when the audience has a supported Excel version and the spill behavior is useful for the workbook’s design.
References, Tables, and Blank Cells
When copying formulas, relative references such as A2 change to A3, A4, and so on. If every row should compare against a fixed value stored in E1, lock that reference as $E$1 before filling. The dollar signs keep both the column and row fixed. A mixed reference such as $E1 locks the column but lets the row change.
Excel tables can make this setup easier to maintain. Convert a data range to a table with Insert > Table, then add the formula in the result column. Excel commonly treats a formula entered in a table column as a calculated column and fills it through the table. Structured references, such as a column name in brackets, can make formulas easier to read, but ordinary cell references are often simpler when learning IFS.
Decide explicitly what should happen when an input is blank. A blank cell can satisfy some comparisons in ways that may not match the business rule, so add a blank check first when blanks should produce a blank result. With the score in A2 and the grade in B2, use:
=IFS(A2="","",A2>=90,"A",A2>=80,"B",A2>=70,"C",A2>=60,"D",TRUE,"F")
The first test returns an empty string when A2 is blank; otherwise, the grade rules apply. If zero is a valid score, this formula keeps zero distinct from an empty cell. If a cell contains a formula that returns an empty string, the A2=”” test also treats that result as blank.
IFS Compared with IF and Other Options
IFS can be easier to read than several nested IF functions when there are many alternatives. With only one or two branches, IF may be simpler. IFS does not replace every other function: a lookup table can be a better fit when categories or thresholds change frequently, because you can update the data table rather than edit a long formula.
- Use IFS for a short, ordered set of conditions and their outcomes.
- Use IF for a straightforward two-way decision or when an older Excel version must be supported.
- Use a lookup approach when many threshold values or category mappings are maintained as data.
- Use MAP with IFS when Microsoft 365 or Excel 2024 users need one formula to calculate results for an array.
A nested IF can provide a fallback for older Excel, but it can become difficult to audit as branches accumulate. Whichever method you choose, document unusual rules and test values at every boundary. A formula that is syntactically valid can still encode the wrong priority.
Common Errors and How to Fix Them
The #N/A error usually means that none of the tests was TRUE and no final default case was supplied. Add a final test such as TRUE with the desired fallback result. If #VALUE! appears, inspect the tests and the input data; a function or comparison may be receiving an unexpected value. In a MAP formula, #SPILL! commonly means that cells needed for the returned results are not empty.
- Wrong category: Put higher-priority or more specific conditions first, then check overlapping thresholds.
- Formula does not adjust by row: Check for dollar signs that accidentally lock a row reference. Use relative references for the changing input.
- Formula shows as text: Check whether the cell is formatted as Text or whether an apostrophe precedes the equals sign. Change the format to General, then re-enter the formula.
- Excel rejects commas: Some regional settings use semicolons as formula argument separators. If Excel’s formula-entry settings require semicolons, replace the separators for that installation; the examples here use commas.
- Unexpected blank result: Check whether an early condition matches blank or zero values before the intended rule is reached.
Frequently Asked Questions
Can I use IFS across an entire column?
Yes. Enter a row-based formula in the first result cell and fill it down for the rows you need. In Microsoft 365, MAP can instead return results for a specified range as a spilled array.
Can IFS test more than one condition at a time?
Yes. Put AND or OR inside an IFS logical test when a case depends on multiple checks. For example, AND requires every included test to be true; OR requires at least one.
What happens if no IFS condition is true?
IFS returns #N/A unless you include a final catch-all condition. A final TRUE paired with a fallback value is a common way to define that result.
Does IFS return every matching result?
No. It returns the value paired with the first TRUE condition, then stops. Put the highest-priority or most specific case first.
Why does copying my IFS formula give the same answer in every row?
Check whether the input reference is locked, such as $A$2. If each row should use its own input, use a relative reference such as A2 and fill down.
Can IFS work in Excel 2016?
IFS is not available in Excel 2016. Use nested IF formulas or another method supported by the workbook’s target Excel version if compatibility with Excel 2016 is required.
Related
- Excel Data Model Relationships: How They Work in Reports
- Excel Data Model Calculated Field: What to Use Instead
- Excel Data Model: How to Combine Tables and Analyze Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Paste Values in Excel: Keep Results, Remove FormulasTo get paste values in Excel, copy the cells, select where the results should go, then choose Home…
- How to Filter in Excel and Find the Exact Rows You NeedTo filter a list in Excel, select a cell in your data, choose Data > Filter, then open…
- How to Filter by Color in ExcelTo filter by color in Excel for Microsoft 365 on Windows, turn on filters for your data, open…
- Show Formulas in Excel on iPad: View Cell FormulasIn Excel on iPad, tap a cell to see its formula in the formula bar. To display formulas…
- VLOOKUP Excel Example: Look Up Data Across Two SheetsTo look up a value from one Excel sheet in another, use VLOOKUP with the source sheet in…
- COUNTIF Not Including Zero? Excel Formula ExamplesIn Excel for Microsoft 365, use =COUNTIF(A2:A20,"<>0") to count cells whose values are not zero, assuming your data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.