Spreadsheets

XLOOKUP from the bottom: pull the latest amount paid to a biller

Use XLOOKUP search_mode -1 to pull the latest electricity bill amount from a long log without sorting, and see the LOOKUP(2,1/...) trick for older Excel.

Editorial illustration for XLOOKUP from the bottom: pull the latest amount paid to a biller

To get the most recent amount paid to a biller, use XLOOKUP's last argument: =XLOOKUP(F1,$B$2:$B$500,$C$2:$C$500,"Not found",0,-1). The -1 tells Excel to search from the bottom of the list upward, so the first match it meets is the last one in the log. Your log stays in the order you typed it.

How are the arguments arranged?

XLOOKUP takes six arguments in a fixed order, and the last two are easy to mix up:

PositionArgumentIn this formula
1lookup_valueF1, the biller name such as Electricity
2lookup_array$B$2:$B$500, the biller column
3return_array$C$2:$C$500, the amount column
4if_not_found"Not found"
5match_mode0, exact match
6search_mode-1, last to first

Because the fifth argument comes before the sixth, you must fill the match mode (0) even though it is the default, in order to reach the search mode. Skipping it by typing two commas also works: ,,-1.

What does it return on a sample log?

Say the log has dates in A, billers in B and amounts in C, in the order you entered them:

DateBillerAmount
05-JunElectricity₹1,840
12-JunWater₹310
04-JulElectricity₹2,150
05-AugElectricity₹2,380

With F1 set to Electricity, the default top-down search would return ₹1,840, the oldest. With search mode -1 it returns ₹2,380. To also show the date of that bill, point the return array at column A instead: =XLOOKUP(F1,$B$2:$B$500,$A$2:$A$500,"Not found",0,-1).

What if my log is not in date order?

Search mode -1 finds the last row, not the latest date. If you add old bills at the bottom, you will get the wrong one. Two options: keep the log in date order, or ask for the latest date explicitly:

  • =XLOOKUP(1,($B$2:$B$500=F1)*($A$2:$A$500=MAXIFS($A$2:$A$500,$B$2:$B$500,F1)),$C$2:$C$500,"Not found")

MAXIFS finds the newest date for that biller (Excel 2019 onward), and the multiplication keeps only the row where biller and date both match. If two bills share a date, you get the first of them.

What about older Excel without XLOOKUP?

The long-standing trick is =LOOKUP(2,1/($B$2:$B$500=F1),$C$2:$C$500). Where the biller matches, 1 divided by TRUE is 1; elsewhere it is a #DIV/0! error. LOOKUP searches for 2, which is not in the list, so it settles on the last number-1 it can find, ignoring the errors. It works in versions that predate XLOOKUP and needs no special entry.

MethodWorks inReturns
XLOOKUP with -1Excel 365 and 2021Last matching row
LOOKUP(2,1/...)Almost every ExcelLast matching row
XLOOKUP with MAXIFSExcel 365 and 2021Row with the latest date

Which bills suit this lookup?

Any biller you pay repeatedly: electricity, piped gas, broadband, society maintenance, a school fee instalment, a vehicle insurance premium. For each, one cell answers "what did I pay last time?" before you pay again, which makes a sudden jump easy to spot. Put the biller names in a column and fill the formula down beside them, and you have a one-glance table of last payments for the whole household.

A small checklist

  • Keep the biller names consistent, ideally chosen from a drop-down list.
  • Store amounts as numbers, not as text with the rupee sign typed in.
  • Add a row for each payment, not one edited row per month, so the history stays.
  • Add the date return in a second cell so you can see how old the amount is.

If you also keep society maintenance, broadband and gas in the log, a statement import into SpendixAI can feed payments in without retyping, with reports you can download as CSV for a sheet like this.

SPENDIXAI JOURNAL

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

Explore your reports