Function Problems

How to Return Values from a Range in Excel with XLOOKUP

Steve KSteve K Nov 10, 2026 868 views

Question details

The user needs to retrieve corresponding maintenance and setup hours from a lookup table based on a specific parts quantity using the XLOOKUP function.

How to Return Values from a Range in Excel with XLOOKUP
Product
Excel
Device & OS
not provided
Scenario
Looking up a numeric parts quantity within predefined lookup ranges to extract multiple related data points (maintenance and setup hours) simultaneously.
Observed behavior
The formula fails to evaluate the quantity correctly because the lookup column contains text representations of ranges instead of pure numbers, preventing XLOOKUP from executing an approximate match.
Before you start

Ensure your lookup data column contains strictly numerical values representing the lower limit of each tier. XLOOKUP cannot perform mathematical approximate matches on text-based range strings like '11-20' or 'above 100'.

Solution 1Recommended

Convert Text to Numbers and Apply XLOOKUP

By converting text-based range boundaries into standard numbers and using the appropriate match mode, XLOOKUP can accurately evaluate quantity tiers and spill multiple return values.

XLOOKUP is highly versatile but relies on strict data types to perform approximate matches. When evaluating ranges, the lookup array must consist of numbers sorted in ascending order. Using the -1 match mode ensures that if an exact parts quantity isn't found, the formula falls back to the next smaller item (the lower limit of that tier).

1
Convert text ranges to numbers

Select your lookup column (e.g., A3:A13). Remove any text formatting such as '11-20' and replace it with just the numerical lower boundary for each range (e.g., 11). For descriptions like 'above 100', enter the numerical boundary like 101.

2
Enter the XLOOKUP formula

Click on the target cell where you want the maintenance hours to appear (e.g., F7). Type the formula: =XLOOKUP(F2,A3:A13,C2:D13,"",-1).

3
Apply and spill results

Press the Enter key. Because the return array (C2:D13) spans two columns, Excel will automatically calculate the result and dynamically spill the maintenance and setup hours into F7 and G7.

Convert Text to Numbers and Apply XLOOKUP
Dynamic Spilling: By selecting a multi-column return array, XLOOKUP inherently spills the corresponding values into adjacent cells, eliminating the need to write separate VLOOKUP or INDEX/MATCH formulas for each column.
Advanced Spreadsheet Features

Use WPS Spreadsheet to Easily Perform XLOOKUP

WPS Office offers full support for modern functions like XLOOKUP, allowing you to easily handle dynamic arrays, approximate matches, and multi-column lookups with high efficiency.

  1. 1. Open your workbook: Launch WPS Office and open your spreadsheet document.
  2. 2. Prepare the lookup table: Verify that your lookup column contains numerical lower limits instead of text-based ranges.
  3. 3. Apply the XLOOKUP function: Select your target cell and input =XLOOKUP(F2,A3:A13,C2:D13,"",-1).
  4. 4. Review dynamic results: Press Enter to see the returned maintenance and setup hours instantly spill across the adjacent cells.
Fully compatible with Microsoft Excel formulas and file formats (.xlsx), including XLOOKUP.Seamlessly handles dynamic arrays to spill multiple return values across columns automatically.Lightweight architecture ensures fast calculation of complex lookups and massive datasets.Free to download with a highly intuitive user interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP return a #N/A error when matching ranges?

This typically occurs because the lookup column contains text formatting instead of numbers (e.g., typing '10-20' instead of just '10'). XLOOKUP cannot mathematically evaluate text strings for an approximate match. You must convert the column values to pure numbers.

What does the -1 mean in the XLOOKUP formula?

The -1 represents the 'match mode' argument in the XLOOKUP syntax. It instructs the function to look for an exact match first, and if one isn't found, to return the next smaller item. This is essential for evaluating numerical ranges correctly.

Can XLOOKUP return multiple columns of data at once?

Yes. By selecting a multi-column range for the return array argument (such as C2:D13 instead of just C2:C13), XLOOKUP will fetch all corresponding values for the matched row and dynamically spill them into adjacent cells.