Only exact quantities work
Use match mode -1, not 0, to include quantities between thresholds.
Choose a rate from quantity bands
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill E2:E4 with the rate for each quantity in D2:D4. Choose the largest minimum quantity less than or equal to the query, regardless of table order. Return Below minimum when no band qualifies and Review quantity for negative inputs. This selects one flat rate, not a progressive charge.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: E2, E3, E4. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Find the rate attached to the largest threshold at or below each quantity, with explicit rules for missing bands and negative inputs.
THE QUICK ANSWER01
IF separates negative quantities. XLOOKUP match mode -1 selects an exact threshold or the next smaller one; search mode 1 scans the table without requiring a sorted binary-search input.
ƒx
=IF(D2<0,"Review quantity",XLOOKUP(D2,$A$2:$A$6,$B$2:$B$6,"Below minimum",-1,1))For the example data
E2 10E3 8E4 Review quantity
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
A band is defined by its lower bound
The rule chooses the greatest qualifying threshold, not the closest value by distance. With thresholds 10 and 25, quantity 24.999 still belongs to the 10 band. At exactly 25 it changes bands.
A flat rate is not a progressive total
The output is one selected rate. A progressive calculation would apply different rates to portions of a quantity and needs a different task definition. The rates here are invented teaching data.
Supported inputs and duplicates
Use finite numeric quantities, distinct nonnegative numeric thresholds and nonnegative numeric rates. Negative quantities return a review label. Duplicate thresholds, blanks, text numbers and booleans remain ungraded because they need an explicit data-cleaning or tie policy.
Read the full step-by-step walkthrough
- Read the threshold rule
Each row starts a band. A quantity of 25 uses the row whose minimum is 25; a quantity just below 25 uses the nearest smaller threshold. This task returns the flat rate, without multiplying it by quantity.
- Separate negative quantities
The task treats a negative quantity as a review case. Check that rule before asking the lookup to choose a band. Zero can be a valid query.
- Choose exact or next smaller
The fifth XLOOKUP argument is match mode -1. Exact-only mode 0 misses in-between quantities; mode 1 chooses the next larger threshold and assigns the wrong band.
- Keep table order independent
The final argument 1 searches first to last. Binary search modes need sorted data. The practice cases deliberately reorder thresholds and their rates together.
- Set a missing-band result
If a nonnegative quantity lies below every minimum, return Below minimum. This case is different from a negative query and from a valid zero rate.
- Fill while fixing the source ranges
Keep the table references absolute and the query row relative. Fill E2 down to E4, then check all datasets so exact boundaries and reordered rows are tested too.
Things to watch
A quantity gets the next band too early
Mode 1 chooses the next larger item. This task assigns bands by the next smaller threshold.
Reordered tables return different rates
Do not use binary search on these unsorted cases. Keep each threshold paired with its rate and use search mode 1.
About this example
Browser formula entry, supported filling, all supplied datasets, independent grading, retries and XLSX downloads were checked in Chrome on October 17, 2026. Checks cover these task rules and examples. Native Microsoft Excel and Google Sheets recalculation remain unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.