Shopping

Lowest, highest and latest price in Google Sheets: three formulas

Three Google Sheets formulas for a price list: MIN for the lowest, MAX for the highest and an INDEX MATCH lookup for the latest price, even out of order.

Editorial illustration for Lowest, highest and latest price in Google Sheets: three formulas

In Google Sheets, the lowest price is =MIN($B$2:$B$100) and the highest is =MAX($B$2:$B$100). The latest price needs a lookup, because the last row you typed is not always the most recent date. Use =INDEX($B$2:$B$100, MATCH(MAX($A$2:$A$100), $A$2:$A$100, 0)), where column A holds real dates and column B holds prices. All three formulas also work in Excel.

What is the setup?

Say column A holds the date you checked a price and column B holds the price in rupees for one pair of headphones. The sheet has a header in row 1 and data from row 2. Use fixed ranges like $A$2:$A$100 so the formulas keep working as you add rows below, and do not use whole columns, which also pick up headings and stray notes.

The entries below are invented, and they are deliberately not in date order, because that is where most mistakes begin.

RowDatePrice
25 Oct₹2,450
312 Oct₹2,399
428 Sep₹2,599
519 Oct₹2,299
62 Nov₹2,349
726 Oct₹2,399

Formula one: how do you find the lowest price?

Type =MIN($B$2:$B$100) in any empty cell. It returns ₹2,299 for this list. MIN ignores empty cells and text, so blank rows below your data do no harm. But it does count a zero, so never type 0 for a missing price. Leave that cell empty.

To find the lowest price at one seller only, add a store column C and use =MINIFS($B$2:$B$100, $C$2:$C$100, "Store A"). MINIFS takes the range to look in first, then the range to test, then the value to match.

Formula two: how do you find the highest price?

Type =MAX($B$2:$B$100). The answer here is ₹2,599, from 28 September. The gap between highest and lowest is ₹2,599 − ₹2,299 = ₹300, which you can calculate with =MAX($B$2:$B$100)-MIN($B$2:$B$100).

Formula three: how do you get the latest price?

First find the latest date with =MAX($A$2:$A$100), which is 2 Nov. Then ask Sheets to return the price from the same row with =INDEX($B$2:$B$100, MATCH(MAX($A$2:$A$100), $A$2:$A$100, 0)). MATCH finds the position of the latest date inside column A, with 0 meaning an exact match, and INDEX returns the price at that position. The answer is ₹2,349.

A common shortcut is =LOOKUP(2, 1/($B$2:$B$100<>""), $B$2:$B$100), which returns the last non-empty price in the column. That gives ₹2,399, the 26 October entry, because it was typed last. It is correct only if you always add rows in date order.

What if two rows share the latest date?

MATCH returns the first one it finds. If you check twice in a day, add a time, or keep one row per day. For a quick fix, sort the sheet by date with Data, Sort range, and use the last row.

Checklist before you trust the numbers

  • Dates are right-aligned, which usually means real dates.
  • Prices are plain numbers with no text, spaces or rupee signs typed in.
  • Ranges cover every row you have, with some room to grow.
  • Missing prices are empty, not 0.
  • The latest-price formula uses dates, not row position.

When you compare the latest price with your target, a spare cell can hold =IF(latest<=target,"Buy","Wait"), with the cell names replaced by real references. When a price sheet turns into dozens of products, a tracker that keeps your actual purchases and receipts, such as SpendixAI, handles what you bought, while the sheet stays for what you are watching.

SPENDIXAI JOURNAL

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

Explore your reports