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:

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,219
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,089,847
Messages
5,410,730
Members
403,325
Latest member
sherrir

This Week's Hot Topics

Top