Results 1 to 9 of 9

XLOOKUP and dates with multiple criteria.

Threaded View

  1. #1
    Registered User
    Join Date
    02-13-2022
    Location
    Cardiff, Wales
    MS-Off Ver
    Microsoft 365 for Mac
    Posts
    3

    XLOOKUP and dates with multiple criteria.

    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.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Xlookup with multiple criteria
    By Mackay2m in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 01-12-2022, 04:09 PM
  2. Xlookup return maximum value for multiple criteria
    By Excelforum2020 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-09-2021, 10:18 AM
  3. Replies: 5
    Last Post: 08-17-2021, 03:06 PM
  4. Help on XLOOKUP formula with multiple criteria
    By lyzas in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-22-2021, 05:06 AM
  5. Multiple criteria for XLOOKUP formula
    By mikehk in forum Excel General
    Replies: 1
    Last Post: 06-28-2021, 05:50 PM
  6. Replies: 3
    Last Post: 06-24-2021, 10:31 AM
  7. [SOLVED] XLOOKUP to return cell based on multiple criteria in single column
    By mikehay08 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-24-2021, 02:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1