Look Up a Shipping Rate with INDEX and MATCH
Your task: In I2, find the shipping cost for the zone and weight band in each quote, then fill it down.
Full instructions, scenario and goal
Scenario
A parcel carrier publishes its prices in a table: the weight band is down the side and the delivery zone is across the top. Customer service has three quotes to give, each naming a zone and a weight band, and needs the cost for each without reading the table by eye. The carrier re-issues the price table every quarter.
Your task
- Look at the price table in A1:E6 and the three quotes in G2:H4.
- In I2, find the cost for the zone in G2 and the weight band in H2, using the table. Use $ signs on the table and its labels so the formula can be copied.
- Press Enter, then copy I2 and paste it into I3:I4.
- Select Check Answer.
You are done when
- I2:I4 contain formulas that find the cost from the zone and the weight band in each row.
- The row and the column are found by matching the labels, not by typing positions.
- The formulas still work if the quotes change or the price table is re-issued.
What you will practise
Combine INDEX with two MATCH functions to read one cell of a table from a row label and a column label.
The idea in 30 seconds
MATCH finds where a value sits in a list and returns its position: 1 for the first item, 2 for the second, and so on. The last argument chooses how: 0 asks for an exact match, which is what you want for names and codes. INDEX does the opposite job: give it a range and a position, and it returns the value at that position.
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 I2. 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
MATCH finds where a value sits in a list and returns its position: 1 for the first item, 2 for the second, and so on. The last argument chooses how: 0 asks for an exact match, which is what you want for names and codes. INDEX does the opposite job: give it a range and a position, and it returns the value at that position.
Used together they read a table the way a person does: find the row from the row label, find the column from the column label, then take the cell where they cross. INDEX accepts a row number and a column number, so one MATCH supplies each. Because both positions come from the labels themselves, the formula keeps working when the table is re-ordered or extended.
Skills you will practise
- INDEX
- MATCH
- Two way lookup
- Absolute references
Topic guide: Excel Lookup Functions Practice: VLOOKUP, XLOOKUP, INDEX MATCH
Complete first: Look Up a Product's Price
Common mistakes to avoid
- Row and column the wrong way round
- INDEX takes the row number first and the column number second. Putting them in the other order reads a different cell, or a cell that does not exist.
- A MATCH range that includes the wrong cells
- The positions must be counted inside the range you give INDEX. If the labels MATCH searches start one cell earlier than the price block, every position is off by one.
- Typing the row and column numbers
- INDEX(table,3,3) gives the right answer for one quote and the wrong one for every other.