Function Problems

Excel Formula to Return Smallest Unused Value at Least 100 Greater

Rana Garcia Sep 27, 2026 869 views

Question details

The user needs an Excel formula to find the smallest available value from a source list that is at least 100 greater than a specific reference value, while ensuring that previously used values are not duplicated.

Product: Excel
Device & OS: not provided
Scenario: Assigning minimum valid values from a pool of data based on a threshold while dynamically preventing the reuse of already assigned items.
Observed behavior: Requires a complex logical calculation that filters out previously used items from the source range and retrieves the next valid number that meets the addition criteria.

Before you start

Ensure your version of Excel or spreadsheet software supports dynamic array functions, as this solution relies on modern functions like LET, FILTER, XMATCH, and XLOOKUP.

Solution 1Recommended

Use a Combination of LET, FILTER, and XLOOKUP

Combine advanced array functions to filter out previously assigned values and locate the smallest valid match remaining in the data.

This method leverages the LET function to create a temporary variable for the filtered array, keeping the formula clean. The FILTER and XMATCH functions work together to remove any numbers that have already been generated in the rows above.

After filtering, the XLOOKUP function searches for the smallest remaining value that meets the minimum threshold requirement (Strength + 100). Exact duplicates in the source array will be treated as separate instances, allowing multiple identical values to be drawn independently.

Prepare your data ranges

Ensure your source values are in a fixed range (for example, $F$2:$F$21) and your threshold or 'Strength' values are listed in an adjacent column (for example, B2).

Enter the array formula

Select your result cell (e.g., C2) and enter the following formula: =LET(cl,FILTER($F$2:$F$21,ISERROR(XMATCH($F$2:$F$21,C$1:C1))),XLOOKUP(B2+100,cl,cl,,1))

Apply to remaining rows

Press Enter to execute the formula, then click and drag the fill handle at the bottom-right of the cell downwards. The expanding range C$1:C1 will dynamically include previously calculated values to prevent reuse.

Use a Combination of LET, FILTER, and XLOOKUP

Understanding the XLOOKUP match mode: The match mode argument '1' at the end of the XLOOKUP function is crucial. It ensures that if an exact match isn't found, the formula returns the next larger item, satisfying the 'at least 100 greater' condition.

Advanced Formulas in WPS Spreadsheet

Use Advanced Array Formulas Seamlessly in WPS Office

WPS Office Spreadsheet fully supports modern dynamic array functions like LET, FILTER, XMATCH, and XLOOKUP. This allows you to build complex data processing logic directly in your spreadsheet without upgrading to expensive software.

1. Open your dataset: Launch WPS Spreadsheet and open your existing .xlsx file containing the source values.
2. Input the array formula: Select the target cell (C2) and paste the exact formula: =LET(cl,FILTER($F$2:$F$21,ISERROR(XMATCH($F$2:$F$21,C$1:C1))),XLOOKUP(B2+100,cl,cl,,1))
3. Fill down the column: Hover over the bottom-right corner of the cell until the crosshair appears, then drag it down to apply the logic to the rest of your list.

Full compatibility with Microsoft Excel's advanced formulas and dynamic arrays.Execute complex logical operations like finding unused thresholds with fast calculation speeds.Fully compatible with .xlsx and .xls file formats for easy sharing.Free to download and use with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a #NAME? error when using this formula?

A #NAME? error typically means your spreadsheet software version does not support the newer functions used in the formula, such as LET or XLOOKUP. Consider upgrading to the latest version of WPS Office or Microsoft 365, which natively support these dynamic array functions.

How does XMATCH prevent duplicate values from being reused?

In this formula, XMATCH cross-references the original source list against the expanding range of cells above the current formula (e.g., C$1:C1). If a value has already been assigned, XMATCH flags it. The ISERROR condition then forces the FILTER function to exclude these used values from the remaining available pool.

Can I change the '100 greater' condition to a different value?

Yes. You can modify the 'B2+100' portion of the XLOOKUP function to any other value or cell reference. For example, to find a value that is at least 50 greater, simply change it to 'B2+50'.

What happens if there are no values large enough remaining?

If there are no unused values left in your source range that are at least 100 greater than your specified base value, the XLOOKUP function will return an #N/A error because no valid match can be found.

Excel & Data Problems