I have this problem at work to populate the worksheet with the right case number.
Sheet 1: (Report)
SSN | Service Date
123456 | 10/01/2014
Sheet 2: (Data)
SSN | Case Number | Start Date | End Date
123456 | 0000000 | 01/01/2010 | 12/31/2012
123456 | 1111111 | 01/01/2013 | 05/31/2014
123456 | 2222222 | 06/01/2014 | 11/10/2015
How can I do a VLOOKUP based on the Service Date to be within the "range" of the Start and End Date of another sheet?
In this case I would like to lookup the SSN and return case number 2222222 because that is the case active for such date of service.
I was looking online and found "MATCH". I am able to match the first result of the case matches the SSN, but how to go to the next case if it does not match?
=IF(E2>=INDEX('CASE NUMBERS'!A:F,MATCH(C2,'CASE NUMBERS'!A:A,0),4)&E2<=INDEX('CASE NUMBERS'!A:F,MATCH(C2,'CASE NUMBERS'!A:A,0),5),"YES","NO")
I am using Excel 2013 on Windows 7 at work.
If SSN is in A1 of both sheets and your Case Numbers are numeric (other than 0000000) then you might try:
=SUMIFS(Sheet2!B:B,Sheet2!A:A,A2,Sheet2!C:C,"<="&B2,Sheet2!D:D,">="&B2)
SUMIFS is explained here (and elsewhere!).