How to Use XLOOKUP to Return the Next Largest or Smallest Value
Question details
The user wants to configure the XLOOKUP function to return a value from an array that is strictly the next largest or next smallest compared to a specific lookup value.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Searching for a numeric threshold in a dataset where an exact match does not exist, requiring the formula to identify the immediate next highest or lowest available value instead.
- Observed behavior
- XLOOKUP's standard search modes do not natively evaluate strictly greater-than or less-than comparisons on their own, requiring a logical array to force a TRUE match for candidates above or below the threshold.
Ensure your data array is sorted in the correct order (ascending for next largest, descending for next smallest). Since this method returns the first matching logical TRUE, sorting ensures the first match is also the mathematically closest value.
Use a Logical Comparison to Find the Next Smallest Value
Create an array of TRUE/FALSE values to identify numbers smaller than your target, then use XLOOKUP to return the first TRUE match.
By setting the lookup value to TRUE and replacing the lookup array with a logical expression, XLOOKUP will evaluate each cell against your condition.
Click on the cell where you want the next smallest value to be displayed.
Type the formula =XLOOKUP(TRUE, B3:B7<B2, B3:B7) into the formula bar. In this example, B2 is your target lookup value, and B3:B7 is the range containing your dataset.
Press Enter. The formula checks each cell in B3:B7, finds those strictly less than B2, and returns the first one that matches.
Modify the Formula to Find the Next Largest Value
Change the logical operator in the formula to greater-than (>) to find the next value above your lookup target.
Master Complex XLOOKUP Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports modern functions like XLOOKUP, allowing you to perform advanced dynamic arrays, logical comparisons, and complex data lookups quickly and accurately.
- 1. Download WPS Office: Install WPS Office for free from the official website and open WPS Spreadsheet.
- 2. Open your dataset: Load your existing Excel workbook or create a new spreadsheet with your data.
- 3. Apply the XLOOKUP formula: Select an empty cell and type your =XLOOKUP() formula exactly as you would in Microsoft Excel.
- 4. Get instant results: Press Enter to instantly calculate your logical arrays and retrieve the correct value.
Frequently Asked Questions
Why is XLOOKUP returning a value that isn't the closest match?
If XLOOKUP returns a value that is larger (or smaller) but not the closest to your target, your lookup array is likely unsorted. The formula =XLOOKUP(TRUE, range>target, range) returns the very first match it finds from top to bottom. Sorting your data correctly ensures the first match it evaluates is also the closest numerical match.
Does XLOOKUP's built-in match_mode work for finding the next largest value?
XLOOKUP does have a match_mode argument (-1 for exact match or next smaller, 1 for exact match or next larger). However, it requires evaluating the exact lookup value against the array first. If you specifically need to compare arrays dynamically (like strictly greater than without checking exact matches), using the TRUE logical array method provides much more precise control.
Can I use this XLOOKUP method to return a value from an adjacent column?
Yes. The third argument in the formula dictates the return array. For example, using =XLOOKUP(TRUE, B3:B7>B2, C3:C7) will check column B for the next largest value based on your criteria, but it will return the corresponding result from column C.
What does 'TRUE' mean as the first argument in the XLOOKUP formula?
Using 'TRUE' tells the XLOOKUP function to search for the boolean value TRUE. Since the second argument (like B3:B7>B2) generates a temporary array of TRUE and FALSE results based on your logical condition, XLOOKUP simply locates the first TRUE in that generated array and returns its position.
