I have a huge table of data. The most important information is Item, Date, Number One, and Number Two. The data is not in order and is constantly being sorted. Right now, I can produce a table that allows the user to pick any random date and see what set of numbers are associated with all the items on the list, and the date these numbers occurred. You can call this the "current" set of information.
I need to be able to produce another table of what the "next" set of numbers and date for each item will be. The set of numbers only increase, and it is never by the same amount.
Right now for the "current" set of information I am using
A2 = Item1
Q3 = Date the user can input to see what the "current" version is at the time
Example: Produce Number One for Item1
=IFERROR(INDEX(C2:C12,MATCH(A2&MAX(IF((A2:A12=A2),IF(B2:B12<=Q3,B2:B12))),A2:A12&B2:B12,0)),0)
This connects the Item and Date together to find Number One and displays that current one. The index changes for Date, Number One and Number Two.
Example of original data. Data is never in order, it is always being sorted.
Item-----Date-------Number One---Number Two
Item1----1/1/14--------2------------1
Item1----1/1/15--------2------------3
Item1----1/1/16--------4------------7
Item1----1/1/17--------6------------11
Item2----2/1/15--------2------------3
Item2----2/1/16--------2------------5
Item2----2/1/17--------2------------7
Item2----2/1/18--------8------------9
Item3----3/1/16--------2------------1
Item3----3/1/17--------2------------3
Item3----3/1/18--------2------------9
Example of "current" data to use. User selects date as 4/1/16. My formula will produce the current information.
Item-----Date-------Number One---Number Two
Item1----1/1/16--------4------------7
Item2----2/1/16--------2------------5
Item3----3/1/16--------2------------1
I need formulas to create the "next" sets in line. There would be a formula for Date, Number One, and Number Two. So the next table should produce
Item-----Date-------Number One---Number Two
Item1----1/1/17--------6------------11
Item2----2/1/17--------2------------7
Item3----3/1/17--------2------------3
I am having trouble finding a formula that finds the next set of numbers since the numbers are not evenly increased. And with the data always being sorted, I have to be careful with an INDEX-MATCH and cannot use row functions.
This is a continuation of another thread I made. I added more information throughout the thread so I made a new one to put it all in one post. One user suggested a formula, but it only works if the numbers increase by the same amount.
https://www.mrexcel.com/forum/excel-...t-numbers.html
Bookmarks