Closest date lookup

[Jono_NZ]

Hi,

I'm trying to do the following:

Sheet One

List of..................Campaign
Regions................Close
-------------------------
AK.......................30 June 09
AK.......................30 July 10
AK.......................30 May 11
CH.......................30 June 09
CH.......................30 July 10
CH.......................30 June 11
DN.......................30 July 09
DN.......................30 June 10
DN.......................30 August 11

Sheet Two

Region.................Sold Date
---------------------------
AK.......................15April 2010

And based on the criteria in sheet two I'm trying to return the closest campaign date by region.

Any ideas anyone has would be appreciated!

Cheers

 

[bill chu]

Hi Jono,
Assume the campaign dates are arranged in order.
Assume the "closest campaign date" is the largest prior date.
You can input below formula beside sold date in sheet2 and hit ctrl+shift+enter to get the result;

=LOOKUP(0,IF((Sheet1!$A$1:$A$9=Sheet2!A1),Sheet1!$B$1:$B$9-Sheet2!B1),Sheet1!$B$1:$B$9)

Sheet1!$A$1:$A$9 is list regions area;
Sheet1!$B$1:$B$9 is compaign close area;
Sheet2!A1 is target region in sheet2;
Sheet2!B1 is sold date;

Don't forget to use the date format of the result cell.

Bill

 

[Jono_NZ]

Thanks. I was meaning however the closest campaign date to the sold date, so for AK 15 April 2010 it would return 30 July 2010.

 

[Marcelo Branco]

Assuming your data in Sheet 1 columns A B (headers in row 1) maybe this in Sheet 2

A B C
<TABLE style="WIDTH: 173pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=230><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2825117 class=xl67 height=20 width=64>Region



</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=82>Sold Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 63pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=84>Closest Date</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>AK</TD>



<TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>15/abr/10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>30/jul/10</TD>

</TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>CH</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>30/mar/11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>30/mai/11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>DN</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>30/jan/10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>30/jul/10</TD></TR></TBODY></TABLE>

Array-Formula in C2

=INDEX(Shee1!$B$2:$B$100,MATCH(MIN(IF(Shee1!$A$2:$A$100=A2,ABS(Shee1!$B$2:$B$100-B2))),IF(Shee1!$A$2:$A$100=A2,ABS(Shee1!$B$2:$B$100-B2)),0))

confirmed with Ctrl+Shift+Enter
(hold down both Crtl and Shift keys and hit Enter)

HTH

M.
ps: abr in potuguese = apr in english; mai = may

 

[bill chu]

=LOOKUP(0,0/IF(Sheet1!A1:A9=Sheet2!A1,MIN(IF(Sheet1!A1:A9=Sheet2!A1,ABS(Sheet1!B1:B9-Sheet2!B1)))=ABS(Sheet1!B1:B9-Sheet2!B1)),Sheet1!$B$1:$B$9)

 

[Jono_NZ]

Thanks - got the index formula to work.

 

[Marcelo Branco]

Thanks - got the index formula to work.

You are welcome and tks for the feedback

M.