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

 

[gaz_chops]

No, 12th Aug 2017 is before 1st Oct 2017! I'm using UK date format

Suggest you try it entered as an array, then if it doesn't work as you want post sample showing answer you expect.

 

[toongal12]

No, 12th Aug 2017 is before 1st Oct 2017! I'm using UK date format

Suggest you try it entered as an array, then if it doesn't work as you want post sample showing answer you expect.

Can you post a picture of your excel using the formula. I did enter it as an array and it still gives #VALUE.

 

[gaz_chops]

Post your sample, showing the formula. I can't post a pic. the formula is exactly as i posted earlier.

 

[toongal12]

No, 12th Aug 2017 is before 1st Oct 2017! I'm using UK date format

Suggest you try it entered as an array, then if it doesn't work as you want post sample showing answer you expect.

My data comes from a pivot table.

Entered as an ARRAY.

{=INDEX(ItemData[Version],MATCH(RIGHT($A2,3)+LARGE(IF((ItemData[ItemName]=$A2)*(ItemData[CompletedDate]<=$D$1),ItemData[CompletedDate]),1),ItemData[CompletedDate]+RIGHT(ItemData[ItemName],3),0))}

A2=item I want
D1=date that is being compared


It creates an identifier by combing the Item number and date to one number. The problem is another item and date can equal the same number. So it won't know which one to pick.

Item---------CompletedDate------Version
Item-001----8/2/16----------------1
Item-001----9/1/17----------------2
Item-001----10/1/18---------------3
Item-002----8/1/16----------------2
Item-002----9/2/17----------------3



If I try to use my formula for Item-001 for 8/3/16, depending on how the data is sorted, it can choose either Version 1 or 2 because Item-001 and 002 have the same total date/number combination.

I need possibly a string for it to connect Item-001 to the date, instead of combination.

 

[toongal12]

Post your sample, showing the formula. I can't post a pic. the formula is exactly as i posted earlier.

{=INDEX(ItemData[Version],MATCH(1,IF(RIGHT($A2,3),IF(ItemData[CompletedDate]<=$D$1,1))))}

Returns #VALUE

 

[gaz_chops]

You have missed the bit that matches the Item No.!

MATCH(1,IF(RIGHT($A2,3)=??,

If this is stored in a cell then use the ref, if not enter as text e.g. "002"

 

[toongal12]

You have missed the bit that matches the Item No.!

MATCH(1,IF(RIGHT($A2,3)=??,

If this is stored in a cell then use the ref, if not enter as text e.g. "002"

What is ref? Items in column have to be format "Item-001". There are many items so I cannot make special cases.

{=INDEX(ItemData[Version],MATCH(1,IF(RIGHT($A2,3)=$A2,IF(ItemData[CompletedDate]<=$D$1,1))))}

Now returns #N/A

 

[gaz_chops]

I'm confused now!

Your original post implied you want to return the "latest" Version number for a specific Item, is this not the case?

 

[toongal12]

I'm confused now!

Your original post implied you want to return the "latest" Version number for a specific Item, is this not the case?

You said "If this is stored in a cell then use the ref, if not enter as text e.g. "002" <--- I'm assuming the "002" is to be manually typed for RIGHT($A2,3)="002", which I cannot do. It needs to be all done automatically, which is why I put RIGHT($A2,3)=$A2



Yes, I want the latest version compared to a specific date. I copied your formula, but it did not work. What did I do wrong?

{=INDEX(ItemData[Version],MATCH(1,IF(RIGHT($A2,3)=$A2,IF(ItemData[CompletedDate]<=$D$1,1))))}

 

[gaz_chops]

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?