Lookup latest price

To lookup the latest price for a product in a list, sorted so latest items appear last, you can use a formula based on the LOOKUP function. In the example show, the formula in G7 is: =LOOKUP(2,1/(item=F7),price) where item is the named range B5:B12, price is the named range D5:D12, and data is sorted ascending by date.

April 14, 2025 · 1 min · 73 words · David Cobb

Table of Contents

Explanation

The LOOKUP function assumes data is sorted, and always does an approximate match.

Notice the array contains only two unique values: the divide by zero error (#DIV/0!)

and the number 1.

Excel formula: Get date associated with last entry

LOOKUP searches the array for the value 2, ignoring the error values.

Not finding 2, it falls back to the last 1, at position 7 in the lookup vector.

LOOKUP’s default behavior makes it useful for solving certain problems in Excel.

Excel formula: Get value of last non-empty cell

Excel formula: Get information about max value

Excel formula: XLOOKUP latest by date

Excel LOOKUP function