VLOOKUP approximate match: look up a value in a range
With TRUE as the last argument, VLOOKUP stops looking for an exact value and finds the tier a number falls into. That is how you get the commission rate for $37,500 in sales when the table only lists $25,000 and $50,000.
In short: =VLOOKUP(lookup_value,tiers,column,TRUE) returns the row with the largest value in the first column that is less than or equal to the lookup value. The first column holds the lower bound of each tier and must be sorted from smallest to largest.
Try it now
The table on the left is a commission plan. Each row applies from the sales figure in column A upward. Return the commission rate for the sales in F2.
Task The commission tiers are on the left: from $0 in sales the rate is 1%, from $10,000 it is 2%, and so on. In G2, use VLOOKUP to return the rate that matches the sales in F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Sales from $ | Commission rate | Sales rep | Sales $ | Commission rate | ||
| 2 | 0 | 0.01 | Lena Hoffmann | 37500 | |||
| 3 | 10000 | 0.02 | |||||
| 4 | 25000 | 0.03 | |||||
| 5 | 50000 | 0.04 | |||||
| 6 | 100000 | 0.05 |
How approximate match works
An exact match with FALSE needs the lookup value to appear in the first column, otherwise you get #N/A. Tier tables never work like that. Nobody keeps a row for every possible sales figure; the table only says where each tier starts.
| Argument | What it does | In the example |
|---|---|---|
| lookup_value | The number you want to place in a tier | F2 (sales of 37,500) |
| table_array | The tier table. First column: lower bounds, ascending | A2:B6 |
| col_index_num | Which column of the table holds the answer | 2 (rate) |
| range_lookup | TRUE, or leave it out, for approximate match | TRUE |
Think of it as walking down column A and stopping at the last tier that does not go past the lookup value. For 37,500 that is 0, then 10,000, then 25,000. The next tier, 50,000, is too high, so the 25,000 row wins and the formula returns 3%.
A value sitting exactly on a boundary belongs to the higher tier. Sales of exactly 25,000 already earn 3%. Every number in column A reads as "from this value up".
Why the tier table must be sorted
With approximate match Excel does not read the table row by row. It runs a binary search: check a value in the middle, decide whether to continue above or below, halve the range, repeat. That is fast, but it only gives the right answer when the first column goes from smallest to largest.
On an unsorted table the search lands in the wrong place and returns the wrong tier, with no error to warn you. Microsoft's VLOOKUP documentation says the same thing: with TRUE the first column has to be sorted in ascending order or the result may be wrong. Check the order before you trust the numbers.
Practice: a discount for every order
Now the formula fills down a whole order list, so the tier table has to be locked with dollar signs. If that is new to you, read up on absolute references first.
Task The discount tiers are on the right. In C2, use VLOOKUP to return the discount for the order value in B2. The formula is then filled down to C7 and must be right in every row.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Order value $ | Discount | Order value from $ | Discount | |
| 2 | B-2041 | 180 | 0 | 0 | ||
| 3 | B-2042 | 1240 | 250 | 0.02 | ||
| 4 | B-2043 | 499.9 | 500 | 0.05 | ||
| 5 | B-2044 | 2750 | 1000 | 0.08 | ||
| 6 | B-2045 | 500 | 2500 | 0.1 | ||
| 7 | B-2046 | 86 |
Where tier lookups show up at work
- Commission plans: sales thresholds in column A, rates in column B. The commission amount is
=F2*VLOOKUP(F2,A2:B6,2,TRUE). - Volume discounts: order value or quantity from which a discount applies.
- Shipping rates: weight bands starting at 0 kg, 2 kg, 5 kg and so on.
- Customer tiers: annual spend that makes a customer Bronze, Silver or Gold. The result can be text.
- Grades and scores: the point total needed for each grade or rating.
A tier table beats a long chain of nested IF formulas for one simple reason: when a threshold changes, you edit one cell and every formula picks it up.
Practice: shipping cost by weight
This table has three columns and the price sits in the third. Try leaving out the fourth argument; VLOOKUP then uses approximate match on its own.
Task Shipping costs depend on the weight. In F2, use VLOOKUP to return the price for the parcel whose weight is in E2. The price is in the third column of the table.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | From kg | Parcel size | Price $ | Weight kg | Shipping $ | |
| 2 | 0 | S | 4.9 | 7.4 | ||
| 3 | 2 | M | 6.9 | |||
| 4 | 5 | L | 8.9 | |||
| 5 | 10 | XL | 12.9 | |||
| 6 | 20 | XXL | 24.9 |
Common mistakes
The table is not sorted ascending
The classic one, and it can go unnoticed for months. Sort the table by its first column, smallest to largest, with Data > Sort.
#N/A for small values
If the lookup value is below the first threshold, there is no tier to land in and VLOOKUP returns #N/A. Start every tier table at 0, even if the first tier means 0% discount.
Upper limits instead of lower limits
Rate cards are often written as "up to 10,000". VLOOKUP needs the starting value of each tier instead. Rewrite "up to 10,000: 1%, up to 25,000: 2%" as "from 0: 1%, from 10,000: 2%", and decide whether exactly 10,000 belongs to the lower or the upper tier.
Numbers stored as text
Thresholds imported as text no longer compare as numbers. Left-aligned numbers are a typical sign. Convert them to real numbers first.
Using TRUE for IDs
Approximate match is for tiers only. Item numbers, names and IDs need an exact match with FALSE, as covered in the main VLOOKUP guide.
Other ways to look up a tier
XLOOKUP has a match mode for this: =XLOOKUP(F2,A2:A6,B2:B6,,-1), where -1 means "exact match or the next smaller item". According to Microsoft only XLOOKUP's binary search modes require sorted data, so -1 also works on an unsorted table. INDEX and MATCH do it too: =INDEX(B2:B6,MATCH(F2,A2:A6,1)), where match type 1 again expects ascending order.
VLOOKUP with TRUE in German Excel
A German Excel calls the function SVERWEIS, writes TRUE as WAHR and separates arguments with semicolons:
| English | German |
|---|---|
=VLOOKUP(F2,A2:B6,2,TRUE) | =SVERWEIS(F2;A2:B6;2;WAHR) |
| approximate match | ungefähre Übereinstimmung |
| XLOOKUP, INDEX, MATCH | XVERWEIS, INDEX, VERGLEICH |
The exercises accept either spelling.
Which Excel versions support it?
VLOOKUP with approximate match works in every version of Excel, including Excel 2016 and 2019, Excel for Mac and Excel for the web. The XLOOKUP alternative needs Microsoft 365, Excel 2021 or Excel 2024. INDEX with MATCH works everywhere.
Frequently asked questions
What does TRUE mean in VLOOKUP?
TRUE turns on approximate match. VLOOKUP then returns the row with the largest value that is less than or equal to the lookup value. FALSE means exact match.
Is leaving out the last argument the same as TRUE?
Yes. Without a fourth argument VLOOKUP uses approximate match. Writing TRUE anyway makes the intent obvious to the next person who opens the file.
Why does VLOOKUP with TRUE return the wrong value?
Almost always the first column is not sorted ascending, or the table lists upper limits instead of lower limits. Sort it and make sure each row holds the starting value of its tier.
Which tier does a value on the boundary fall into?
The higher one. A row starting at 10,000 applies from exactly 10,000.
Can I do this with IF instead?
You can nest IF or use IFS, but beyond two or three tiers the formula gets hard to read and every threshold change means editing formulas. A tier table with VLOOKUP stays easy to maintain.