INDEX/MATCH Within Date Range

DaVinciDecoder

New Member
Joined
Feb 22, 2017
Messages
6
SHEET 1

PATIENT NO. | SERVICE DATE | PATIENT STATUS
1 | 08/01/2015 | ?
2 | 09/25/2015 | ?
3 | 04/03/2016 | ?
1 | 05/15/2016 | ?
3 | 05/20/2016 | ?



SHEET 2
PATIENT NO. | SERVICE BEGIN DATE | SERVICE END DATE | PATIENT STATUS
1 | 08/01/2015 | 08/30/2015 | NEW
1 | 05/01/2016 | 05/31/2016 | RETURN
2 | 09/01/2015 | 09/30/2015 | NEW
3 | 04/01/2016 | 04/30/2016 | NEW
3 | 05/01/2016 | 05/31/2016 | DECEASED

Hi,

I've simplified the two spreadsheets, but effectively, I would like a function that looks up the Patient Number from Sheet 1 in Sheet 2, contains a criteria to check if the Service Date in Sheet 1 falls between the Service Date range in Sheet 2, then returns the Patient Status from Sheet 2.

I'm hoping a Index/Match or Lookup function would be best suited for what I'm trying to accomplish.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
For your Sheet1, Patient Status formula, an index/match coupled with an offset and countif will find the bottom as opposed to index/match or vlookup alone which will default to the top most value. Just change your sheet numbers and look up ranges, look up values, etc in the index and match functions.

Code:
=OFFSET(INDEX(Sheet2!E$25:H$29,MATCH(A20,Sheet2!E$25:E$29,0),4),COUNTIF(Sheet2!E$25:E$29,Sheet1!A20)-1,0)

I would recommend a separate formula on sheet2 that determines new, return or deceased.
 
Upvote 0
Hi, thank you for taking the time to respond. Unfortunately, the formula you suggested does not contain a criteria to determine if the patient's service date (from Sheet 1) falls within the appropriate date range (in sheet 2) and therefore, served as a basic vLookup -- the first instance of the Patient Status was retrieved. The goal with the formula is to retrieve the Patient's status at a given point in time. For instance, Patient 1 was a new patient on 08/01/2015 but then was a return patient on 05/15/2016.
 
Upvote 0
I would need more information to be able to create a formula to help you achieve your goal. The only status that is determinable given the information you provided is "New" i.e. the patient's first appearance in sheet 2. "Return" and "Deceased" both fall under the same criteria with the information you provided i.e. between 2 dates after having appeared previously. How does "Return" or "Deceased" get into that column on sheet 2?

Also, if it is returning the top most value, you have adapted the formula incorrectly to your sheet.
 
Upvote 0
The Patient Status is static data -- there is no formula driving that result in Sheet 2. My goal is to input a formula in Sheet 1 that will match the MRN in Sheet 2, determine if the Service Date in Sheet 1 falls within the range in Sheet 2, and return the corresponding patient Status. For example, Patient 1 had a visit on 08/01/2015, so using a formula, I'd like it to return the Patient Status from Sheet 2, which would be "New". For Patient 1 again, on their 2nd visit on 05/15/2016, the formula should generate a result of "Return" from Sheet 2.
 
Upvote 0
See if this works for you.
This is an array formula that must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed,

Excel Workbook
ABC
1PATIENT NO.SERVICE DATEPATIENT STATUS
2108/01/2015NEW
3209/25/2015NEW
4304/03/2016NEW
5105/15/2016RETURN
6305/20/2016DECEASED
Sheet 1
Excel Workbook
ABCD
1PATIENT NO.SERVICE BEGIN DATESERVICE END DATEPATIENT STATUS
2108/01/201508/30/2015NEW
3105/01/201605/31/2016RETURN
4209/01/201509/30/2015NEW
5304/01/201604/30/2016NEW
6305/01/201605/31/2016DECEASED
Sheet 2
 
Upvote 0
On my "Sheet2" I included 2 new entries and then sorted by column 1 from smallest to largest:

[TABLE="width: 318"]
<tbody>[TR]
[TD]Patient No[/TD]
[TD]Begin Date[/TD]
[TD]End Date[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8/1/2015[/TD]
[TD="align: right"]8/30/2015[/TD]
[TD]new[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5/1/2015[/TD]
[TD="align: right"]5/31/2016[/TD]
[TD]return[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9/1/2015[/TD]
[TD="align: right"]9/30/2015[/TD]
[TD]new[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]10/1/2016[/TD]
[TD="align: right"]10/31/2016[/TD]
[TD]return[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4/1/2016[/TD]
[TD="align: right"]4/30/2016[/TD]
[TD]new[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]5/1/2016[/TD]
[TD="align: right"]5/31/2016[/TD]
[TD]deceased[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]11/1/2016[/TD]
[TD="align: right"]11/30/2016[/TD]
[TD]new[/TD]
[/TR]
</tbody>[/TABLE]

Now on my "Sheet1" this is what populates by using the formula I posted earlier.

[TABLE="width: 225"]
<tbody>[TR]
[TD]Patient No.[/TD]
[TD]Service Date[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8/1/2015[/TD]
[TD]return[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9/25/2015[/TD]
[TD]return[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4/3/2016[/TD]
[TD]deceased[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5/15/2016[/TD]
[TD]return[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]5/20/2016[/TD]
[TD]deceased[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]10/20/2016[/TD]
[TD]return[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]11/20/2016[/TD]
[TD]new[/TD]
[/TR]
</tbody>[/TABLE]


Just to clarify and to make sure I understand what you want the output to be: You want to input a Patient No and Service Date and receive a status from the formula, correct?

If that is not the case please help me understand. If that is the case then my formula gives you the output by looking up the bottom most status of a patient AFTER it has been input on sheet2 and AFTER sorting sheet2 smallest to largest. The service date is not needed as long as the table is sorted.

Let me know how it goes or if I am totally off track with what you are trying to do.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top