Search date range +/- 7 days and return date that falls within range

[tlafferty]

I have a table of dates arranged in a vertical column. I would to enter a date and find the first date that is within 15 days of that date (-7 date +7). If no date in the table is within 15 days of the search date, I would like to return #N/A. Note that most of the dates in the look up column are separated by more than 7 days, but not all. I would prefer to use a native Excel function rather than a UDF, if possible.

See attached workbook for desired results and data.

Thanks!

[FDibbins]

which date are you searching on, and which column are you searching IN?

Maybe if you gave a sample, it will be easier to see what you want?

[tlafferty]

Thanks for the reply, and sorry about not being clearer. Here's what I'm after. In the A column of the book I attached, I have a search date. In the B column, I show what it should return when examining the dates in the G column. The dates in the D column are also being checked against the G column dates.

Looking at cell A2, I can't find any date in the G column that is within 15 days, so I put #N/A in B2. The first date in the A colunmn that is within 15 days of any date in the G column is in cell A6, and the corresponding date is 10/4/2014. No other date is within 15 days of that date in the G column.

Let me know if you need additional clarification, and thanks again for the reply.

[tlafferty]

Bump... any takers? Need more information?

[FDibbins]

forum rules require that you wait at least a day before bumping

[FDibbins]

Im still working onthis, but take a look at following, copied down...
=IF(COUNTIFS(G:G,">="&A2-7,G:G,"<="&A2+7)>0,INDEX(G:G,IFERROR(MATCH(A2,G:G,-1),MATCH(A2,G:G,1)),1),"")

[tlafferty]

Thank you for your work on this! I'm away from my workstation till sometime tomorrow, But I will check it out first chance I get and will let you know.

[ncmay]

Try the formula below as an array formula (Control+Shift+Enter) in cell B2 and copy down.

=INDEX($G$2:$G$46,MATCH(1,($G$2:$G$46>=A2-7)*($G$2:$G$46<=A2+7),0))

It returns the results indicated in the column B group but differs slightly in the column E group.