Cross reference with multiple instances in reference data

**Nick F** · 05-08-2013, 09:53 AM

I am an excel newbie and need some help from experienced users. This is part of a medical audit (although all data in the attached sheet is completely fictitious).

In sheet 1 there is data with three columns - an unique identifier (ID), and two dates (IN & OUT). In sheet 2 there are two columns - ID and a date (eDate).

I would like in column C of sheet 2 to look at the data in sheet 1 and say for each ID is the eDate is between the IN and OUT dates. ID's may appear more than once in both sheets but for the same ID in sheet 1 the IN & OUT dates may be consecutive but do not overlap.

A sample workbook with the correct column titles but fictitious data is attached. In red I have shown the output I am trying to achieve! I've been trying to use an array but can't get it to work.

Thank you in advance for your help with this.

**benishiryo** · 05-08-2013, 10:00 AM

hi Nick F, welcome to the forum. not sure if you are using Excel 2003 or 2011 as stated in your profile. COUNTIFS only available in 2007 & above:
=COUNTIFS(Sheet1!$A$2:$A$10,A2,Sheet1!$B$2:$B$10,"<="&B2,Sheet1!$C$2:$C$10,">="&B2)

but if you need to use it in 2003 & below, then:
=SUMPRODUCT((Sheet1!$A$2:$A$10=A2)*(Sheet1!$B$2:$B$10<=B2)*(Sheet1!$C$2:$C$10>=B2))

extend range if needed