Excel Formula to lookup the nearest greater number based on date

0
votes

I have a sheet with a column of dates some may repeat and amounts in the column next to those dates. On another sheet I also have a sheet full of dates some that may repeat and other amounts. I need a formula that will go through the first sheet's dates and find the nearest amount to that date thats on the second sheet. An example of my problem is detailed below.

Sheet 1 will have 

 09/08/2014  $3,838
 09/08/2014  $564
 09/08/2014  $1023

Sheet 2 will have

09/08/2014 $561
09/08/2014 $1023

Basically what the end result should be is for the formula to give the results of which dollar amounts on sheet 1 have exact matches near matches (based on being greater than whats on sheet 2) and no matches. So for sheet 1

 09/08/2014 $3,838 = Nomatches
 09/08/2014 $564 = Nearmatch
 09/08/2014 $1023 = Exactmatch
1
excelexcel-formulaexcel-2010formulas
This can be done but what is your tolerance to consider it near match? – L42
there isnt going to be a set tolerance level its basically just as long as what is on sheet 1 is > than what is on sheet 2 but which ever is closer on sheet 1 is the near match – user3066795
no the data is being sorted somewhere else and cannot be resorted. – user3066795

1 Answers

0
votes

Ok here's a possible way. I set the tolerance to 90%.
So if a value in Sheet2 is 90% of your value in Sheet1 that is a Nearmatch.

=IF(ISERROR(VLOOKUP(B2,IF(A2=Sheet2!A:A,Sheet2!B:B),1,0)),IF(VLOOKUP(B2,IF(A2=Sheet2!A:A,Sheet2!B:B),1,1)>=0.9*B2,"Nearmatch","Nomatches"),"Exactmatch")

Use formula above and enter using Ctrl + Shift + Enter.
So suppose you have a data like this in Sheet1.

enter image description here

Enter formula in C2. Then just copy to remaining cells.
For this to work, you need to have your data in Sheet2 sorted ascending by Amount like below. HTH.

enter image description here