vlookup between 2 worksheets, return yes/no if date is between date range

Hello, this is my first post. I have searched and searched for a formula that will work for my needs, will no luck. I need to identify if an insurance agents license was active when they wrote an insurance policy. The policy date, state/license # are on one sheet and their state/license # with effective and renewal date are on another.

I need to look up the unique value from sheet 1 column H by row, and locate it in sheet 2 column D. Once located if the validate if the date in Sheet 1 column F (same row) is between the dates on sheet 2, greater than column F & less than column H (for the row identified by vlookup). Then return yes if true and no if false. I have tried several formula variations, vlookup, index/match, if, and, isna, etc. The one below is my most recent but none of them work. I feel like I am really close but just not quite there. Hopefully I have included enough to assist me.


Sheet 1
F G H I
Created On License # LIC STATE/# Compliant Y/N
04/04/17 366957 AR/366957 =IF(AND(VLOOKUP(H2,Sheet2!D:D,1,FALSE)*(F2=Sheet2!F:F,F2<=Sheet2!H:H)),"No","Yes")
04/19/17 366957 AR/366957
05/09/17 366957 AR/366957
05/10/17 366957 AR/366957
05/15/17 366957 AR/366957
04/05/17 366957 AR/366957
04/10/17 366957 AR/366957
04/19/17 366957 AR/366957
04/24/17 366957 AR/366957

Sheet 2
D E F G H
LIC STATE/# LIC STATUS LIC EFF DATE End Date LIC REN DATE
AR/366957 AC 06/08/2010 8/1/2017 06/15/2018
CA/0G88933 AC 01/25/2010 8/1/2017 04/30/2019
CO/427732 AC 03/28/2017 8/1/2017 06/30/2018
IL/15401554 AC 03/27/2017 8/1/2017 06/30/2019
KY/720924 AC 01/11/2010 8/1/2017 06/30/2018
LA/503474 AC 01/11/2010 8/1/2017 06/30/2019
MS/10172083 AC 01/09/2010 8/1/2017 06/30/2019
NC/15401554 AC 12/03/2009 8/1/2017 06/30/2018
TN/2009231 AC 06/04/2010 8/1/2017 06/30/2018
TX/1627910 AC 06/08/2010 8/1/2017 06/15/2018
OH/826977 DNR 10/12/2010 8/1/2017 06/30/2018
FL/P224817 DNR 01/22/2010 8/1/2017


Any and all help anyone can provide is greatly appreciated!!

Hi mmor5,

Please use this formula =IFERROR(IF((VLOOKUP(H2,Sheet2!D$2:H$13,5,0)>F2>VLOOKUP(H2,Sheet2!D$2:F$13,3,0)),"Y","N"),"N")

Regards,

Ritu Raj

Thank you so much Ritu Raj! We are very close but it is bring back "Y" every time. It is not bringing back "N" when the date in column F on sheet 1 does not fall in between the dates in columns F & H matching the vlookup. Would a specific example help?

Updated formula is : =IFERROR(IF((AND(VLOOKUP(H2,Sheet2!D$2:H$13,5,0)>F2,F2>VLOOKUP(H2,Sheet2!D$2:F$13,3,0))),"Y","N"),"N")
Try this. Sorry i forgot to add AND.

For example change the date in f2, before 6/8/2010 or after 6/15/2018. It will give "N".

Hello Ritu - I apologize for the last response. I thought that the AND formula was missing. I actually tried to add it in myself but did get it quite right. The last formula you provided is working perfectly!!

Thank you so much for your assistance, this saved me so much additional time.
Melanie