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

 

[toongal12]

I'm confused because you are using A2 as the ref in your formula! Is A2 the 1st item in the list? Or a reference cell with the Item you want to find?

Please reference the 4th post on this page. I posted a long bit about the data

 

[toongal12]

I'm confused because you are using A2 as the ref in your formula! Is A2 the 1st item in the list? Or a reference cell with the Item you want to find?

A2 is Item-001, the item I want information on

 

[gaz_chops]

Try

=INDEX(C2:C7,MATCH(A2&MAX(IF(A2:A7=A2,IF(B2:B7<=G1,B2:B7))),A2:A7&B2:B7,0))

Enter as an array

Note:
Change these references
C2:C7 is Version
B2:B7 is Completed Date
G1 is Date to compare

[/FONT][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="class: xl64, width: 65, align: right"] 2 [/TD]
  [TD="width: 65"]Item-001[/TD]
  [TD="class: xl63, width: 65, align: right"]12/12/13[/TD]
 [/TR]
 [TR]
  [TD]Item-001[/TD]
  [TD="class: xl63, align: right"]01/09/16[/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/06/12[/TD]
  [TD="align: right"]2[/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"]3[/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"]4[/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"]5[/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="align: right"]6[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]
[FONT=Lucida Grande]

 

[toongal12]

Try

=INDEX(C2:C7,MATCH(A2&MAX(IF(A2:A7=A2,IF(B2:B7<=G1,B2:B7))),A2:A7&B2:B7,0))

Enter as an array

Note:
Change these references
C2:C7 is Version
B2:B7 is Completed Date
G1 is Date to compare

[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="class: xl64, width: 65, align: right"] 2[/TD]
[TD="width: 65"]Item-001[/TD]
[TD="class: xl63, width: 65, align: right"]12/12/13[/TD]
[/TR]
[TR]
[TD]Item-001[/TD]
[TD="class: xl63, align: right"]01/09/16[/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/06/12[/TD]
[TD="align: right"]2[/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"]3[/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"]4[/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"]5[/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="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I think this works!

I just want to make sure I understand how it's working

So the & creates a "string" or concatenation that combines the Item and Date as one entity but still keeping their values? Such as "Item-001 & 1/6/12"? So in the end, it matches the Item&Date, to make sure the correct item is grabbed?


Also, another question:

I have been using the * to combine qualities for comparison. Should I be making an additional IF instead?

Example:
I have been using
IF((ItemData[ItemName]=$A2)*(ItemData[CompletedDate]<=G1,ItemData[CompletedDate]))
which made the formula have issues.


If I want to compare another column (to make sure it is a valid type with data), will * bring incorrect results? Or use another IF?

Example:

IF((ItemData[ItemName]=$A2)*(ItemData[Type]<>""),IF(ItemData[CompletedDate]<=G1,ItemData[CompletedDate]))

Or should it be changed to:

IF((ItemData[ItemName]=$A2,IF(ItemData[Type]<>"",IF(ItemData[CompletedDate]<=G1,ItemData[CompletedDate]))

Thank you so much for helping me. This has been going on for weeks and I have tried so making different formulas.

 

[gaz_chops]

It does as you say, Concatenates the 2.

The * is the same as using and, the If statement will check if true then move to the next If etc.

Hah