VLOOKUPs填充Excel表 [英] VLOOKUPs to Populate an Excel Table
问题描述
我在编写VLOOKUP以排序某些数据时遇到问题。
I'm having trouble writing a VLOOKUP to sort some data.
我有一个表格,其数据类似于:
I have one table that has data that looks like this:
MarkAsOfDate MaturityDate ZeroRate
05-May-15 05-May-15 0.006999933
05-May-15 06-May-15 0.006999933
05-May-15 05-Jun-15 0.008996562
05-May-15 06-Jul-15 0.008993128
... ....
$ b b
我想为每个实例创建一个表,其中第一列和第二列中的日期之间的间隔正好为一个月(例如05 - 5月15日和05 - 6月-15日),以及空白,其中没有这样的值存在。
I want to make a table with every instance where the interval between the dates in the first and second columns is exactly one month (such as 05 - May - 15 and 05 - Jun - 15), and with blanks where no such value exists.
所以我做了第二个表格,看起来像:
So I made a second table which looks like:
MarkAsofDate MaturityDate Zero Rate 1M
5-May-15 5-Jun-15
6-May-15 6-Jun-15
7-May-15 7-Jun-15
8-May-15 8-Jun-15
9-May-15 9-Jun-15
.... ....
我想使用第一个表中的数据填充此表。我试图写一个VLOOKUP它,但我不知道如何做到两个列而不是一个。
I want to populate this table using data from the first table. I've tried to write a VLOOKUP for it but I'm not sure how to do it with two columns instead of one.
提前感谢。
推荐答案
Vlookup有一些限制,因为你开始看到。另一个建议是索引/匹配。
Vlookup has some limitations, as you're starting to see. Another suggestion is Index/Match. Use this in your second table.
注意:我假设您的顶部表位于 Sheet1
。把这放在你的C2,在第二个表(在零利率1M头下):
Note: I assume your top table is in Sheet1
. Put this in your C2, in the second table (under the "Zero Rate 1M" header):
=索引(Sheet1!$ C: $ C,match(A2& B2,Sheet1!$ A:$ A& Sheet1!$ B:$ B,0))
,然后按 CTRL + SHIFT + ENTER 。
这篇关于VLOOKUPs填充Excel表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!