VLOOKUP Multiple Criteria Including Date Range

Question

chippy138 Author

Level 1

0 points

VLOOKUP Multiple Criteria Including Date Range

I've got a table of values that contains titles of payments, the dates they are valid between and the amount they are.

I am attempting to find the amount the payment is for a particular month in a separate table.

Examples of the two tables are shown below.

Table 1

Name	Date from	Date to	Amount
Gym Membership	06/07/2013	06/06/2014	£50
Gym Membership	06/07/2014	06/06/2015	£60
Insurance	01/08/2013	01/07/2014	£100
Insurance	01/08/2014	01/07/2015	£110

Table 2

Month	Insurance	Gym Membership
June 2014
July 2014
August 2014

Now I am attempting a lookup that, in "Table 2" cell "B2", will find, in "Table 1" the Payment that has the matching name to the column title ("B1"), and the month in Table 2 is valid within the Payment Dates. So "Table 1" column "A" must match the value in "Table 2" "B1" and the date in "Table 2" "A2" must be between the dates in "Table 1" column "B" and "Table 1" column "C".

With the formulas working correctly, the values in Table 2 should show as:

Table 2

Month	Insurance	Gym Membership
June 2014	£100	£50
July 2014	£100	£60
August 2014	£110	£60

I assume I would require a VLOOKUP to find the Payment Name in Table 1, but then some additional criteria to check the dates.

MacBook Pro (13-inch Mid 2012), OS X Mavericks (10.9.4), Numbers for Mac

Posted on Sep 2, 2014 8:07 AM

Reply

Answer 1

SGIII

Level 8

38,214 points

Sep 2, 2014 11:39 AM in response to chippy138

Here is one way to do this:

Table 2's "lookup" formula (the INDEX MATCH combination is a flexible alternative to VLOOKUP) in B2, copied to column C and down, is:

=INDEX(Table 1::$D,MATCH(B$1&$D2,Table 1::$E,1))

The values in the YRMM index columns, which can be hidden after you've set everything up, are calculated as follows:

In Table 1: =A2&YEAR(B2)&RIGHT("00"&MONTH(B2),2)

In Table 2: =YEAR(A2)&RIGHT("00"&MONTH(A2),2)

SG

Reply

Answer 2

chippy138 Author

Level 1

0 points

Sep 4, 2014 5:03 AM in response to SGIII

One problem with this is that, if a date range in Table 1 is expired and that expense no longer exists, Table 2 continues to pick up the last price of that.

It needs to check if the current month in Table 2 falls between the two date ranges in Table 1.

JP

Reply

Answer 3

SGIII

Level 8

38,214 points

Sep 4, 2014 7:32 AM in response to chippy138

Yes, I see why you mean. I think your best bet may be to either: 1) archive expired rows by moving them to a separate table, or 2) add rows with 0 values to mark the end of periods, e.g. if you quit the gym after 2015-06-06 make a Gym Membership entry for 2015-07-01 with 0 so the table will show 0 for the month of July 2015 and thereafter.

SG

Reply