VLOOKUPs to Populate an Excel Table

Question

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
   ...               ....

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    
 ....                ....

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.

Thanks in advance.

Answer 1

Vlookup has some limitations, as you're starting to see. Another suggestion is Index/Match. Use this in your second table.

Note: I assume your top table is in Sheet1. Put this in your C2, in the second table (under the "Zero Rate 1M" header):

=Index(Sheet1!$C:$C,match(A2&B2,Sheet1!$A:$A&Sheet1!$B:$B,0)) and enter by pressing CTRL+SHIFT+ENTER.

Answer 2

There are a lot of ways to do it - if your data is sorted first by MarkAsofDate and then by Maturity date, the simplest method will be to add a helper column on your raw data tab - let's say column E. In column E, starting at E2 and copied down, type [Assuming MarkAsofDate is column A & MaturityDate is column E]:

=IF(MONTH(B1)-MONTH(A1)=1,A1,"")

This column will show the MarkAsOfDate for every item which has a MaturityDate 1 month after the MarkAsOfDate; for all other rows it will show "".

In your special data results tab, use the MATCH function to find the row in column E which matches your current row's MarkAsOfDate. We will also use the INDEX function to return the value from that row in column C. Assuming your first sheet with raw data is sheet1 and your special data results is on sheet2, type this into E2 on sheet2 and drag down:

=INDEX(Sheet1!C:C,MATCH(A2,Sheet1!A:A,0))

Another alternative (apart from BruceWayne's recommended Array Formula) would be to use the OFFSET function. OFFSET creates a new range based on a starting point, moving a number of cells to the right/left/up/down, for a given height and width. In this case, we will first use MATCH to find the first time that the MarkAsOfDate on Sheet1 matches Sheet2. We will use that info and the OFFSET function to create a new range which starts there, and ends at the bottom of your data, like so:

=OFFSET(Sheet1!A1,MATCH(A2,Sheet1!A:A,0),1,COUNT(Sheet1!C:C),2)

Then we just need to use VLOOKUP on the range we created above, like so:

=VLOOKUP(B2,OFFSET(Sheet1!A1,MATCH(A2,Sheet1!A:A,0),1,COUNT(Sheet1!C:C),2),0)

This second alternative avoids needing a helper column, but is more complex and could be prone to errors if your rows/columns change (because we had to hardcode a couple of things in the OFFSET function). Also, OFFSET is volatile, meaning it recalculates whenever any cell calculates, so it can slow down your workbook if you have a lot of rows of it. Based on that, I recommend you either use the helper column method above, or if you are comfortable with Array Formulas, using BruceWayne's answer.