where item is the named range B5:B12, price is the named range D5:D11, and data is sorted ascending by date.
How this formula works
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 or TRUE and FALSE values like this:
This array is provided as the divisor to 1, which results in another array like this:
This array is provided as the search vector. Notice the array contains only two values: the divide by zero error (#DIV/0!) and the number 1.
LOOKUP searches the array for a value of 2. Not finding 2, it falls back to the last 1, at position 7 in the lookup array.
Finally, LOOKUP returns the 7th item in the results array, the price $15.
To read more about the concept of intentionally looking for a value that won't ever appear, read about BigNum.
To retrieve a date associated with a last entry tabular data, you can use a a formula based on the LOOKUP function. In the example shown the formula in H5 is: = LOOKUP ( 2 , 1 / ( C5:G5 <> "" ), C$4:G$4 ) How this formula works...
To find the value of the last non-empty cell in a row or column, you can use the LOOKUP function in this surprisingly compact formula. As an additional bonus, this formula is not an array formula, and not volatile. How this formula works The key to...
To lookup information related to the maximum value in a range, you can use a formula that combines the MAX, INDEX, and MATCH functions. In the example shown, the formula in I8 is: = INDEX ( F3:F11 , MATCH ( MAX ( C3:C11 ), C3:C11 , 0 )) which...
The Excel LOOKUP function performs an approximate match lookup in a one-column or one-row range, and returns the corresponding value from another one-column or one-row range. LOOKUP's default behavior makes it useful for solving certain problems...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.