How to Return Text Labels from Numeric Ranges in Excel
Question details
The user needs to assign specific text categories to cells based on predefined numeric ranges.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Categorizing numeric data into distinct text labels based on specific number limits.
- Observed behavior
- To successfully map a continuous range of numbers to specific categorical text values without boundary overlaps.
Ensure your numeric ranges are continuous and non-overlapping. Clearly define the lower and upper bounds for each category before writing your formula to prevent logical errors.
Use the LOOKUP Function to Categorize Numeric Ranges
The LOOKUP function provides a clean, easily adjustable array approach to mapping numbers to text labels.
Using an array lookup is generally cleaner than creating multiple nested IF statements. This method matches an input number against an array of lower bounds and returns the corresponding text category.
Click on the cell where you want the resulting text label to be displayed.
Type the formula =LOOKUP(B14,{0,9,17,30,50},{"Yellow","-","Purple","Pink","-"}), adjusting B14 to reference your source data cell.
Press Enter to see the result, then drag the fill handle from the bottom-right corner of the cell to apply the categorization to the rest of your column.
Apply Nested IF Statements for Strict Logic Rules
Nested IF functions are widely used and allow for explicitly defining strict less-than or greater-than logic for each text label.
Combine CHOOSE and MATCH Functions
This combination separates the boundary matching from the category selection, making it an elegant alternative to long nested IF statements.
Categorize Data Easily with WPS Spreadsheet
WPS Spreadsheet supports all standard advanced formulas including LOOKUP, nested IF, CHOOSE, and MATCH, allowing you to categorize numerical data efficiently for free.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your numerical data.
- 2. Select the destination cell: Click the cell where you want the text label to appear.
- 3. Enter the formula: Type your preferred classification formula, such as =LOOKUP(B14,{0,9,17,30,50},{"Yellow","-","Purple","Pink","-"}).
- 4. Apply formula down: Press Enter, then drag the fill handle down to apply the categorization to the entire column instantly.
Frequently Asked Questions
Why does my nested IF formula return the wrong text label?
Nested IF formulas evaluate conditions in order from left to right. If your logic isn't strictly sequential (e.g., checking <9 then <=16), a number might trigger the wrong condition early. Always arrange your checks in a logical ascending or descending order.
What happens if a number falls outside the specified LOOKUP array?
If the lookup value is smaller than the smallest value in the lookup array, the LOOKUP function returns an #N/A error. Ensure your array starts with a boundary low enough to catch all possible values (like 0 or a negative number).
Can I use VLOOKUP instead of LOOKUP for assigning text labels?
Yes, you can create a separate reference table with your numeric lower boundaries in the first column and text labels in the second column. Then use =VLOOKUP(B14, TableRange, 2, TRUE) to perform an approximate match.
