Spreadsheets

XLOOKUP approximate match for slabs, like cashback or fee tiers

Use XLOOKUP with match_mode -1 to find a cashback or fee rate from a spend-band table, and learn what to do when a value is below the first band.

Editorial illustration for XLOOKUP approximate match for slabs, like cashback or fee tiers

To look up a rate from a tier table, use XLOOKUP with match_mode -1: =XLOOKUP(F2,$A$2:$A$5,$B$2:$B$5,"No tier",-1). Excel finds the exact value if it is there, otherwise the next smaller one. Spend ₹12,500 against bands starting at 0, 5,000, 10,000 and 20,000, and you land on the 10,000 band.

How should the tier table look?

List the lower edge of each band in the first column and the rate in the second. Every number here is invented for the example; use the bands from your own card or plan terms.

A: Spend from (₹)B: Cashback rate
00%
50001%
100002%
200003%

Type the rates as real percentages (format the cells as Percentage) so you can multiply them. In F2 put a month's spend, say ₹12,500, and the formula returns 2%. Then the cashback is =F2*XLOOKUP(F2,$A$2:$A$5,$B$2:$B$5,0,-1), which is ₹250 for a 2% band. At exactly ₹10,000 the exact match returns 2% too; at ₹9,999 you get 1%.

Does the table have to be sorted?

Less strictly than with older functions. XLOOKUP with its normal top-to-bottom search checks every row and returns the largest value that does not exceed yours, so an unsorted table still gives the right answer. Sorting from small to large is still sensible: it is easier to read, and VLOOKUP or MATCH with approximate matching, as well as XLOOKUP's binary search modes (2 and -2), do need it. A table in a random order gives wrong answers in those cases without any error message, so sort first if you switch methods.

What if the spend is below the first band?

  • If your first band starts at 0, nothing can be below it, because a spend cannot be negative. This is the simplest design.
  • If the first band starts at 5,000, a spend of 3,000 returns #N/A. The fourth argument turns that into a word or a number, such as "No tier" or 0.
  • Use 0 for rate calculations so that multiplication still works, and text only when you want a label.

Refunds can make a month's net spend negative. A negative number also falls below the first band, so the fallback covers it.

Is the rate applied to all spend or only to the part in the band?

This is a rule of your card or plan, not of Excel, so read its terms. In a flat-rate tier, all the spend gets the matched rate, which is what the formula above does. In a marginal tier, each band's rate applies only to the portion inside it, like stacked brackets. Take ₹12,500 under the table above treated as marginal: the first 5,000 earns 0%, the next 5,000 earns 1% (₹50) and the last 2,500 earns 2% (₹50), so ₹100 in total, against ₹250 under the flat reading. The two methods differ a lot, so confirm yours.

What are the common mistakes?

  1. Typing the band as text, such as "5,000+". The lookup column must contain plain numbers.
  2. Leaving out the match mode, so Excel looks for an exact spend and returns the fallback almost always.
  3. Mixing monthly and yearly figures: if the bands are monthly, look up one month's spend at a time.
  4. Forgetting the dollar signs when copying the formula down, so the table range slides.

Once you have a month of spend by card, the lookup is quick. A statement import into SpendixAI can give you each card's monthly totals through its reports, so you spend less time adding rows before you test a band.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports