I've tried searching around for an answer to this for awhile but with no luck.
Currently using, Excel 2007 on a Windows XP system.
Essentially, I have data as below
So, I'm trying to 'VLOOKUP' the ID Num2 value, accross to Sheet1. Based upon the ID Num1 and the date, but the date can be a couple of days different. I would strip the days, so just looking up the Month / Year value, but I can't do this because some ID Nums weekly, but none are under a week frequency. So I want a 6 day margin for range.
As far as I've got so far is the below formula,
The issue is, that this only matches if the dates are exactly the same. What I require is a few days give or take.
I've attempted this, basically throwing in a AND Lower and Higher 3 days. It's not having it though.
/Edit :: Gah, this board is obsessed with removing half of my formula, even though in Code tags.
Thank you in advance for this, I'm sure I'm close, I just don't fully understand how the array syntax should be layed out. I'm guessing its not as simple as just chucking in an AND statement.
Cheers
[/FONT]
Currently using, Excel 2007 on a Windows XP system.
Essentially, I have data as below
Code:
[FONT=Courier New]Sheet1[/FONT]
[FONT=Courier New]ID Num1. Date Id Num2[/FONT]
[FONT=Courier New]PM001 03/01/2012[/FONT]
[FONT=Courier New]PM002 06/01/2012[/FONT]
[FONT=Courier New]PM003 09/01/2012[/FONT]
[FONT=Courier New]PM001 02/02/2012[/FONT]
[FONT=Courier New]PM002 09/02/2012[/FONT]
[FONT=Courier New]PM003 12/02/2012[/FONT]
Code:
[FONT=Courier New]Sheet2[/FONT]
[FONT=Courier New]ID Num1. Date Id Num2[/FONT]
[FONT=Courier New]PM001 02/01/2012 2147[/FONT]
[FONT=Courier New]PM002 06/01/2012 2527[/FONT]
[FONT=Courier New]PM003 07/01/2012 2767[/FONT]
[FONT=Courier New]PM001 02/02/2012 2165[/FONT]
[FONT=Courier New]PM002 10/02/2012 2321[/FONT]
[FONT=Courier New]PM003 11/02/2012 2854[/FONT]So, I'm trying to 'VLOOKUP' the ID Num2 value, accross to Sheet1. Based upon the ID Num1 and the date, but the date can be a couple of days different. I would strip the days, so just looking up the Month / Year value, but I can't do this because some ID Nums weekly, but none are under a week frequency. So I want a 6 day margin for range.
As far as I've got so far is the below formula,
Code:
{=INDEX(Sheet2!$C$2:$C$7,MATCH(1,(Sheet2!$A$2:$A$7=$A2)*(Sheet2!$B$2:$B$7=$B2),0))}The issue is, that this only matches if the dates are exactly the same. What I require is a few days give or take.
I've attempted this, basically throwing in a AND Lower and Higher 3 days. It's not having it though.
Code:
{=INDEX(Sheet2!$C$2:$C$7,MATCH(1,(Sheet2!$A$2:$A$7=$A2)*(AND(Sheet2!$B$2:$B$7>DATE(YEAR($B2),MONTH($B2),DAY($B2)-3),Sheet2!$B$2:$B$7<DATE(YEAR($B2),MONTH($B2),DAY($B2)+3))),0))}/Edit :: Gah, this board is obsessed with removing half of my formula, even though in Code tags.
Thank you in advance for this, I'm sure I'm close, I just don't fully understand how the array syntax should be layed out. I'm guessing its not as simple as just chucking in an AND statement.
Cheers
[/FONT]
Last edited:
