I'm struggling with XLOOKUP and dates with multiple criteria.
I'm trying to return a Lab value for a particular ID collected on a date that is closest to, but before, or equal to, the Clinic Date.
For example, to return the closest lab value for ID: A251527 on Clinic Date 17/09/2008 (in cells E2 and F2 respectively).
The answer should be 130 on 12/08/2008 but XLOOKUP using multiple criteria seems to return the Lab value on the first date for this ID (124).
=XLOOKUP(1, ($A$2:$A$17=E2)*(LARGE($B$2:$B$17,COUNTIF($B$2:$B$17,">"&$F$2)+1)<=F2), $C$2:$C$17)
The data is unsorted and is large, ~110,000 rows.
Thanks.
Bookmarks