VLOOKUP then return date between two dates

[mnoah]

Hi all,

I need to do a VLOOKUP combined with a way to get a value association between two dates.

On the left hand side is the range where authorization IDs and effective/expiration dates are. On the right hand side is where a lot of data will be with names, service dates, and a column to find the applicable ID for that service date. So if Frank Smith has a service date of between 2/14/19 and 7/13/19, it needs to return "2199"

I'm able to get the date between two dates given the Service Date from the article here (https://www.extendoffice.com/documents/excel/2702-excel-vlookup-between-date-range.html) but I need to also include the Name of the person.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Effective Date[/TD]
[TD]Expiration Date[/TD]
[TD]ID[/TD]
[TD][/TD]
[TD][/TD]
[TD]Service Name[/TD]
[TD]Service Date[/TD]
[TD]Lookup ID[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Frank Smith[/TD]
[TD]2/14/19[/TD]
[TD]7/13/19[/TD]
[TD]2199[/TD]
[TD][/TD]
[TD][/TD]
[TD]Frank Smith[/TD]
[TD]6/20/19[/TD]
[TD]2199[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Frank Smith[/TD]
[TD]7/14/19[/TD]
[TD]12/20/2020[/TD]
[TD]2200[/TD]
[TD][/TD]
[TD][/TD]
[TD]Frank Smith[/TD]
[TD]6/29/19[/TD]
[TD]2199[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Frank Smith[/TD]
[TD]7/12/19[/TD]
[TD]2199[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Frank Smith[/TD]
[TD]7/13/19[/TD]
[TD]2199[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Frank Smith[/TD]
[TD]7/14/19[/TD]
[TD]2200[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Frank Smith[/TD]
[TD]7/18/19[/TD]
[TD]2200[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:90.3px;" /><col style="width:117.86px;" /><col style="width:128.32px;" /><col style="width:33.27px;" /><col style="width:15.21px;" /><col style="width:15.21px;" /><col style="width:117.86px;" /><col style="width:119.76px;" /><col style="width:90.3px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Name</td><td >Effective Date</td><td >Expiration Date</td><td >ID</td><td > </td><td > </td><td >Service Name</td><td >Service Date</td><td >Lookup ID</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Frank Smith</td><td style="text-align:right; ">14/feb/2019</td><td style="text-align:right; ">13/jul/2019</td><td style="text-align:right; ">2199</td><td > </td><td > </td><td >Frank Smith</td><td style="text-align:right; ">20/jun/2019</td><td style="text-align:right; ">2199</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Frank Smith</td><td style="text-align:right; ">14/jul/2019</td><td style="text-align:right; ">20/dic/2020</td><td style="text-align:right; ">2200</td><td > </td><td > </td><td >Frank Smith</td><td style="text-align:right; ">29/jun/2019</td><td style="text-align:right; ">2199</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Frank Smith</td><td style="text-align:right; ">12/jul/2019</td><td style="text-align:right; ">2199</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Frank Smith</td><td style="text-align:right; ">13/jul/2019</td><td style="text-align:right; ">2199</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Frank Smith</td><td style="text-align:right; ">14/jul/2019</td><td style="text-align:right; ">2200</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Frank Smith</td><td style="text-align:right; ">18/jul/2019</td><td style="text-align:right; ">2200</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >I2</td><td >=SUMPRODUCT(($A$2:$A$3=G2)*($B$2:$B$3<=H2)*($C$2:$C$3>=H2)*($D$2:$D$3))</td></tr></table></td></tr></table>