Index/Match formula to return Contract# and Start date in same cell

[fawlty128]

My goal is to have a formula return the contract# and start date in instances where the same model# is included on two Different contracts.

Currently the formula below brings in the other contract# as well as the other start date. Unfortunately, the date is not formatted as a date and the contract isn't necessarily a different contract .
I've tried including Text(mm/dd/yy) but I must be putting it in the wrong place in the formula. I have no idea how to make the formula only look for instances where a different contract is involved.

Thanks in advance for any assistance anyone can provide.

The model# is in column Q, the contract# is in column Z and the start date is in column V

=INDEX(Z31660:Z37000,MATCH(Q31659,Q31660:Q370000,0))&" - "&INDEX(V31660:V37000,MATCH(Q31659,Q31660:Q370000,0))

 

[awoohaw]

try this:

mr excel questions 22.xlsm
	Q					V				Z	AA
2	Model					Start Date				Contract Number	ContractNmb & StartDate
3	D					2023-03-01				AB24	AB24-20230301
4	D					2023-03-04				AB26	AB26-20230304
5	E					2023-04-03				AM05
6	F					2023-04-05				AZ06
Sheet16

Cell Formulas
Range	Formula
AA3:AA6	AA3	=IF(COUNTIF($Q$3:$Q$6,$Q3)>1,Z3&"-"&TEXT(V3,"yyyymmdd"),"")

 

[fawlty128]

Thank you so much, the formula works. The only issue is that I need the formula result to be the other contract# & start date instance of the model# involved.

 

[awoohaw]

Thank you so much, the formula works. The only issue is that I need the formula result to be the other contract# & start date instance of the model# involved.

Is that for both or just the most recent one? And what do you want when there is a 3rd duplicate?

 

[fawlty128]

Is that for both or just the most recent one? And what do you want when there is a 3rd duplicate?

I would need it for both. Fortunately, it is rare that a product moves from one contract to another active contract, so it is highly unlikely there would be a 3rd duplicate on 3 different active contracts.

 

[awoohaw]

okay, this is clunky! So, it may or may not work, but it does in this small scenario:

mr excel questions 22.xlsm
	A																Q					V				Z	AA	AB
3																	D					2023-03-01				AB24	AB24-20230301	AB26-20230304
4																	D					2023-03-04				AB26	AB26-20230304	AB24-20230301
5																	E					2023-04-03				AM05
6																	F					2023-04-05				AZ06
fawlty128

Cell Formulas
Range	Formula
AB3:AB4	AB3	=LET(thismodel,Q3,thiscontract,Z3,models,$Q$3:$Q$6,startds,$V$3:$V$6,contract,$Z$3:$Z$6,orders,$Q$3:$Z$6, duplicates,CHOOSECOLS(FILTER(orders,models=thismodel,""),1,6,10), notthiscontract, FILTER(CHOOSECOLS(duplicates,3),CHOOSECOLS(duplicates,3)<> thiscontract,""), notthisdate, TEXT(FILTER(CHOOSECOLS(duplicates,2),CHOOSECOLS(duplicates,3)<> thiscontract,""),"yyyymmdd"), notthiscontract & "-" & notthisdate)
AA3:AA6	AA3	=IF(COUNTIF($Q$3:$Q$6,$Q3)>1,Z3&"-"&TEXT(V3,"yyyymmdd"),"")

 

[fawlty128]

okay, this is clunky! So, it may or may not work, but it does in this small scenario:

mr excel questions 22.xlsm
	A																Q					V				Z	AA	AB
3																	D					2023-03-01				AB24	AB24-20230301	AB26-20230304
4																	D					2023-03-04				AB26	AB26-20230304	AB24-20230301
5																	E					2023-04-03				AM05
6																	F					2023-04-05				AZ06
fawlty128

Cell Formulas
Range	Formula
AB3:AB4	AB3	=LET(thismodel,Q3,thiscontract,Z3,models,$Q$3:$Q$6,startds,$V$3:$V$6,contract,$Z$3:$Z$6,orders,$Q$3:$Z$6, duplicates,CHOOSECOLS(FILTER(orders,models=thismodel,""),1,6,10), notthiscontract, FILTER(CHOOSECOLS(duplicates,3),CHOOSECOLS(duplicates,3)<> thiscontract,""), notthisdate, TEXT(FILTER(CHOOSECOLS(duplicates,2),CHOOSECOLS(duplicates,3)<> thiscontract,""),"yyyymmdd"), notthiscontract & "-" & notthisdate)
AA3:AA6	AA3	=IF(COUNTIF($Q$3:$Q$6,$Q3)>1,Z3&"-"&TEXT(V3,"yyyymmdd"),"")

Wow, that is a hell of formula, Thank you so much it actually works. The only thing I completely forgot about (until I looked at some of the #SPILL! results) is that Column AA indicates if the contract is; Valid, Expired, or Future. I am only concerned with Valid contracts. The other contracts should be ignored. I do Not need to capture instances where an item moves from an Expired contract to a new Valid contract. What would need to be added to the formula so that it only considers 'Valid' contracts in the calculations?
Again, thank you so much for all your help, it is very much appreciated.

 

[awoohaw]

I don't know. the way columns are dropped and chosen is really fickle to put new changes in as part of the filter criteria. Not saying it is impossible, but I think I would need to pretty much start over, and use the results so far as some kind of template. I'm not able to look at it in much detail for the next few days.