Lookup value against date range to return another value

[davidphi]

Hi,

I have a similar query to this question:

http://www.mrexcel.com/forum/excel-...-lookup-date-within-multiple-date-ranges.html

But, I think it's not quite the same in my case.

I have a table of data with Value's and Date's eg:

A	B
Value	Date
Apple	02/07/2008
Orange	04/08/2010
Apple	08/01/2015

<tbody>
</tbody>

I have a lookup table of Values, Dates and ID's:

A	B	C	D
Value	Date Start	Date End	ID
Apple	01/01/2008	01/01/2015	1
Apple	02/01/2015	16/01/2017	2
Orange	01/01/2008	16/01/2017	3

<tbody>
</tbody>

I want to lookup the ID of the value within the date range as specified by the date. I've tried

=LOOKUP(2,1/((B2>=LookupTable!B2:B4)*(B2<=LookupTable!C2:C4)),LookupTable!D2:D4)

But, instead of getting the ID 1 for Apple, I get the ID 3 for Orange. I believe this is because it is the first date range, from the bottom up, that matches the date range of date in B2.

What I want to calculate is the following:

A	B	C
Value	Date	ID
Apple	02/07/2008	1
Orange	04/08/2010	3
Apple	08/01/2015	2

<tbody>
</tbody>

How can I get it to take in to account the value as well as the date range?

Thanks!

David

 

[63falcondude]

Here's one option. This assumes that you will never have any duplicate Values in the same date range.

Excel 2010
	A	B	C	D	E	F	G	H
1	Value	Date	ID		Value	Date Start	Date End	ID
2	Apple	7/2/2008	1		Apple	1/1/2008	1/1/2015	1
3	Orange	8/4/2010	3		Apple	1/2/2015	1/16/2017	2
4	Apple	1/8/2015	2		Orange	1/1/2008	1/16/2017	3
Sheet1

Cell Formulas
Range	Formula
C2	=SUMIFS(H:H,E:E,A2,F:F,"<="&B2,G:G,">="&B2)

 

[Aladin Akyurek]

control+shift+enter, not just enter:

=INDEX(LookupTable!$D$2:$D$6,MATCH(1,IF(LookupTable!$A$2:$A$6=A2,IF(B2>=LookupTable!$B$2:$B$6,
IF(B2<=LookupTable!$C$2:$C$6,1))),0))

 

[davidphi]

Awesome - thanks very much. The index solution worked well. I did try this initially, but couldn't work out how to get the correct set of matches.

Thanks for your help!

David

 

[Aladin Akyurek]

Awesome - thanks very much. The index solution worked well. I did try this initially, but couldn't work out how to get the correct set of matches.

Thanks for your help!

David

You are welcome.