VLookup With Date Range & Additional Criteria

[fsunole98]

Hey Guys,

Desperately need some help here. I have a column where I need to

- lookup a date and see if it falls in-between two dates displayed on another sheet
- Make sure the dates it is looking up are for the correct advertiser

What I need it to return is either 'Active" or "Previous IO" if the date does not fall in the range.

So in the example below I need to look up if the date on column 2 for 7/1/17 is an Active or under previous IO based on the date ranges in sheet 2.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Status
PERSAV_Q1	Macys

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="168"></colgroup><tbody>
[TD="bgcolor: #6d9eeb"]Date[/TD]
[TD="bgcolor: #6d9eeb"]Placement[/TD]
[TD="bgcolor: #6d9eeb"]Advertiser[/TD]

[TD="align: right"]2017-08-01[/TD]

[TD="align: center"]?[/TD]

</tbody>

In Sheet two I have this:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Macys

<colgroup><col style="width: 100px"><col width="100"><col width="100"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4A86E8]#4A86E8[/URL] , align: center"]Advertiser[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4A86E8]#4A86E8[/URL] , align: center"]Start Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4A86E8]#4A86E8[/URL] , align: center"]End Date[/TD]

[TD="align: center"]8/1[/TD]
[TD="align: center"]8/31[/TD]

</tbody>

I tried the following formula (that has more data) but this does not incorporate the advertiser lookup in it:

==LOOKUP(2,1/('Sheet 2'!B2:B14<=A1)/('Sheet 2'!C2:C14>=A1),'Sheet2'!G2:G14)

Test - Google Sheets

Thank you!!<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[Nine Zero]

This is what I came up with

Assuming your data begins with headers on both sheets at cell A1

Cell D2 <a2,vlookup(c2,sheet2!a:c,3,0)>=IF(AND(VLOOKUP(C2,Sheet2!A:C,2,0)[COLOR=#ff0000]"[/COLOR]<[COLOR=#ff0000]"[/COLOR]A2,VLOOKUP(C2,Sheet2!A:C,3,0)[COLOR=#ff0000]"[/COLOR]>[COLOR=#ff0000]"[/COLOR]A2),"Active","Previous IO")

Remove the red quotations as the forum thinks it is HTML tags so they keep dissapearing</a2,vlookup(c2,sheet2!a:c,3,0)>

 

[fsunole98]

This is very helpful!

in my real example i am getting the '#n/a" error saying that it "did not find value '24917' in vlookup evaluation". I think the issue is that the formula is not looking up the actual date but the number you get for 2017-07-01 (which is what the vlookup is comparing to).

How can I fix this? the B2 value is what is not being read correctly.

 

[Nine Zero]

Could you tell me the exact placements of your data like the top left cell where each of your data begins for each sheet

I tried with your date formatting and it still worked fine...

if you tell me your coordinates i can formulate it for you to just copy paste

 

[fsunole98]

IF(AND(VLOOKUP(G2,'Campaign Dates'!A1:B14,2,0)<B2,VLOOKUP(B2,'Campaign Dates'!A1:C14,3,0)>B2),"Previous IO","Active")

g2 = advertiser name
b2 = date for lookup (2017-07-01)
campaign dates = sheet 2 (same setup but just more data)

 

[Nine Zero]

Ahh i see your issue

So we need to continue to look for the advertiser in both vlookups

so instead of B2 in the vlookup it needs to remain G2

IF(AND(VLOOKUP(G2,'Campaign Dates'!A1:B14,2,0)<B2,VLOOKUP(G2,'Campaign Dates'!A1:C14,3,0)>B2),"Previous IO","Active")

 

[fsunole98]

You are a god among men. Thank you so much!!!!!!

 

[Nine Zero]

No problem, glad i could help