coloradobstime
New Member
- Joined
- Dec 23, 2011
- Messages
- 3
Good Morning! I found this thread here (http://www.mrexcel.com/forum/excel-questions/785753-index-match-greater-than-date-criteria.html) which is closely related to my issue but cannot figure out how to remove the "third criteria" from the formula in order to make it work for my problem. I utilize a program that uses "Dated Constant" variables which means it will utilize the value of that variable until the next change. I need to take similar logic to this in a spreadsheet I have for post-analysis. I will ensure the data set I am referencing has the appropriate values prior to the period I am looking at. As an example, the Folly1 unit minimum will be 120 past 01/01/2016 until the next change that occurs on 04/01/2016 which will move it to 105 until the next change.... so between January 1, 2016 and March 31, 2016 at 11:59:59 it remains 120 at 04/01/2016 12:00am it changes to 105.
Spreadsheet:
Excel 2012
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
I am trying to:
Expected to: Find unit in data (column b) that matches unit in lookups (column g), then find the latest date (column a) less than or equal to date listed in lookups (column f) and return minimum capacity (column c) to column I.
Thanks for pointing me in the right direction!
Spreadsheet:
Excel 2012
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Data | Lookups | Expected return value: | |||||||||||||
2 | Date | Unit | Minimum Capacity | Date | Unit | Quantity | Minimum Capacity for Period Only | |||||||||
3 | 1/1/2016 | Cat | 0 | 1/1/2016 | Folly1 | 202 | 120 | |||||||||
4 | 1/1/2016 | Dog1 | 3 | 1/2/2016 | Folly1 | 700 | 120 | |||||||||
5 | 1/1/2016 | Dog2 | 3 | 4/1/2016 | Folly1 | 1201 | 105 | |||||||||
6 | 1/1/2016 | Dog3 | 3 | 4/2/2016 | Folly1 | 1201 | 105 | |||||||||
7 | 1/1/2016 | Elephant 5 | 28 | |||||||||||||
8 | 1/1/2016 | Elephant 6 | 45 | |||||||||||||
9 | 1/1/2016 | Elephant 6s | 45 | *Data will be sorted by Unit and then by Date | ||||||||||||
10 | 1/1/2016 | Elephant 7 | 80 | |||||||||||||
11 | 1/1/2016 | Elephant 7s | 80 | One match type is an exact match column b to column g. | ||||||||||||
12 | 1/1/2016 | Folly1 | 120 | |||||||||||||
13 | 4/1/2016 | Folly1 | 105 | Second match type is 1 - Less than (largest value less than or equal to) column a to column f | ||||||||||||
14 | 10/1/2016 | Folly1 | 120 | |||||||||||||
15 | 4/1/2017 | Folly1 | 118 | |||||||||||||
16 | 10/1/2017 | Folly1 | 120 | Expected to: Find unit in data (column b) that matches unit in lookups (column g), then find the latest date (column a) less than or equal to date listed in lookups (column f) and return minimum capacity (column c) to column I. | ||||||||||||
17 | 4/1/2018 | Folly1 | 105 | |||||||||||||
18 | 10/1/2018 | Folly1 | 120 | Have tried: | ||||||||||||
19 | 4/1/2019 | Folly1 | 105 | Works for exact date match: | ||||||||||||
20 | 10/1/2019 | Folly1 | 120 | 1/1/2016 | Folly1 | 202 | 120 | |||||||||
21 | 4/1/2020 | Folly1 | 105 | 4/1/2016 | Folly1 | 121 | 105 | |||||||||
22 | 10/1/2020 | Folly1 | 120 | 4/1/2017 | Folly1 | 700 | 118 | |||||||||
23 | 4/1/2021 | Folly1 | 105 | |||||||||||||
24 | 10/1/2021 | Folly1 | 120 | Does not work for between two dates…. | ||||||||||||
25 | 1/1/2016 | Folly2 | 200 | 1/2/2016 | Folly1 | 202 | #N/A | |||||||||
26 | ||||||||||||||||
27 | Reason: the match type is defined as type "0" for both… | |||||||||||||||
28 | =INDEX($A$1:$C$25,MATCH(1,($B$1:$B$25=G25)*($A$1:$A$25=F25),0),3) | |||||||||||||||
29 | ||||||||||||||||
30 | Seen this post: | |||||||||||||||
31 | http://www.mrexcel.com/forum/excel-questions/785753-index-match-greater-than-date-criteria.html | |||||||||||||||
32 | but cannot figure out how to remove third criteria… |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2
I am trying to:
Expected to: Find unit in data (column b) that matches unit in lookups (column g), then find the latest date (column a) less than or equal to date listed in lookups (column f) and return minimum capacity (column c) to column I.
Thanks for pointing me in the right direction!