VLOOKUP with approximate match
IntermediateShipping teams often price delivery in tiers (for example: 0–1 lb, 1–5 lb, 5–10 lb, and so on). In Excel, this is a classic use case for VLOOKUP with an approximate match.
In this sheet, you have:
- A list of orders with package weights in C5:C9
- A tiered rate table in G5:H9 (minimum weight → rate per lb)
Your task
Fill in D5:D9 with a formula that looks up the correct Rate ($/lb) based on each row’s Weight (lb).
Requirements:
- Use VLOOKUP with approximate match (TRUE) so each weight picks the highest tier that is less than or equal to the weight.
- Lock the rate table range so you can copy the formula down.
- Copy your formula from D5 down through D9.
When the rates are correct, the Shipping cost column will calculate automatically.
Need some help?
Hint 1
Approximate match means the first column of the lookup table must be sorted from smallest to largest (the table in G5:H9 is already in that order).
Hint 2
In D5, your lookup value is the weight in C5. Your table_array is the rate table, and the return column is the rate (column 2 of that table).
Hint 3
Use absolute references (with $) for the table range so the lookup table doesn’t shift when you fill down.
Answer
Exercise
VLOOKUP with approximate match
IntermediateShipping teams often price delivery in tiers (for example: 0–1 lb, 1–5 lb, 5–10 lb, and so on). In Excel, this is a classic use case for VLOOKUP with an approximate match.
In this sheet, you have:
- A list of orders with package weights in C5:C9
- A tiered rate table in G5:H9 (minimum weight → rate per lb)
Your task
Fill in D5:D9 with a formula that looks up the correct Rate ($/lb) based on each row’s Weight (lb).
Requirements:
- Use VLOOKUP with approximate match (TRUE) so each weight picks the highest tier that is less than or equal to the weight.
- Lock the rate table range so you can copy the formula down.
- Copy your formula from D5 down through D9.
When the rates are correct, the Shipping cost column will calculate automatically.
Need some help?
Hint 1
Approximate match means the first column of the lookup table must be sorted from smallest to largest (the table in G5:H9 is already in that order).
Hint 2
In D5, your lookup value is the weight in C5. Your table_array is the rate table, and the return column is the rate (column 2 of that table).
Hint 3
Use absolute references (with $) for the table range so the lookup table doesn’t shift when you fill down.