INDEX/MATCH Within Date Range

[DaVinciDecoder]

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.

 

[amdopt]

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.

=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.

 

[DaVinciDecoder]

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.

 

[amdopt]

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.

 

[DaVinciDecoder]

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.

 

[AhoyNC]

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
	A	B	C
1	PATIENT NO.	SERVICE DATE	PATIENT STATUS
2	1	08/01/2015	NEW
3	2	09/25/2015	NEW
4	3	04/03/2016	NEW
5	1	05/15/2016	RETURN
6	3	05/20/2016	DECEASED
Sheet 1

Excel Workbook
	A	B	C	D
1	PATIENT NO.	SERVICE BEGIN DATE	SERVICE END DATE	PATIENT STATUS
2	1	08/01/2015	08/30/2015	NEW
3	1	05/01/2016	05/31/2016	RETURN
4	2	09/01/2015	09/30/2015	NEW
5	3	04/01/2016	04/30/2016	NEW
6	3	05/01/2016	05/31/2016	DECEASED
Sheet 2

 

[amdopt]

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.