Shipping Rate Bands with INDEX and XMATCH
Excel version: XMATCH needs Excel 2021 or Microsoft 365; INDEX with MATCH (match type 1) works in every version.
Your task: In C2:C6, find each parcel's shipping rate from the weight bands in E2:F6.
Full instructions, scenario and goal
Scenario
A small online shop charges shipping by parcel weight. The band table in E2:F6 gives the rate for each band, identified by its lower weight limit. Five parcels are waiting to be shipped and the warehouse wants each rate worked out from the table, so the sheet still works when the rates or weights change.
Your task
- In C2, find the shipping rate for the first parcel, using the band table in E2:F6.
- Copy the formula down to C6. Make sure the table references stay fixed.
- A parcel that weighs exactly the lower limit of a band belongs to that band.
- Select Check Answer when you are done.
You are done when
- Each parcel gets the rate of the band it falls in.
- Weights that are exactly on a limit use the higher band.
- The table is referenced, not typed.
What you will practise
Look a number up in a table of bands by finding the largest lower limit that does not exceed it.
The idea in 30 seconds
Many tables are bands, not exact values: parcels from 0 to under 1 kg cost one rate, from 1 to under 5 kg another, and so on. The table lists only the lower limit of each band, and a weight belongs to the band with the largest lower limit that is not above it. An exact match will fail for most weights, because 3.2 is not in the list of limits.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell C2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
Many tables are bands, not exact values: parcels from 0 to under 1 kg cost one rate, from 1 to under 5 kg another, and so on. The table lists only the lower limit of each band, and a weight belongs to the band with the largest lower limit that is not above it. An exact match will fail for most weights, because 3.2 is not in the list of limits.
XMATCH(value, list, -1) finds the exact match or, if there is none, the next smaller item, and returns its position. Hand that position to INDEX to return the rate from the same row. The list of limits does not need to be sorted for this mode, unlike the old approximate VLOOKUP, and a weight exactly on a limit (5 kg) belongs to the band that starts there. Lock the table references with dollar signs so you can copy the formula down.
Skills you will practise
- XMATCH
- INDEX
- Approximate match
- Banded lookup
- Absolute references
Topic guide: Excel Lookup Functions Practice: VLOOKUP, XLOOKUP, INDEX MATCH
Complete first: Look Up a Shipping Rate with INDEX and MATCH
Common mistakes to avoid
- Using exact match
- Most weights are not in the limit list, so an exact match returns #N/A.
- Matching the next larger limit
- That puts every parcel in the band above the one it belongs to and overcharges the customer.