15,880,364 members
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

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.
Posted
Updated 13-Jun-15 1:42am
v2
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

Solution 1

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

v2
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