Click here to Skip to main content
15,880,364 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi,

I have been seeking out for a solution for a problem. I have tried a couple of formulas like VLOOKUP, INDEX.. MATCH and nothing seemed to be working.
I have two range values as like below
Date    Rate of Interest        Date    Charge code Interest Rate
1/2/2014    1.1     5/27/2015   INTEREST
3/20/2014   1.2     5/10/2015   INTEREST
5/20/2014   1.3     4/12/2015   AUDIT FEE
10/20/2014  1.3     3/30/2015   LEGAL FEE
1/10/2015   1.4     2/20/2015   COLLECTION
6/13/2015   1.5     1/10/2015   COLLECTION
11/20/2015  1.6     12/20/2014  COLLECTION
12/20/2015  1.7     8/20/2014   COLLECTION
12/21/2015  1.8     7/11/2014   INTEREST
12/22/2015  1.9     6/30/2014   INTEREST
12/22/2015  2       5/4/2014    COLLECTION
12/23/2015  2.1     4/12/2014   COLLECTION
12/24/2015  2.2     4/12/2014   COLLECTION
12/25/2015  2.3     3/20/2014   COLLECTION
12/26/2015  2.4     2/10/2014   COLLECTION
12/27/2015  2.5     1/2/2014    COLLECTION
12/30/2015  2.6     1/2/2014    ADVANCE

I need to compare the second range value with the first one and get the Interest Rate column filled for the second range from the first one.

Here is the business rule. The first date cell value in the second range would be compared with the list of date values in the first and if the date cell value is less than or equal to any of the date list values in the first range, and the corresponding charge code value is “INTEREST” for the date cell value in the second range, the Rate of Interest column value in the first range needs to be gotten populated in the Interest Rate column of the second range.

Any formula or VBA code would help greatly.
Thank you in advance for your valuable solution.
Posted
Updated 13-Jun-15 1:42am
v2
Comments
Ralf Meier 13-Jun-15 5:27am    
Did you have achieved anything until now ?
Are you familiar with VBA-coding and generating methods ?
Sundar9057 13-Jun-15 6:47am    
No Ralf, not arrived at any solution. Though I have not tried using VBA-code to generate methods, I don't think that would be tough for me to understand as I am a VB6 programmer

1 solution

Step 1 - highlight all of the data in the first two columns (don't include the headings, and it's a good idea to continue beyond the bottom of the available data in case you add more data later).

Step 2 - assign a name to the selected area. Easiest way is to type it into the "Name Box" field (usually to the left of the formula bar) or right-click and choose "Define Name"

In my example I highlighted cells A2 to B21 and gave it the name "Rates"

Step 3 - look up the interest rate that was effective for each of the dates in the second set of data (In my case the 2nd set of dates is in Column D): LOOKUP(D2,Rates), but wrapping it into an IF function based on the "Charge Code".
=IF(E2="INTEREST",LOOKUP(D2,Rates),"")


Step 4 - drag that formula down the full list of items so that the row numbers are automatically altered by Excel.

These were my results:
Date	Rate of Interest		Date		Charge Code	Interest Rate
02/01/2014	1.1			27/05/2015	INTEREST	1.4
20/03/2014	1.2			10/05/2015	INTEREST	1.4
20/05/2014	1.3			12/04/2015	AUDIT FEE	
20/10/2014	1.3			30/03/2015	LEGAL FEE	
10/01/2015	1.4			20/02/2015	COLLECTION	
13/06/2015	1.5			10/01/2015	COLLECTION	
20/11/2015	1.6			20/12/2014	COLLECTION	
20/12/2015	1.7			20/08/2014	COLLECTION	
21/12/2015	1.8			11/07/2014	INTEREST	1.3
22/12/2015	1.9			30/06/2014	INTEREST	1.3
22/12/2015	2			04/05/2014	COLLECTION	
23/12/2015	2.1			12/04/2014	COLLECTION	
24/12/2015	2.2			12/04/2014	COLLECTION	
25/12/2015	2.3			20/03/2014	COLLECTION	
26/12/2015	2.4			10/02/2014	COLLECTION	
27/12/2015	2.5			02/01/2014	COLLECTION	
30/12/2015	2.6			02/01/2014	ADVANCE	
 
Share this answer
 
v2
Comments
CHill60 13-Jun-15 9:55am    
So you don't want the interest rate that was in force on a given date, but the one that came into force after that date??
CHill60 13-Jun-15 9:59am    
I have to leave now but if you want the next value then use MATCH and ADDRESS to get the location of where it found the value, then grab the next rows value. Sorry - I will come back to this if I can, but in the meantime here is an example[^] of what I mean

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900