Learning Excel: Approximate match
Price bands
The price of a ride depends on the distance: a fixed amount up to 3 km, then one band higher each time. There's no row for 7.2 km in the table, and yet VLOOKUP has to find a price.
Approximate match
P2:Q5 holds the price bands: from 0 km a ride costs £6, from 3 km £9, and so on.
With TRUE as the last argument, VLOOKUP doesn't look for an exact match, but takes the largest value that isn't greater than what you're looking for. =VLOOKUP(D2,P2:Q5,2,TRUE) looks for 2.5 km, finds the band from 0 and gives £6.
F2
=VLOOKUP(D2,P2:Q5,2,TRUE)
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Ride | Customer | Zone | Km | Weight | Price | Area | Surcharge | Customer name | Name | Code | Area | From km | Price | |||
| 2 | R01 | K03 | Centre | 2.5 | 3 | £6.00 | Bloom Bakery | K01 | Manchester | 0 | £6.00 | ||||||
| 3 | R02 | K01 | Suburbs | 7.2 | 12 | Book Nook | K02 | Burnage | 3 | £9.00 | |||||||
| 4 | R03 | K05 | Docks | 11.4 | 25 | Cygnet Café | K03 | Manchester | 6 | £12.00 | |||||||
| 5 | R04 | K02 | Centre | 1.8 | 1 | Dot Print | K04 | Didsbury | 10 | £18.00 | |||||||
| 6 | R05 | K03 | Suburbs | 5.5 | 8 | Fork & Spoke | K05 | Withington | |||||||||
| 7 | R06 | K04 | Docks | 9.9 | 18 | ||||||||||||
| 8 | R07 | K01 | Centre | 3 | 4 | ||||||||||||
| 9 | R08 | K02 | Suburbs | 6 | 10 | Zone | Centre | Suburbs | Docks | ||||||||
| 10 | R09 | K05 | Docks | 14 | 30 | Surcharge | £0.00 | £2.00 | £5.00 | ||||||||
| 11 | R10 | K06 | Centre | 4.2 | 2 | ||||||||||||
| 12 | |||||||||||||||||
| 13 | Position | Weight | Centre | Suburbs | Docks | Class | Medium | ||||||||||
| 14 | Light | £0.00 | £1.00 | £3.00 | Zone | Docks | |||||||||||
| 15 | Medium | £2.00 | £3.00 | £5.00 | Result | ||||||||||||
| 16 | Heavy | £4.00 | £6.00 | £8.00 | |||||||||||||
| 17 |
Try it yourself
Ride R06 is 9.9 km. Work out the price in F7.
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Ride | Customer | Zone | Km | Weight | Price | Area | Surcharge | Customer name | Name | Code | Area | From km | Price | |||
| 2 | R01 | K03 | Centre | 2.5 | 3 | Bloom Bakery | K01 | Manchester | 0 | £6.00 | |||||||
| 3 | R02 | K01 | Suburbs | 7.2 | 12 | Book Nook | K02 | Burnage | 3 | £9.00 | |||||||
| 4 | R03 | K05 | Docks | 11.4 | 25 | Cygnet Café | K03 | Manchester | 6 | £12.00 | |||||||
| 5 | R04 | K02 | Centre | 1.8 | 1 | Dot Print | K04 | Didsbury | 10 | £18.00 | |||||||
| 6 | R05 | K03 | Suburbs | 5.5 | 8 | Fork & Spoke | K05 | Withington | |||||||||
| 7 | R06 | K04 | Docks | 9.9 | 18 | ||||||||||||
| 8 | R07 | K01 | Centre | 3 | 4 | ||||||||||||
| 9 | R08 | K02 | Suburbs | 6 | 10 | Zone | Centre | Suburbs | Docks | ||||||||
| 10 | R09 | K05 | Docks | 14 | 30 | Surcharge | £0.00 | £2.00 | £5.00 | ||||||||
| 11 | R10 | K06 | Centre | 4.2 | 2 | ||||||||||||
| 12 | |||||||||||||||||
| 13 | Position | Weight | Centre | Suburbs | Docks | Class | Medium | ||||||||||
| 14 | Light | £0.00 | £1.00 | £3.00 | Zone | Docks | |||||||||||
| 15 | Medium | £2.00 | £3.00 | £5.00 | Result | ||||||||||||
| 16 | Heavy | £4.00 | £6.00 | £8.00 | |||||||||||||
| 17 |
F7
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson