I need to create a formula that if compared with a date, it can tell me the most recent version of an item. So if I insert 8/12/16 for Item-001, it will return 2.
The dates and items are in no order
<tbody>
</tbody>
My array is trying to output Version. Some items have the same date, so I make it match the last three digits of the Item to the date to create a "unique" identifier.
My problem is the identifier can sometimes match another item number and date combination, so now it doesn't work. I wasn't sure how to change it.
={INDEX(C2:C7,MATCH(RIGHT(A2,3)+LARGE(IF((A2:A7=A2)*(B2:B7<=C9),B2:B7),1),B2:B7+RIGHT(A2:A7,3),0))}
The dates and items are in no order
Item | Completed Date | Version |
Item-001 | 9/1/16 | 3 |
Item-001 | 6/1/12 | 1 |
Item-001 | 7/1/14 | 2 |
Item-002 | 10/1/17 | 1 |
Item-002 | 11/1/18 | 1 |
Item-002 | 9/1/16 | (cell is blank) |
Date comparison | 8/12/16 |
<tbody>
</tbody>
My array is trying to output Version. Some items have the same date, so I make it match the last three digits of the Item to the date to create a "unique" identifier.
My problem is the identifier can sometimes match another item number and date combination, so now it doesn't work. I wasn't sure how to change it.
={INDEX(C2:C7,MATCH(RIGHT(A2,3)+LARGE(IF((A2:A7=A2)*(B2:B7<=C9),B2:B7),1),B2:B7+RIGHT(A2:A7,3),0))}