Match date +/- days

[jlopl]

Hi all,

I have a two tables and I am using the index match formula to lookup data and headers to do some math.

formula in cell G1 is =INDEX($A:$B,MATCH($D2,$A:$A,0),MATCH(G$1,$7:$7,0))*$E2*$C9/100

I want the result to be 9.288 (match should be 92.88*10/100)

So the ask is:

how can I tell excel to look at the date in column D and if its with 2-3 days of the dates in column A match it (the 2/28/17 date) to that row?

	a	b	c	d	e	f	g
1				date	value		amount
2				3/1/17	10		#N/A
3
4
5
6
7	date	amount
8	1/31/17	42.88
9	2/28/17	92.88
10	3/31/17	100
11

<tbody>
</tbody>

 

[Oaktree]

For the short answer, instead of

MATCH($D2,$A:$A,0)

try

MATCH(TRUE,ABS(A:A - D2)<=3,0)

BUT, in doing so, this is now an array formula that must be confirmed with CTRL+SHIFT+ENTER instead of just ENTER. Doing so correctly will result in Excel putting { }s around your formula in the formula bar.

Note also that you probably don't want full columns in your array formula. Use A1:A50 instead of A:A or, maybe better still, consider changing your range to an Excel table so that you don't have to worry about changing the formulas if/when new data is appended to the bottom.

 

[jlopl]

thank you