Lookup & Reference
Find the exact or next-larger capacity tier with XLOOKUP
Select the smallest listed capacity meeting a requested quantity and return a clear message when none qualifies.
Choose the direction of approximate matching deliberately
This fictional stock-package table contains capacities15,20,30 and50 units. For a request of24, the smallest listed capacity at least24 is30. XLOOKUP match mode1 asks for an exact match or next larger value; a next-smaller lookup answers a different question.
The default linear search can use an unsorted table. Do not switch to binary search without satisfying its sorting requirements. Keep duplicate capacities unambiguous: the first exact match in source order is returned.
Capacity minus requested quantity is a numerical difference, not an engineering safety margin. Real electrical, structural or shipping selection requires additional rules not represented by a single threshold. Missing numeric capacities or a nonnumeric/negative request are rejected.
| Capacity | Package | Example selection |
|---|---|---|
| 15 | Package15 | Request0 through15 |
| 20 | Package20 | Exact20 |
| 30 | Package30 | Request24 |
| 50 | Package50 | Request65 exceeds available capacities |
Select an exact or next-larger package
Finds an exact rating match or steps UP to the smallest rating strictly greater than D2.
=IF(OR(NOT(ISNUMBER(D2)),COUNT(A2:A5)<>4),"Invalid inputs",IF(OR(D2<0,MIN(A2:A5)<=0),"Invalid inputs",XLOOKUP(D2,A2:A5,B2:B5,"Exceeds maximum",1)))Unused capacity in the chosen package
Check for an above-maximum request before subtraction. Otherwise a numeric zero fallback would produce a misleading negative capacity.
=IF(OR(NOT(ISNUMBER(D2)),COUNT(A2:A5)<>4),"Invalid inputs",IF(OR(D2<0,MIN(A2:A5)<=0),"Invalid inputs",IF(D2>MAX(A2:A5),"Exceeds maximum",XLOOKUP(D2,A2:A5,A2:A5,0,1)-D2)))Try the example
Use the original values or change them to test your understanding.