CODEWITHSUNDEEP
vb.netexcelexcel-2007vba
user2534898
user2534898

Reputation: 1

Lookup a value from a range of dates in excel

Please help.

We have two tables. A list of accounts and the other is a change log. I need to add a new column in table 1 where the value is the amount in table 2 for the correct account and correct validity period.

ex.

table 1:
account#   beginning period   ending period
1          January 1, 2012    January 31, 2012
2          January 12, 2012   February 12, 2012

table 2:
account #  amount    valid period beg      valid period end
1           10      january 1, 2009        december 5, 2010
1           20      december 6, 2010       june 1, 2011
1           30      june 2, 2011           december 1, 2012
2           13      january 15, 2011       december 15, 2011
2           20      december 16, 2011      february 20, 2012

Thanks.

Upvotes: 0

Views: 217

Answers (1)

Jirka Š.
Jirka Š.

Reputation: 3428

Although it is a bit complex requirement it could be done with built-in functions (although it can look a bit obscure :-) ). Specifically I mean function SUMIFS.

enter image description here

It has several parameters.

  1. The first one is an area with values to be summed. It is B8:B12 in this example.
  2. The second is an area whith values to be checked with some condition. It is A8:A12.
  3. The third is a criterion to be applied for area from second parameter. It is (inter alia) account #.

So the formula says: sum all values from rows in B8:B12 where account # (A8:A12) is equal to desired account # (e.g. A3).

Ok, it is not all, you need specify the time range. It would be a bit clunky because you must check if two time period are overlapping (it would be easier to check if one date is in specified period). But it could be done because SUMIFS can take another pairs of criteria range and criterion. But it cannot be used for OR condition, so you had to combine more SUMIFS.

Nice article about overlapping ranges is e.g. http://chandoo.org/wp/2010/06/01/date-overlap-formulas/

BTW: you have to format cells in B2:C3 and C8:D12 as a date to be able to compare them.

Upvotes: 2

Related Questions