How to Return Values from a Range in Excel with XLOOKUP
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.

- 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.
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'.
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).
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.
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).
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.

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. Open your workbook: Launch WPS Office and open your spreadsheet document.
- 2. Prepare the lookup table: Verify that your lookup column contains numerical lower limits instead of text-based ranges.
- 3. Apply the XLOOKUP function: Select your target cell and input =XLOOKUP(F2,A3:A13,C2:D13,"",-1).
- 4. Review dynamic results: Press Enter to see the returned maintenance and setup hours instantly spill across the adjacent cells.

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.




