VLOOKUP then return date between two dates

mnoah

Board Regular
Joined
Oct 14, 2015
Messages
54
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.

ABCDEFGHI
1NameEffective DateExpiration DateIDService NameService DateLookup ID
2Frank Smith2/14/197/13/192199Frank Smith6/20/192199
3Frank Smith7/14/1912/20/20202200Frank Smith6/29/192199
4Frank Smith7/12/192199
5Frank Smith7/13/192199
6Frank Smith7/14/192200
7Frank Smith7/18/192200

<tbody>
</tbody>
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,666
Office Version
2007
Platform
Windows
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>
 

Forum statistics

Threads
1,077,635
Messages
5,335,379
Members
399,014
Latest member
hamzalaarif

Some videos you may like

This Week's Hot Topics

Top