+ Reply to Thread
Results 1 to 2 of 2

Index Match both ID and next highest date between two spreadsheets

  1. #1
    Registered User
    Join Date
    12-14-2017
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Office 2016
    Posts
    1

    Index Match both ID and next highest date between two spreadsheets

    I could use some help, I use VLOOKUP all the time to take two reports from our system to find matches. But, this problem is a little different. I have the following spreadsheets:

    Spreadsheet 1: each client ID will have one one row associated with it - this is their date of discharge from another facility. Columns in bold.

    CLIENT ID


    DISCHARGE DATE


    Spreadsheet 2 - This spreadsheet could have thousands of rows with multiple dates of services occuring
    CLIENT ID (will match spreadsheet 1)


    DATE OF SERVICE

    These are the only columns that really matter. I want to find the first service the client had after their date of discharge from the other facility. I don't care about the dates prior. And I don't care about subsequent dates. They could show up many times but I want the one just after the discharge date. I am fine with N/As, this is quick and dirty to feed something else. I couldn't figure out to make VLOOKUP work for this.

    Can anyone help? I tried INDEX MATCH and it was a failure. We're so busy as a healthcare facility I can't afford to put much more time to this. Any help would be appreciated!
    Last edited by jjoslin; 04-20-2020 at 06:08 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    365
    Posts
    414

    Re: Index Match both ID and next highest date between two spreadsheets

    Assuming:
    Client IDs in column A starting in row 2 in both worksheets
    Discharge dates in Sheet1 column B and
    Service dates in a single row for each client, starting at Sheet2 column B,

    Try this formula in Sheet1 row 2 any column:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    You may have to array enter (Ctrl+Shift+Enter) in your Excel version. Then copy down.

    The OFFSET function is set to select a range with 100 columns (you can adjust it accordingly) starting at column B and one row, the one that matches Client ID in sheet2.

    Please run some test and let us know how it goes.

    Good luck!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] INDEX and MATCH for the highest value in the list
    By Cuni in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-20-2020, 11:44 AM
  2. [SOLVED] highest score of student using index match max if
    By johnsnider1 in forum Excel General
    Replies: 7
    Last Post: 08-24-2018, 01:43 PM
  3. Index Match formula - need to match the highest value
    By becskull in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-12-2017, 08:10 AM
  4. [SOLVED] Index and Match - Max formula lookup the highest value
    By ghostexcel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2015, 01:10 PM
  5. summing an index(match,match) over multiple spreadsheets
    By andyjoewalnutt in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-31-2013, 03:15 PM
  6. [SOLVED] INDEX-MATCH to find next highest value (unsorted data)
    By Lady222 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-26-2012, 10:15 AM
  7. Index, Match for highest value
    By cedarhill in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-05-2009, 09:36 PM

Tags for this Thread

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