将两个单元格值与一系列值进行比较 [英] Comparing two cell values with a range of values
问题描述
我一直在寻找解决问题的方法。我尝试了几个公式，如VLOOKUP，INDEX .. MATCH，似乎没有任何工作。
我有两个范围值，如下所示
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
我需要将第二个范围值与第一个范围值进行比较并获得利率列填写第一个范围的第二个范围。
这是业务规则。将第二个范围中的第一个日期单元格值与第一个中的日期值列表进行比较，如果日期单元格值小于或等于第一个范围中的任何日期列表值，则相应的费用代码值对于第二个范围内的日期单元格值，INTEREST，第一个范围内的利率列值需要在第二个范围的利率列中填充。
任何公式或VBA代码都会有很大帮助。
提前感谢您提供有价值的解决方案。
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.
推荐答案
第1步 - 突出显示前两列中的所有数据（不包括标题，如果您稍后添加更多数据，最好继续超出可用数据的底部。）
步骤2 - 为所选区域指定名称。最简单的方法是将其输入名称框字段（通常在公式栏的左侧）或右键单击并选择定义N a me
在我的例子中，我突出显示了A2到B21的单元格，并将其命名为Rate
步骤3 - 查看利率对于第二组数据中的每个日期都有效（在我的情况下，第二组日期在D列中）：LOOKUP（D2，Rates），但是包装它基于收费代码进入IF功能。
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 anIFfunction based on the "Charge Code".
=IF(E2="INTEREST",LOOKUP(D2,Rates),"")
第4步 - 将该公式拖放到完整的项目列表中，以便Excel自动更改行号。
这些是我的结果：
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
这篇关于将两个单元格值与一系列值进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
