Comparing two cell values with a range of values

Question

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 12-Jun-15 20:52pm

Sundar9057

Updated 13-Jun-15 1:42am

CHill60

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2015-06-13T02:50:00

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