Unique identifier in an array

[toongal12]

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

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))}

 

[AliGW]

I don't understand wy 2 is the latest version in your example - please elaborate.

 

[toongal12]

I don't understand wy 2 is the latest version in your example - please elaborate.

So I want to know what latest version of Item-001 was out on 8/12/16

Item-001 has 3 dates: 9/1/16, 6/1/12, 7/1/14.

If compared to 8/12/16, the only date that has occured, that is the most recent is 7/1/14 (which has Version 2). 9/1/16 has not happened yet; therefore, not current version.

 

[gaz_chops]

Try

=INDEX(C2:C7,MATCH(1,IF(RIGHT(A2:A7,3)=A2,IF(B2:B7<=C9,1))))
Enter as an array

 

[toongal12]

Try

=INDEX(C2:C7,MATCH(1,IF(RIGHT(A2:A7,3)=A2,IF(B2:B7<=C9,1))))
Enter as an array

Returns with #VALUE

 

[gaz_chops]

Did you enter as an array, Ctrl Shift & Enter

 

[toongal12]

Did you enter as an array, Ctrl Shift & Enter

Yes. Entered as array, gives #VALUE

 

[toongal12]

Did you enter as an array, Ctrl Shift & Enter

It also needs the most current version, so wouldn't MAX or LARGE be necessary? Otherwise, this will just pick the first one it finds. I need the most recent version.

 

[gaz_chops]

It appears to work for me!

[TABLE="width: 455"]
<!--StartFragment--> <colgroup><col width="65" span="7" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="width: 65"]Item[/TD]
  [TD="width: 65"]Completed Date[/TD]
  [TD="width: 65"]Version[/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"](cell is blank)[/TD]
  [TD="width: 65"]002[/TD]
  [TD="class: xl63, width: 65, align: right"]12/08/17[/TD]
 [/TR]
 [TR]
  [TD]Item-001[/TD]
  [TD="class: xl63, align: right"]01/09/16[/TD]
  [TD="align: right"]3[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]Item-001[/TD]
  [TD="class: xl63, align: right"]01/06/12[/TD]
  [TD="align: right"]1[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]Item-001[/TD]
  [TD="class: xl63, align: right"]01/07/14[/TD]
  [TD="align: right"]2[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]Item-002[/TD]
  [TD="class: xl63, align: right"]01/10/17[/TD]
  [TD="align: right"]1[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]Item-002[/TD]
  [TD="class: xl63, align: right"]01/11/18[/TD]
  [TD="align: right"]1[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]Item-002[/TD]
  [TD="class: xl63, align: right"]01/09/16[/TD]
  [TD="colspan: 2"](cell is blank)[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]

This part "B2:B7<=C9" will find the last value on or before the specified date.

 

[toongal12]

It appears to work for me!

[TABLE="width: 455"]
<tbody>[TR]
[TD="width: 65"]Item[/TD]
[TD="width: 65"]Completed Date[/TD]
[TD="width: 65"]Version[/TD]
[TD="width: 65"][/TD]
[TD="width: 65"](cell is blank)[/TD]
[TD="width: 65"]002[/TD]
[TD="class: xl63, width: 65, align: right"]12/08/17[/TD]
[/TR]
[TR]
[TD]Item-001[/TD]
[TD="class: xl63, align: right"]01/09/16[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item-001[/TD]
[TD="class: xl63, align: right"]01/06/12[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item-001[/TD]
[TD="class: xl63, align: right"]01/07/14[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item-002[/TD]
[TD="class: xl63, align: right"]01/10/17[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item-002[/TD]
[TD="class: xl63, align: right"]01/11/18[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item-002[/TD]
[TD="class: xl63, align: right"]01/09/16[/TD]
[TD="colspan: 2"](cell is blank)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This part "B2:B7<=C9" will find the last value on or before the specified date.

I don't think it works?

If you enter 12/8/17 as the date, the answer for Item-002 should be 1, not (cell is blank). If 12/8/17 is the check date, the most current version is 1/10/17, not 1/9/16


Also be aware the data can be sorted and the order will be constantly changing.