CODEWITHSUNDEEP
excelexcel-formulaexcel-2010formulas
user3066795
user3066795

Reputation: 41

Excel Formula to lookup the nearest greater number based on date

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

Upvotes: 0

Views: 130

Answers (1)

L42
L42

Reputation: 19727

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

Upvotes: 0

Related Questions