where B5:B13 and E5:E7 contain valid dates, and C5:C13 contains amounts. Working from the inside out, the expression: generates strings like “0117” using the values in column B and E, which are then compared to each other. The result is an array like this: where TRUE represents dates in the same month and year. The number 1 is then divided by this array. The result is an array of either 1’s or divide by zero errors (#DIV/0!): which goes into LOOKUP as the lookup array. LOOKUP assumes data is sorted in ascending order and always does an approximate match. When the lookup value of 2 can’t be found, LOOKUP will match the previous value, so lookup will match the last 1 in the array. Finally, LOOKUP returns the corresponding value in result_vector, which contains the amounts in C5:C13.
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.
