Function Problems

How to Use Excel IF or IFS Formula to Assign Age Groups

Aamir Naveed Akram Oct 7, 2026 869 views

Question details

The user needs to categorize data into specific age-group brackets (e.g., Under 11, Under 13) based on corresponding school-year numbers.

How to Assign Age Groups Using Excel IFS Formula

Product: Excel
Device & OS: not provided
Scenario: Categorizing school-year numbers into specific age-group brackets for reporting, sports teams, or educational analysis.
Observed behavior: Needs to generate dynamic text labels automatically from a predefined set of numerical thresholds.

Before you start

Ensure your school-year or age data is properly formatted as numerical values (not text) in your spreadsheet before applying logical formulas.

Solution 1Recommended

Use the IFS Formula to Assign Age Groups

The IFS function evaluates multiple conditions in the order they are written and returns a specific text label corresponding to the first condition that evaluates to TRUE.

The IFS function is a cleaner alternative to nested IF statements. When assigning age groups, it is crucial to arrange your conditions from the smallest number to the largest when using the less than or equal to (<=) operator.

Select the target cell

Click on the empty cell where you want the age group label to appear (for example, cell B1 next to your first school-year number in A1).

Enter the IFS formula

Type the formula: =IFS(A1<=2,"Under 11", A1<=4,"Under 13", A1<=6,"Under 15", A1<=8,"Under 17", A1<=10,"Under 19", A1<=12,"Under 21"). Adjust the cell references and thresholds as needed.

Apply to the column

Press Enter to apply the formula. Then, click the small square at the bottom-right corner of the cell and drag it down to fill the formula for the rest of your data.

Use the IFS Formula to Assign Age Groups

Sequential Logic: Because IFS stops at the first TRUE condition, any number 2 or below triggers 'Under 11'. A number like 3 bypasses the first check and triggers 'Under 13'.

Solution 2

Use an Approximate Match VLOOKUP Table

Creating a separate lookup table and using the VLOOKUP function is a highly maintainable method, especially for complex or frequently changing age group rules.

Categorize Data with WPS Spreadsheet

Easily Manage Age Groups and Data Ranges in WPS Office

WPS Spreadsheet fully supports advanced logical functions like IFS and VLOOKUP, allowing you to seamlessly categorize age groups and perform complex data analysis with ease.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your school-year or age data.
2. Select the destination cell: Click on the cell adjacent to your data where the age group label should be displayed.
3. Input the function: Type your =IFS() or =VLOOKUP() formula directly into the formula bar and press Enter.
4. Fill the series: Double-click the fill handle in the bottom-right corner of the active cell to automatically categorize the rest of your dataset.

100% compatible with Microsoft Excel formulas, functions, and .xlsx formats.Built-in support for advanced logical functions including IFS, IF, and XLOOKUP.Free, lightweight, and features an intuitive interface familiar to Excel users.Cross-platform support ensures you can categorize and analyze data on any device.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use nested IF statements instead of IFS?

Yes, if you are using an older version of Excel that does not support the IFS function, you can use nested IF formulas. For example: =IF(A1<=2,"Under 11", IF(A1<=4,"Under 13", "Older")). However, nested IFs can become difficult to read with many conditions.

Why is my IFS formula returning an #N/A error?

The #N/A error occurs in an IFS formula when none of the conditions evaluate to TRUE. To prevent this, you can add a final catch-all condition at the end of your formula using TRUE as the logical test, like TRUE, "Out of Range".

Is VLOOKUP better than IFS for large data ranges?

Yes, VLOOKUP with a lookup table is generally better for large sets of categories. It is easier to maintain and update because you only need to change the values in the lookup table rather than editing the formula itself.

Excel & Data Problems