Match date +/- days

jlopl

New Member
Joined
Nov 30, 2016
Messages
7
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?
abcdefg
1datevalueamount
23/1/17
10#N/A
3
4
5
6
7dateamount
81/31/1742.88
92/28/1792.88
103/31/17100
11

<tbody>
</tbody>
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
For the short answer, instead of
Code:
MATCH($D2,$A:$A,0)
try
Code:
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.
 

Forum statistics

Threads
1,148,294
Messages
5,745,936
Members
423,985
Latest member
sayed manzar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top