New beginner.


Version Window 10
Excel 2016 but my colleague Version 2010
I had French version using ( to seperate the formula but my colleague has English version which using (,)
Actual database with 18000 lines on Base file & 200 lines on BAF table.


I have two table which has different range of date, BAF value & the helper column which can find in following table .
The principal & the goal is to match the range of BAF table & give the correct period of information & if it's out of range, got error message.
It can allow duplicate record & can add the data on both BAF & Base file with new information.


Example
Case 1 : Base table : dated 1/3 to 31/3, BAF table has information : 1/3 to 31/3, 1/4 to 30/4, 1/5 to 31/5.
I would like the data only take the information from 1/3 to 31/3. (not 1/4 to 30/4 & 1/5 to 31/5)


Case 2 : Base table : Dated 1/4 to 15/4 & 16/4 to 30/4.
BAF table (same as case 1) : the result need to show between 1/4 to 30/4.


Case 3 : Base table: (a) dated 1/3 to 31/3 or 1/7 to 31/7 which assume BAF table does not have those period which need to show error message : N/A.
(b) dated 1/3 to 1/6 but BAF table has only the rate from 1/3 to 31/5 only, error message : end date larger than validity. pls input correctly.


Case 4 : Base table : dated 1/4 to 31/5, BAF table, same as Case 1.
I would like the result only show Apr BAF table.
Case 5 : Base table: dated 1/4 to 30/4, BAF table, 1/4 to 30/6.
I would like the result can show the BAF value without problem.


Base file table (around 18000 lines)
Start dateEnd dateHelper 1BAF rate1/430/4ABCDE (it will be change accordingly to the combination)2001/531/5ABCDE2501/331/3ABCDE130

BAF file table (around 200 lines)
Start dateEnd dateHelper 1BAF value1/331/3ABCDE130

If you need any further question for set up the formula, I also att'd the file for easy understand.

Tks for your help in advance !


Rgds
Ale

Attached Files

formula with date need to match.xlsx (13.3 KB, 12 views)