Multiple sheet and multiple value INDEX MATCH that includes a date range return

[TBahr]

I've been looking through the forum for an answer, and although I've seen some very similar posts, I can't get a definitive answer for my issue: Here is an example of what I am working with and what I'm trying for my INDEX MATCH


=INDEX(Sheet1!A1:A1312,MATCH(Sheet2!$A$1:$A$9999&$B$1:$B$9999,Sheet1!$A$1:$A$9999&(Sheet2!$B$1>=Sheet1!$B$1)*(Sheet2!$A$1<=Sheet1!$C$1),0))

What I am trying to create is are two very similar INDEX MATCHES in Sheet 1: Column C would show the Beginning Date (Sheet 2, column B) and Column D the Ending Date (Sheet 2 Column C) that the incident date occurs in if the SIZE CODE (BIG-01, etc) is the same. My searching suggests that the INDEX/MATCH is the simplest means to accomplish this, but if VBA or some other method is better suited, I would appreciate any input as I am relatively new to this sort of Excel power-usage.

Thanks for considering!


Sheet 1

A B

BIG-01 7/5/2012
BIG-01 1/16/2010
BIG-01 4/18/2011
BIG-01 9/11/2013
BIG-01 6/6/2009
BIG-01 8/11/2012
BIG-01 10/10/2012
BIG-01 12/15/2014
LITTLE-01 7/5/2012
LITTLE-01 1/16/2010
LITTLE-01 4/18/2011
LITTLE-01 9/11/2013
LITTLE-01 6/6/2009
LITTLE-01 8/11/2012
LITTLE-01 10/10/2012
LITTLE-01 12/15/2014
LITTLE-01 3/15/2008
MEDIUM-01 4/25/2015
MEDIUM-01 7/5/2012
MEDIUM-01 1/16/2010
MEDIUM-01 4/18/2011
MEDIUM-01 9/11/2013
MEDIUM-01 6/6/2009
MEDIUM-01 8/11/2012
MEDIUM-01 10/10/2012
MEDIUM-01 12/15/2014

Sheet 1
A B C
BIG-01 11/1/2012 11/1/2013
BIG-01 11/1/2013 11/1/2014
BIG-01 11/1/2014 11/1/2015
BIG-01 11/1/2015 11/1/2016
BIG-01 11/1/2011 11/1/2012
BIG-01 11/1/2010 11/1/2011
BIG-01 11/1/2009 11/1/2010
BIG-01 11/1/2008 11/1/2009
BIG-01 11/1/2007 11/1/2008
LITTLE-01 11/1/2012 11/1/2013
LITTLE-01 11/1/2013 11/1/2014
LITTLE-01 11/1/2014 11/1/2015
LITTLE-01 11/1/2015 11/1/2016
LITTLE-01 11/1/2011 11/1/2012
LITTLE-01 11/1/2010 11/1/2011
LITTLE-01 11/1/2009 11/1/2010
LITTLE-01 11/1/2008 11/1/2009
LITTLE-01 11/1/2007 11/1/2008
MEDIUM-01 11/1/2012 11/1/2013
MEDIUM-01 11/1/2013 11/1/2014
MEDIUM-01 11/1/2014 11/1/2015
MEDIUM-01 11/1/2015 11/1/2016
MEDIUM-01 11/1/2011 11/1/2012
MEDIUM-01 11/1/2010 11/1/2011
MEDIUM-01 11/1/2009 11/1/2010
MEDIUM-01 11/1/2008 11/1/2009
MEDIUM-01 11/1/2007 11/1/2008

 

[TBahr]

I'm still researching, and haven't yet found a solution. If anyone has insight, I would be eternally grateful.

 

[Mike Szczesny]

Hi TBahr,

Can you use something like this? If I understand you correctly, do you want to link the incident to year interval?
Mike Szczesny

Excel 2012
	A	B	C	D	E	F	G
1	Company	Incident Date	Year		Year	Beg. Date	End. Date
2	A	07/05/12	a5		a1	11/01/12	11/01/13
3	A	01/16/10	a7		a2	11/01/13	11/01/14
4	A	04/18/11	a6		a3	11/01/14	11/01/15
5	A	09/01/13	a1		a4	11/01/15	11/01/16
6	A	06/06/09	a8		a5	11/01/11	11/01/12
7	A	08/11/12	a5		a6	11/01/10	11/01/11
8	A	10/10/12	a5		a7	11/01/09	11/01/10
9	A	12/15/14	a3		a8	11/01/08	11/01/09
10					a9	11/01/07	11/01/08
11
Sheet1

Cell Formulas
Range	Formula
C2	{=LOOKUP(2,1/((B2>=$F$2:$F$10)*(B2<=$G$2:$G$10)),$E$2:$E$10)}
C3	{=LOOKUP(2,1/((B3>=$F$2:$F$10)*(B3<=$G$2:$G$10)),$E$2:$E$10)}
C4	{=LOOKUP(2,1/((B4>=$F$2:$F$10)*(B4<=$G$2:$G$10)),$E$2:$E$10)}
C5	{=LOOKUP(2,1/((B5>=$F$2:$F$10)*(B5<=$G$2:$G$10)),$E$2:$E$10)}
C6	{=LOOKUP(2,1/((B6>=$F$2:$F$10)*(B6<=$G$2:$G$10)),$E$2:$E$10)}
C7	{=LOOKUP(2,1/((B7>=$F$2:$F$10)*(B7<=$G$2:$G$10)),$E$2:$E$10)}
C8	{=LOOKUP(2,1/((B8>=$F$2:$F$10)*(B8<=$G$2:$G$10)),$E$2:$E$10)}
C9	{=LOOKUP(2,1/((B9>=$F$2:$F$10)*(B9<=$G$2:$G$10)),$E$2:$E$10)}
Press CTRL+SHIFT+ENTER to enter array formulas.