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.

	A	B	C	D	E	F	G	H	I
1	Name	Effective Date	Expiration Date	ID			Service Name	Service Date	Lookup ID
2	Frank Smith	2/14/19	7/13/19	2199			Frank Smith	6/20/19	2199
3	Frank Smith	7/14/19	12/20/2020	2200			Frank Smith	6/29/19	2199
4							Frank Smith	7/12/19	2199
5							Frank Smith	7/13/19	2199
6							Frank Smith	7/14/19	2200
7							Frank Smith	7/18/19	2200

<tbody>
</tbody>

 

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