Array formulas that do not use explicit ranges

Question

For every instance of a customer buying a product I need to figure out when that customer previously bought that product. I've a solution that works using array formulas:

{=MAX(IF(($D$3:$D$15 = [@Product])*($C$3:$C$15=[@Customer]) * ($E$3:$E$15<[@Date]),$E$3:$E$15,FALSE))}

and I like it because it doesn't require that the data be sorted (I wanted to avoid that prerequisite) but I am not totally happy about it because the formula includes cell ranges (e.g. $D$3:$D$15) and hence if and when new transactions are added to the source data I'll need to update the formula.

A worked example is here on OneDrive.

Can anyone figure out a better way of doing this that can do away with those ranges in the array formula?

Answer 1

Try using structured references instead of the range ref

{=MAX(IF((Table1[[#All],[Product]] = [@Product])*(Table1[[#All],[Customer]]=[@Customer]) * (Table1[[#All],[Date]]<[@Date]),Table1[[#All],[Date]],FALSE))}

Answer 2

Adding another condition to the date retrieval would be easier (and non-array) is switched over to the AGGREGATE¹ function where any non-matching value is forced to an error and errors are ignored.

' in F3
=IFERROR(AGGREGATE(14, 6, ([Date])/(([Product]=[@Product])*([Customer]=[@Customer])*([Date]<[@Date])), 1), "")
' in G3
=IF([@PreviousPurchase]="", "", [@Date]-[@PreviousPurchase])

      

The IFERROR function returns a zero-length string (e.g. "") when no previous purchase date can be determined. This requires that the G3 formula be adjusted as as well.

¹ _{The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.}

Sample returned to: http://1drv.ms/1gwJ8wH