Formula in Excel to display the date for the last occurrence of a value in a table
Hello,
This is a follow-up question to the above thread. The formula given (by
Flyster) for the solution was:
=LOOKUP(2,1/(B1:B12=B15),A
1:A12)
My question in this thread relates to the structure of the 2nd term:
1/(B1:B12=B15)
In this example, the denominator (presumably a single term) consists of an equation — in this case an equality.
How can an equation (of any form) operate as a term (if that's what is happening here)?
Thanks
Some Excel functions expect arrays as an input. The array may comprise a range of cells or the results of a Boolean expression. LOOKUP, AGGREGATE (for first parameters 14 through 19) and SUMPRODUCT are functions that work this way.
Other Excel functions expect a single value as an input, but can tolerate an array if you array-enter the formula (Control + Shift + Enter). IF is a good example of such a function. In such cases, the function will return an array of results, one for each value in the input array. In most cases, you will pass the results of these functions into another function that is expecting an array as an input.
Still other Excel functions can handle a range of cells as an input, but need to be array-entered if passed the results of a Boolean expression. SUM is a good example of such a function.
In the particular case of Flyster's clever formula, the Boolean expression tests whether cells B1:B12 equal the value of B15. If so, the denominator returns TRUE, which is coerced into a value of 1 when used in an arithmetic expression. Should a cell in the range B1:B12 not equal B15, the denominator returns FALSE, which is coerced into a value of 0. Since 1/0 is undefined, the expression returns a DIV/0! error value at that position. Putting it all together, the expression 1/(B1:B12=B15) will return an array of 1 and DIV/0! error values.
LOOKUP has the property of considering only those values in the second parameter that are the same data type as the first parameter. In Flyster's formula, that means that the DIV/0! error values are ignored.
LOOKUP is expecting the second parameter to be sorted in ascending order. It will then find a match for the highest value less than or equal to the first parameter. In your case, the second parameter is not sorted. Furthermore, Excel will never find a match for 2 in an array of 1 and DIV/0! error values. As a result, the function returns a match for the last value of the same data type as the first parameter. Though somewhat arcane, this allows the LOOKUP formula to return a match for the last instance of B1:B12 equaling B15.