# Unique identifier in an array

#### toongal12

##### Board Regular
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))}

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### toongal12

##### Board Regular

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

##### Well-known Member
Try

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

#### toongal12

##### Board Regular
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

##### Well-known Member
Did you enter as an array, Ctrl Shift & Enter

Last edited:

#### toongal12

##### Board Regular
Did you enter as an array, Ctrl Shift & Enter
Yes. Entered as array, gives #VALUE

#### toongal12

##### Board Regular
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

##### Well-known Member
It appears to work for me!

Code:
``````[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

##### Board Regular
It appears to work for me!

Code:
``````[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.

Replies
3
Views
400
Replies
3
Views
213
Replies
2
Views
127
Replies
0
Views
158
Replies
4
Views
664

1,195,990
Messages
6,012,727
Members
441,723
Latest member
iansitorus

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back