Skip to content
GridBelt
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign in
Start the free test
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign inCreate account
Start the free test
GridBelt

Practice Excel in your browser.

Excel Test

  • Level 1: Beginner
  • Level 2: Intermediate
  • Level 3: Expert
  • Excel test in 7 days
  • Leaderboard
  • Excel test for interview
  • Indeed Excel assessment

Practice

  • All exercises
  • Programs
  • For beginners

Guides

  • Excel formulas
  • Excel shortcuts

Company

  • About
  • Pricing
  • How we grade

© GridBelt. All rights reserved.

  • Privacy Policy
  • Terms of Use
  • Refund Policy

Excel is a trademark of Microsoft Corporation. Indeed, TestGorilla, SHL and other marks are the property of their owners. GridBelt is an independent practice site and is not affiliated with, endorsed by or sponsored by Microsoft, TestGorilla, SHL or Indeed.

  1. Practice
  2. /
  3. VLOOKUP

VLOOKUP approximate match practice: shipping cost by weight band

  • Intermediate
  • ~7 min
  • VLOOKUP

A fulfilment team ships 80 parcels a day and prices them by weight band: 6 bands from under 1 kg to 20 kg and above, listed in F2:H7 with the lower bound of each band in column F and its cost in column H. Weights have one decimal and almost never equal a bound, so an exact-match lookup is useless here. Price every parcel with one formula that finds the band a weight falls into — the approximate match that VLOOKUP was built for.

What you need to do

  1. 1Check the band table first: column F must be sorted ascending (0, 1, 2, 5, 10, 20) — approximate VLOOKUP relies on that order.
  2. 2Click D2 and type =VLOOKUP(C2, then select F2:H7 and press F4 (Windows) or ⌘T (Mac) to lock it as $F$2:$H$7.
  3. 3Type ,3,TRUE) and press Enter: 3 is the Cost column and TRUE asks for the largest lower bound that is less than or equal to the weight.
  4. 4Fill D2 down to D81. Check a parcel that weighs exactly 5.0 kg: it must get the 5–9.9 kg price, because the lower bound is inclusive.

Answer cells

Feedback

Type your formula in the grid, press Enter, then Check. We grade the value in each answer cell and the formula behind it.

Tasks

  1. Not checkedIn D2, return the shipping cost for the weight in C2 from the band table F2:H7 with an approximate-match lookup (VLOOKUP with TRUE), then fill the formula down to D81.

Desktop recommended for spreadsheet tasks. The sheet scrolls inside this box.

StartInsertFormulasDataView
A1fxPreparing your workbook…
A
B
C
D
E
F
G
H
I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Enter your formula in the sheet, then check.

About this exercise

A 80-row dataset on one sheet, one answer cell, about 7 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.

How to solve it

  • D2=VLOOKUP(C2,$F$2:$H$7,3,TRUE)

With TRUE as the last argument VLOOKUP no longer looks for the weight itself; it walks down the sorted lower bounds in column F and stops at the last one that is less than or equal to the weight, then returns the cost from that row. A 2.4 kg parcel passes 0, 1 and 2 but not 5, so it gets the 2–4.9 kg price; a parcel weighing exactly 5.0 kg matches the 5 kg bound and gets the 5–9.9 kg price. The band table does the pricing, so changing a bound or a cost in F:H reprices every parcel at once.

Pitfall 1: approximate match requires the first column sorted ascending; an unsorted table returns plausible-looking wrong prices with no error, which is why TRUE is dangerous on anything but a bands table. Pitfall 2: a weight below the first bound (impossible here because the table starts at 0) returns #N/A — always start a bands table at 0 or at the smallest possible value. Pitfall 3: writing the bands as nested IFs (=IF(C2<1,4.9,IF(C2<2,6.5,…))) hard-codes every cost and bound into 80 formulas; the next price change means editing all of them, and the grader flags the hard-coded costs. Alternatives: =XLOOKUP(C2,$F$2:$F$7,$H$2:$H$7,,-1) (match mode −1 = exact or next smaller item, no sort order required), =XLOOKUP(C2,$G$2:$G$7,$H$2:$H$7,,1) against the upper bounds (next larger), =INDEX($H$2:$H$7,MATCH(C2,$F$2:$F$7,1)) and the compact =LOOKUP(C2,$F$2:$F$7,$H$2:$H$7). Modeling note: the same pattern prices tax brackets, commission tiers, volume discounts and grade boundaries — anything defined by thresholds.

Other ways that work

  • =VLOOKUP(C2,$F$2:$H$7,3) — the 4th argument defaults to TRUE
  • =XLOOKUP(C2,$F$2:$F$7,$H$2:$H$7,,-1) — Excel 365; match mode −1 returns the exact match or the next smaller bound
  • =INDEX($H$2:$H$7,MATCH(C2,$F$2:$F$7,1)) — MATCH type 1 is the approximate match
  • =LOOKUP(C2,$F$2:$F$7,$H$2:$H$7) — LOOKUP is always approximate

Common mistakes

Using FALSE out of habit: =VLOOKUP(C2,$F$2:$H$7,3,FALSE) looks for a bound equal to the weight, so 2.4 returns #N/A and only parcels that weigh exactly 1.0, 2.0, 5.0, 10.0 or 20.0 kg get a price.

How we grade

Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.

Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.

In these programs

  • Excel test in 7 days, Level 2 planDay 3
  • Excel for Data AnalystsDay 7
  • Excel for Sales and MarketingDay 4

Related exercises

  • Pull the unit price with VLOOKUP (exact match)Beginner, ~5 min
  • Show "Not found" instead of #N/AIntermediate, ~7 min
  • Pick the month column with MATCHIntermediate, ~7 min
  • Replace VLOOKUP with XLOOKUPIntermediate, ~6 min

Function reference

Official documentation

Next exercise

Show "Not found" instead of #N/A