Function Problems

Categorize Ages in Excel with a LOOKUP Formula

Phi Hung VoPhi Hung Vo Nov 22, 2026 868 views

Question details

Classify ages into four groups using an Excel formula.

Categorize Ages in Excel with a LOOKUP Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains ages that need to be labeled Youth, Middle-aged, Retired, or Old according to specified age ranges.
Observed behavior
Ages from 0 through 34 should be labeled Youth, 35 through 59 Middle-aged, 60 through 79 Retired, and 80 or older Old.
Before you start

Enter each age as a nonnegative whole number. The examples use cell E1 for the age; change the cell reference if your data is elsewhere.

Solution 1Recommended

Use LOOKUP to assign an age category in Excel

Use this formula to match each age to the lower boundary of its category.

LOOKUP finds the largest threshold that does not exceed the age. Keep the thresholds in ascending order.

1
Enter an age

Type an age, such as 45, in cell E1.

2
Add the formula

Select an empty cell and enter =LOOKUP(E1,{0,35,60,80},{"Youth","Middle-aged","Retired","Old"}), then press Enter.

3
Apply it to other rows

Drag the formula cell's fill handle down alongside the ages in column E. Excel adjusts the cell reference for each row.

Use LOOKUP to assign an age category in Excel
Check age values: A negative age is below the first threshold and returns #N/A. This formula assumes ages are nonnegative whole numbers.
WPS Spreadsheets

Classify age groups in WPS Spreadsheets

Use the same LOOKUP formula in WPS Spreadsheets to label ages and fill the results down a column.

  1. 1. Open the worksheet: Open WPS Spreadsheets and select Open to load your workbook.
  2. 2. Check the age column: Enter or locate nonnegative whole-number ages in column E, starting in E1.
  3. 3. Enter the formula: Select an empty cell beside the first age and enter =LOOKUP(E1,{0,35,60,80},{"Youth","Middle-aged","Retired","Old"}), then press Enter.
  4. 4. Fill the remaining rows: Drag the formula cell's fill handle down to categorize the other ages.
Classify ages using four LOOKUP thresholdsOpen and save Microsoft Excel XLSX filesFill formulas down a column for multiple records
microsoft office alternative - wps office

Frequently Asked Questions

Which category does age 35 return?

The formula returns Middle-aged for 35. The thresholds 35, 60, and 80 are included in their respective categories.

How do I use the formula if the age is in another cell?

Replace E1 in the formula with the cell containing the age. For example, use B2 if the first age is in cell B2.

Why does the formula return #N/A for a negative age?

The first LOOKUP threshold is 0, so a negative value is below every listed threshold. Check the age in the source cell and enter a nonnegative whole number.

How can I change the category names?

Edit the text labels in the formula's second array. Keep the labels in the same order as their corresponding thresholds: 0, 35, 60, and 80.