XLOOKUP and dates with multiple criteria.

**RD3** · 02-13-2022, 05:21 PM

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.

**Fluff13** · 02-13-2022, 05:44 PM

Hi & welcome to the board.
How about

Formula:

Please Login or Register  to view this content.

**RD3** · 02-13-2022, 06:59 PM

Amazing. Thanks very much.
Was working out what the formula did. Shouldn't the Sort Index be 2 even though your formula works?
ie. =INDEX(SORT(FILTER(B2:C17,(A2:A17=E2)*(B2:B17<=F2)),2,-1),1,2)
Only because I will have to use this formula on a much larger dataset with multiple columns.

**TMS** · 02-13-2022, 08:14 PM

This is the difference:

ID Clinic date
A251527 17/09/2008

=FILTER(B2:C17,(A2:A17=E2)*(B2:B17<=F2))
13/06/2008 124
16/06/2008 115
18/06/2008 114
23/06/2008 113
24/06/2008 108
24/06/2008 113
12/08/2008 130

=SORT(FILTER(B2:C17,(A2:A17=E2)*(B2:B17<=F2)),1,-1)
12/08/2008 130
24/06/2008 108
24/06/2008 113
23/06/2008 113
18/06/2008 114
16/06/2008 115
13/06/2008 124

=INDEX(SORT(FILTER(B2:C17,(A2:A17=E2)*(B2:B17<=F2)),1,-1),1,2)
130

=FILTER(B2:C17,(A2:A17=E2)*(B2:B17<=F2))
13/06/2008 124
16/06/2008 115
18/06/2008 114
23/06/2008 113
24/06/2008 108
24/06/2008 113
12/08/2008 130

=SORT(FILTER(B2:C17,(A2:A17=E2)*(B2:B17<=F2)),2,-1)
12/08/2008 130
13/06/2008 124
16/06/2008 115
18/06/2008 114
23/06/2008 113
24/06/2008 113
24/06/2008 108

=INDEX(SORT(FILTER(B2:C17,(A2:A17=E2)*(B2:B17<=F2)),2,-1),1,2)
130

**TMS** · 02-13-2022, 08:27 PM

I think, if you play around with the dates, you'll get different results. The formula you have suggested will still return 130 because it is the largest number in the second column of the filtered array, regardless of the dates.

For example, on row 10, change 18/08/2008 to 18/06/2008 and see what happens.

**wk9128** · 02-13-2022, 09:53 PM

Pls change xlookup to index+match

Cell H2 formula

Formula:

Please Login or Register  to view this content.

OR array formula

Formula:

Please Login or Register  to view this content.

**RD3** · 02-14-2022, 08:31 AM

Thanks all for your help. I get it now.
I think I'm correct in saying that: INDEX/SORT/FILTER works regardless of date order. INDEX/MATCH only works if in date order. That's important to know.

**TMS** · 02-14-2022, 08:34 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

**Fluff13** · 02-14-2022, 09:20 AM

Shouldn't the Sort Index be 2 even though your formula works?

Nope, it's sorting the dates from high to low, so that the most recent date is first & then the index function returns the first row from the sorted list.

Glad to help & thanks for the feedback.

XLOOKUP and dates with multiple criteria.

LinkBack

Thread Tools

Rate This Thread

Display

XLOOKUP and dates with multiple criteria.

Re: XLOOKUP and dates with multiple criteria.

Re: XLOOKUP and dates with multiple criteria.

Re: XLOOKUP and dates with multiple criteria.

Re: XLOOKUP and dates with multiple criteria.

Re: XLOOKUP and dates with multiple criteria.

Re: XLOOKUP and dates with multiple criteria.

Re: XLOOKUP and dates with multiple criteria.

Re: XLOOKUP and dates with multiple criteria.

Thread Information

Users Browsing this Thread

Similar Threads

Xlookup with multiple criteria

Xlookup return maximum value for multiple criteria

FILTER or XLOOKUP? One criteria in multiple locations; possible multiple results listed

Help on XLOOKUP formula with multiple criteria

Multiple criteria for XLOOKUP formula

[SOLVED] xLookup with Multiple Criteria - Finding if a date/time falls within two dates and times

[SOLVED] XLOOKUP to return cell based on multiple criteria in single column

Bookmarks

Bookmarks

Posting Permissions