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:
<tbody>
</tbody>
I have a lookup table of Values, Dates and ID's:
<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:
<tbody>
</tbody>
How can I get it to take in to account the value as well as the date range?
Thanks!
David
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