help with lookup value between dates range

stzruya

New Member
Joined
Mar 8, 2018
Messages
8
Hi,
I need to lookup value in table and return value based on date range.
my table look like that:

NumberDate1Date2 Model
10902/12/200329/07/2005 A
10929/07/200523/11/2009 B
10923/11/200911/11/2010 C
10911/11/201019/12/2010 D
10919/12/201025/04/2013 E
10925/04/201324/03/2015 F
10924/03/201528/12/2050 G
11002/12/200329/07/2005 A
11029/07/200523/12/2009 B
11023/12/200902/11/2010 C
11002/11/201019/12/2010 D
11019/12/201021/03/2013 E
11021/03/201324/03/2015 F
11024/03/201520/05/2015 G
11020/05/201526/05/2015 H
11026/05/201528/12/2050 J

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
In other sheet i have list of numbers such as the numbers above (in column A) with specific date, based on that i need to return the model that was between that time.
for example for number 110 in the date 1/1/2010 the model was C.

is there any function that can do that or even macro that can help in that case.

thank you in advance

Samuel
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><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><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Number</td><td style=";">Date1</td><td style=";">Date2</td><td style=";">Model</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">109</td><td style="text-align: right;;">12/2/2003</td><td style="text-align: right;;">7/29/2005</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">109</td><td style="text-align: right;;">7/29/2005</td><td style="text-align: right;;">11/23/2009</td><td style=";">B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">110</td><td style="text-align: right;;">1/1/2010</td><td style=";">C</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">109</td><td style="text-align: right;;">11/23/2009</td><td style="text-align: right;;">11/11/2010</td><td style=";">C</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">109</td><td style="text-align: right;;">11/11/2010</td><td style="text-align: right;;">12/19/2010</td><td style=";">D</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">109</td><td style="text-align: right;;">12/19/2010</td><td style="text-align: right;;">4/25/2013</td><td style=";">E</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">109</td><td style="text-align: right;;">4/25/2013</td><td style="text-align: right;;">3/24/2015</td><td style=";">F</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">109</td><td style="text-align: right;;">3/24/2015</td><td style="text-align: right;;">12/28/2050</td><td style=";">G</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">110</td><td style="text-align: right;;">12/2/2003</td><td style="text-align: right;;">7/29/2005</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">110</td><td style="text-align: right;;">7/29/2005</td><td style="text-align: right;;">12/23/2009</td><td style=";">B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">110</td><td style="text-align: right;;">12/23/2009</td><td style="text-align: right;;">11/2/2010</td><td style=";">C</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">110</td><td style="text-align: right;;">11/2/2010</td><td style="text-align: right;;">12/19/2010</td><td style=";">D</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">110</td><td style="text-align: right;;">12/19/2010</td><td style="text-align: right;;">3/21/2013</td><td style=";">E</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">110</td><td style="text-align: right;;">3/21/2013</td><td style="text-align: right;;">3/24/2015</td><td style=";">F</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">110</td><td style="text-align: right;;">3/24/2015</td><td style="text-align: right;;">5/20/2015</td><td style=";">G</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">110</td><td style="text-align: right;;">5/20/2015</td><td style="text-align: right;;">5/26/2015</td><td style=";">H</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">110</td><td style="text-align: right;;">5/26/2015</td><td style="text-align: right;;">12/28/2050</td><td style=";">J</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">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: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">I3</th><td style="text-align:left">{=INDEX(<font color="Blue">$D$2:$D$17,MATCH(<font color="Red">G3,IF(<font color="Green">$B$2:$B$17<=H3,IF(<font color="Purple">$C$2:$C$17>=H3,$A$2:$A$17</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,251
Messages
5,527,636
Members
409,778
Latest member
MagalieD

This Week's Hot Topics

Top