The number 1 is divided by this array, which creates a new array composed of either 1’s or #DIV/0! errors: This array is used as the the lookup_vector. The lookup_value is 2, but the largest value in the lookup_array is 1, so lookup will match the last 1 in the array. Finally, LOOKUP returns the corresponding value in result_vector, from the dates in the range C$4:G$4. Note: the result in column H is a date from row 5, formatted with the custom format “mmm” to show an abbreviated month name only.
Zeros instead of blanks
You might have a table with zeros instead of blank cells:
In that case, you can adjust the formula to match on values greater than zero like so:
Multiple criteria
You can extend criteria by adding expressions to the denominator with boolean logic. For example, to match the last value greater than 400, you can use a formula like this:
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.
