Lookup value against date range to return another value

davidphi

New Member
Joined
Jan 16, 2017
Messages
5
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:

AB
ValueDate
Apple02/07/2008
Orange04/08/2010
Apple08/01/2015

<tbody>
</tbody>

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

ABCD
ValueDate StartDate EndID
Apple01/01/200801/01/20151
Apple02/01/201516/01/20172
Orange01/01/200816/01/20173

<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:

ABC
ValueDateID
Apple02/07/20081
Orange04/08/20103
Apple08/01/20152

<tbody>
</tbody>

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

Thanks!

David
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
Here's one option. This assumes that you will never have any duplicate Values in the same date range.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Value</td><td style="text-align: center;;">Date</td><td style="text-align: center;;">ID</td><td style="text-align: right;;"></td><td style="text-align: center;;">Value</td><td style="text-align: center;;">Date Start</td><td style="text-align: center;;">Date End</td><td style="text-align: center;;">ID</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Apple</td><td style="text-align: center;;">7/2/2008</td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="text-align: center;;">Apple</td><td style="text-align: center;;">1/1/2008</td><td style="text-align: center;;">1/1/2015</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Orange</td><td style="text-align: center;;">8/4/2010</td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style="text-align: center;;">Apple</td><td style="text-align: center;;">1/2/2015</td><td style="text-align: center;;">1/16/2017</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">Apple</td><td style="text-align: center;;">1/8/2015</td><td style="text-align: center;;">2</td><td style="text-align: right;;"></td><td style="text-align: center;;">Orange</td><td style="text-align: center;;">1/1/2008</td><td style="text-align: center;;">1/16/2017</td><td style="text-align: center;;">3</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=SUMIFS(<font color="Blue">H:H,E:E,A2,F:F,"<="&B2,G:G,">="&B2</font>)</td></tr></tbody></table></td></tr></table><br />
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Jan 16, 2017
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top