Excel Formula to Associate two data sets together based on exact match closets, match, and no match

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 and bring me back the difference between the two. The amount on Sheet 1 will always be greater than or equaled to what is on sheet 2 for the proper match up. 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
 09/09/2014  $645

Sheet 2 will have

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

Basically what the end result should be is for the formula to give the difference between what is on sheet 1 and what is on sheet 2. the formula that compares sheet 1 and sheet 2 cannot be based on a parameter for example a this is 90% of this so these are to be matched. this is because there will be situations where they should be matched up even though whats on sheet 2 is only 10% of whats on sheet 1 but its the only instance of that number on sheet 1 so they must be matched up. So the final results should look like this below 

 09/08/2014 $3,838 = $3,838
 09/08/2014 $564 = $3
 09/08/2014 $1023 = 0
 09/09/2014 $645 = $610

I believe what I am looking for is going to involve indexing the formula among other things. I have tried doing this formula a ton of ways already including an extensive nested if and vlookup and also adding some sumifs in a separate column and concatenating dates and amounts but that route didnt lead me any success either. Feel free to add stuff to other columns to go off of. The first rule in the formula I think would be to match the exact numbers then match the next closest numbers and subtract them then any left over unmatched would be just the number of sheet 1 but numbers on sheet 2 can only be used once

1
arraysexcelindexingexcel-formulaformula

1 Answers

0
votes

I think I understand what you want but please correct me if I have it wrong. Based on what I think you want then how about this.

I assume:

  • There are two columns of data in Sheet 1 and two columns of data in Sheet 2
  • In both cases there are dates in column A and money amounts in column B
  • The data starts in row 1 and continues in contiguous rows down the sheet
  • The rows of both sheets are sorted first by date (column A) oldest to newest and then by value (column B) smallest to largest

Then put this formula in cell C1 in Sheet 1 and enter it as an array formula (With the cell in edit mode press CTRL+SHIFT+ENTER instead of just ENTER). You'll know if you did this correctly since the formula in the formula bar will then be wrapped in braces, i.e. {=}.

=MIN(ABS(OFFSET(Sheet2!$A$1,MATCH($A1,Sheet2!$A$1:$A$13,0)-1,1,COUNTIF(Sheet2!$A$1:$A$13,"="&Sheet1!$A1),1)-$B1))

Then copy that formula down column C for all rows.

That should do what you want.