Lookup formula, multiple criteria, date range

[bschulze]

I need a formula that will lookup a value based on two criteria, one just a cell reference, the other if the period falls within a given range. I was thinking this could be done using a Index>Match formula, but the date range is throwing me off. I'm hoping this can be accomplished without an array formula because the actual data set is lengthy. Any help in pointing me in the right direction would be greatly appreciated.

Excel 2012
	A	B	C	D	E	F	G	H	I
1	Data					Reference Table
2	Period	Ref1	Value Lookup			BegPeriod	EndPeriod	Ref1	Value
3	201503	1	200			201001	201206	1	100
4	201504	2	400			201207	201506	1	200
5	201306	1	200			201001	201412	2	300
6	201409	2	300			201501	201506	2	400
7	201301	2	300			201001	201506	3	500
8	201107	1	100
9	201506	1	200
10	201309	3	500
11	201207	1	200
12	201210	3	500
13	201503	2	400
14	201412	2	300
Sheet1

 

[konew1]

It looks like your dates do not overlap for each "Ref1", and that the required result is always numeric.
If this is correct you can use sumproduct() or (if you have excel 2007+) use sumifs()

 

[bschulze]

The returned value will always be a number. I'm familiar with both sumproduct and sumif but not utilizing them with a date range, can you provide a quick example of both/either?

 

[konew1]

See below. Note, I inserted extra columns into your sheet

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Data							Reference Table
2	Period	Ref1	Value Lookup	sumproduct	sumifs			BegPeriod	EndPeriod	Ref1	Value
3	201503	1	200	200	200			201001	201206	1	100
4	201504	2	400	400	400			201207	201506	1	200
5	201306	1	200	200	200			201001	201412	2	300
6	201409	2	300	300	300			201501	201506	2	400
7	201301	2	300	300	300			201001	201506	3	500
8	201107	1	100	100	100
9	201506	1	200	200	200
10	201309	3	500	500	500
11	201207	1	200	200	200
12	201210	3	500	500	500
13	201503	2	400	400	400
14	201412	2	300	300	300
Sheet2

 

[bschulze]

Thanks konew1, very slick. Not sure why I wasn't thinking about >= and <= but it makes perfect sense. Appreciate your knowledge.

 

[bschulze]

konew1, I found an instance where I need to lookup a text value, any suggestions on a formula for that? Same criteria above, just referencing a text value.

 

[bschulze]

Got it. I used the Sumproduct to return a row number and the Index to reference the column needed to pull the text value. Thanks again for your help.

=INDEX($L$3:$L$7,SUMPRODUCT(--($H$3:$H$7<=A3),--($I$3:$I$7>=A3),--($J$3:$J$7=B3),ROW($K$3:$K$7)-2),0)

 

[konew1]

Clever. My very first response asked if all of the required results were numeric. If you told me some were text I would asked others to respond thinking sumproduct was irrelevant to you.