2

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.

cheese10234
  • 111
  • 8

1 Answers1

3

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

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Say I want to use this formula to find a different value while using the same lookup information. which variables would I change? – cheese10234 Apr 21 '23 at 21:41
  • 1
    @cheese10234 I have no idea what you mean. What do you want to change? Remember I cannot read your mind. – Scott Craner Apr 21 '23 at 21:42
  • So let's say, using this formula, instead of returning the value in the E column, I want to return the value that is in the C column. still using (B:B=J2)*(A:A=L2). – cheese10234 Apr 21 '23 at 21:52
  • 2
    @cheese10234 same formula, edit, with `TAKE()` you can do that. `TAKE(SORT(FILTER(C:E,(B:B=J2)*(A:A=L2),"NO MATCH"),2,-1),1,1)` – Mayukh Bhattacharya Apr 21 '23 at 21:56
  • @MayukhBhattacharya okay, perfect. I havent used the TAKE function before, so i am confused a bit on how the `2,=1)1,1)` is selecting the cell its pulling the result from. I'll look it up. Thank you guys for the help!! – cheese10234 Apr 21 '23 at 22:37
  • 1
    @cheese10234 `2` is not selecting a cell instead its indexing the range while using the `SORT()` function and sort z to a as the order is `-1` while `1` & `1` used within **[TAKE()](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003)** it takes the first row and first column. – Mayukh Bhattacharya Apr 21 '23 at 22:41
  • 1
    @cheese10234 also you can make it less verbose by excluding the `TAKE()` and using an `Implicit Intersection Operator` --> `@` --> `=@SORT(FILTER(C:E,(B:B=J2)*(A:A=L2),"NO MATCH"),2,-1)` – Mayukh Bhattacharya Apr 21 '23 at 22:43