Formula Errors

How to Fix VLOOKUP Returning #N/A Error for Age Ranges

Maira Mehtab Sep 22, 2026 869 views

Question details

The user is experiencing an #N/A error when attempting to use the VLOOKUP function to categorize ages into predefined brackets.

Product: Spreadsheet
Device & OS: not provided
Scenario: Attempting to categorize continuous numeric age values into specific grouped ranges using a VLOOKUP formula.
Observed behavior: The VLOOKUP formula fails and returns an #N/A error because the lookup table relies on text-based ranges (e.g., '30-39' or '70+') rather than correctly formatted numeric lower limits.

Before you start

Ensure your target age column contains actual numeric values and not numbers formatted as text, as this will prevent the approximate match from working.

Solution 1Recommended

Reformat the Lookup Table with Numeric Lower Limits

VLOOKUP's approximate match requires the first column of the lookup array to contain numeric lower limits sorted in ascending order, rather than hyphenated text ranges.

When the fourth argument of VLOOKUP is set to TRUE (or omitted), it performs an approximate match. This means if it cannot find an exact match, it looks for the largest value that is less than or equal to the lookup value. Text strings like '30-39' break this mathematical logic.

Set up numeric lower limits

Create a new lookup table where the first column contains only the lowest number for each age category (e.g., 19, 30, 40, 50, 60, 70).

Assign category values

In the adjacent column, assign the corresponding category labels or values you want returned for that group (e.g., 6, 5, 4, 3, 2, 1).

Sort ascending

Select your entire lookup table array, go to the Data tab, and sort the first column in ascending order (smallest to largest).

Update the VLOOKUP formula

Enter your VLOOKUP formula using TRUE for an approximate match. For example: =VLOOKUP(C2,O$4:P$9,2,TRUE), where C2 is the target age and O$4:P$9 is your new lookup table.

Absolute References: Always use absolute cell references (like O$4:P$9) for your lookup table array so the target range doesn't shift downwards when you drag the formula to apply it to other rows.

Categorize Data Easily with WPS Spreadsheet

Effortlessly Categorize Data and Fix Formula Errors with WPS Office

WPS Spreadsheet provides a robust, user-friendly environment for complex data categorization and formula troubleshooting. It fully supports VLOOKUP, XLOOKUP, and other advanced functions for accurate data analysis.

1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the age data.
2. Create the lookup table: Set up a two-column reference table with ascending numeric limits in the first column and your categories in the second.
3. Apply VLOOKUP: Type =VLOOKUP(lookup_value, table_array, col_index_num, TRUE) into your target cell, lock the table array with F4, and press Enter to map the ages to your categories.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Built-in error checking to quickly identify and troubleshoot #N/A resultsLightweight application with smooth performance for large datasetsComprehensive function library with rich tooltip guidance

microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP still return #N/A when my age is below the first limit?

If the lookup value (e.g., age 15) is smaller than the lowest limit in your sorted array (e.g., 19), VLOOKUP cannot find a smaller value and will return an #N/A error. Ensure your lookup table starts at 0 or the absolute minimum possible value for your dataset.

Can I use XLOOKUP instead of VLOOKUP for age ranges?

Yes. XLOOKUP is often more flexible. You can use XLOOKUP with a match mode of -1 (exact match or next smaller item) to categorize age ranges. It also does not require the lookup array to be strictly sorted in ascending order.

What if my ages are stored as text?

If your ages are stored as text instead of numbers, VLOOKUP's approximate match will fail. You can convert the text to numbers by multiplying the cell by 1, using the VALUE() function, or utilizing the 'Convert to Number' warning prompt in the spreadsheet UI.

Excel & Data Problems