Function Problems

How to Use Excel Formulas to Return Text Based on Number Ranges

Kushani Nimanthika Oct 23, 2026 868 views

Question details

The user needs to output specific text strings in a cell based on whether a number in another cell falls within predefined numeric ranges.

How to Return Text Based on Number Ranges in Excel

Product: Excel
Device & OS: not provided
Scenario: Categorizing numeric data (such as test scores or sales figures) by assigning specific text labels across three or more predefined ranges.
Observed behavior: The user initially considered conditional formatting but realized it only alters cell appearance and cannot output or change the actual text string in a neighboring cell.

Before you start

Before writing your formulas, clearly define the minimum and maximum values for each of your number ranges and decide on the exact text you want to output for each. Ensure there are no gaps or overlapping boundaries.

Solution 1Recommended

Use the IFS Function to Evaluate Multiple Conditions

The IFS function evaluates multiple conditions in order and returns the text corresponding to the first true condition, making it much easier to read than nested IF statements.

When dealing with continuous number ranges, you can structure your IFS function to evaluate from the highest value to the lowest (or vice versa). Because the function stops at the first TRUE condition, you don't need to specify both the upper and lower bounds for every range.

Select the Target Cell

Click on the cell where you want the text label to appear (for example, D6, adjacent to your numeric value in C6).

Enter the IFS Formula

Type `=IFS(` to begin the function. Enter your conditions sequentially based on your boundaries. For example: `=IFS(C6>=10, "Excellent", C6>=6, "Satisfactory", C6<6, "Needs Improvement")`.

Apply and Fill Down

Press Enter to execute the formula. Click the cell again, grab the fill handle in the bottom-right corner, and drag it down to apply the formula to the rest of your data column.

Use the IFS Function to Evaluate Multiple Conditions

Handling Unmatched Values: If a number doesn't meet any of the specified conditions, IFS will return an #N/A error. You can prevent this by adding TRUE as the final condition, followed by a fallback text string (e.g., TRUE, "Out of Range").

Solution 2

Use a Lookup Table and VLOOKUP for Larger Range Sets

If you have more than three ranges, creating a lookup table and using an approximate match VLOOKUP is cleaner and easier to manage than typing a long conditional formula.

Powerful Spreadsheet Capabilities

Efficiently Categorize Data with WPS Spreadsheets

WPS Spreadsheets provides comprehensive support for modern logical functions, including IFS, XLOOKUP, and nested IFs. You can easily apply and manage complex formulas to automatically categorize your data streams.

1. Open Your Data File: Launch WPS Spreadsheets and open the workbook containing the numbers you need to categorize.
2. Access the Formula Tab: Select the target cell, click the 'Formulas' tab on the top ribbon, and choose 'Insert Function'.
3. Select the IFS Function: Search for 'IFS' in the function dialog box and click OK. A prompt will appear guiding you to input your logical tests and desired text values.
4. Apply the Changes: Click OK to apply the formula, then drag the fill handle to categorize the rest of your dataset.

Fully compatible with Microsoft Excel (.xlsx) formats and standard logical functions.Features a built-in Formula Builder to help you insert and troubleshoot complex statements without memorizing syntax.Lightweight, fast, and completely free for everyday data categorization and analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't conditional formatting work for returning text?

Conditional formatting is strictly a visual tool. It can dynamically change the background color, font style, or borders of a cell based on a numeric range, but it cannot alter the actual data or output new text values into a cell. Formulas are required to generate data.

What is the difference between the IFS function and nested IF statements?

Nested IF statements require you to write multiple IF functions inside one another, which can quickly become long and difficult to read. The IFS function simplifies this by allowing you to list multiple conditions and their corresponding results sequentially within a single function.

Can I use XLOOKUP instead of VLOOKUP for number ranges?

Yes. XLOOKUP is a highly versatile alternative. To evaluate ranges, use XLOOKUP and set the 'match_mode' argument to -1. This tells the function to search for an exact match, and if none is found, return the next smaller item, which effectively evaluates your numeric thresholds.

Excel & Data Problems