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:


where item is the named range B5:B12, price is the named range D5:D12, and data is sorted ascending by date.

Generic formula



The LOOKUP function assumes data is sorted, and always does an approximate match. If the lookup value is greater than all values in the lookup array, default behavior is to "fall back" to the previous value. This formula exploits this behavior by creating an array that contains only 1s and errors, then deliberately looking for the value 2, which will never be found.

First, this expression is evaluated:


When F7 contains "sandals" the result is an array of TRUE and FALSE values like this:


This array is provided as the divisor to 1:


The math operation automatically coerces the TRUE and FALSE values to 1s and 0s, so the result is another array like this:


returned directly to the LOOKUP function as the lookup vector argument.

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

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 then returns the 7th item in the result vector (the named range "price"), the value 15.

To read more about the concept of intentionally looking for a value that won't ever appear, read about BigNum.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.