Search code examples
excelexcel-formulaxlookup

Using XLookup to give latest record by date, but if exact match isnt found it is giving false info

I am using a formula that I learned from this article: https://exceljet.net/formulas/xlookup-latest-by-date?fbclid=IwAR3rDjbzoAUQ5vrqo7QRXJt1VxvVjBuZ7kxHE7_YA-lfl53gx1OMl7MI2GM

Basically, I need to find the most current record of for a custmer based on the highest value in the date column.

=XLOOKUP(MAX(D:D),(B:B=J2)*(A:A=L2)*D:D,E:E,,-1)

It works perfectly if the info is there, but for customers WITHOUT information for one of the campaigns, instead of giving a blank cell or na, it is giving a totally random value.

I tried setting the match type to 0 instead of -1, but this breaks the formula.

Solution

  • That is because (B:B=J2)*(A:A=L2)*D:D is returning all 0 and therefor finding the closest match is haphazard.

    Instead use FILTER, sort it and take the top result:

    =TAKE(SORT(FILTER(D:E,(B:B=J2)*(A:A=L2),"NO MATCH"),1,-1),1,-1)

    enter image description here