Having trouble with an Index Collect with multiple matches?

ConnorForm

Hello all, please let me know if this makes sense what I am trying to accomplish as I have been stuck on trying to get a fix for this. I am working to create a sheet that looks at an export out of a dsp and checks to see if those are elements match with the information in our smartsheet - kind of like a QA sheet to do monthly swaps to make sure what we enter in this dsp in correct. Lets call the QA Sheet, Sheet A and the source of truth our creative sheet, Sheet B.

There are multiple columns in sheet A pulling out elements from the SDF export that is pasted into 4 columns in sheet A, the Smartsheet pulls out a Dealer ID, a tactic, and a model from

The problem I am facing is getting the "SS Model" Column to show the correct model from our Source Sheet B. However the issue is in Sheet A the Dealer ID and Model from the SDF is referenced at least 3x in sheet A whereas in Sheet B, it is only referenced once for a dealer, tactic and model. The Model type for each dealer in this sheet changes monthly, and also dealers can add or decrease the number of models there are running.

With this example. Dealer ID's 42205 and 42103 were running two carlines in Sept, CX-50 being Model 1 and CX-5 as model 2. In Oct, these two dealers are switching to CX-30 Model 1 and CX90- Model 2. In Sheet B however my current formula is only pulling in CX-30 Model 1 for for both of those dealers in the SS Model column when it needs to pull in both CX-30 and CX-90. As you can see in Sheet B from Name Column D, in Sept, 42103 is referenced 6 times, 3 for CX-50 and 3 for CX-5 and 42205 is ref 12 times, 6 for CX-50 and 6 for CX-5. How can I get my (Index/Join)(Collect/Match) grab the correct Models. Below my SS Model is pulling all CX-30 because it is matching on the first match of CX-30 from Sheet B. Whereas the Dealer Tactics that had Model CX-5 should now say cx-90 for that is their second model and the dealer tactics that had Model CX-50 should read CX-30 for Model 1. More Context - A dealer can range from 1-4 Carlines and can change their model type of amount of models. 1 Row in the Creative Sheet (Sheet B) is referenced 3-6 times for each Ad Group Set in the QA Sheet (Sheet A)

Please let me know if additional context/information is needed or if more explanation on a call of some sort would work better to make more sense.

My Current Formula: Is looking at Creative Name parsed together from the SDF elements and trying to match based on a similar formula set up in Sheet B, if that is an error fails the sheet is utilizing these Dealer X Columns and references to check a master Dealer Document that shows the Dealers Models 1-4 (What they are running and how many they are running) - Sheet C

=IF([SDF Length]@row = "6s", [Status (Column E)]@row, IF(CONTAINS("TEST", [Name (Column D)]@row), [Status (Column E)]@row, IFERROR(INDEX(COLLECT({Model}, {creative Parsed}, [SDF Creative Build Parsed]@row), 1), IFERROR(INDEX(COLLECT({Model}, {Creative Parsed + UID Model}, [Dealer X Model 1]@row), 1), IFERROR(INDEX(COLLECT({Model}, {Creative Parsed + UID Model}, [Dealer X Model 2]@row), 1), IFERROR(INDEX(COLLECT({Model}, {Creative Parsed + UID Model}, [Dealer X Model 3]@row), 1), IFERROR(INDEX(COLLECT({Model}, {Creative Parsed + UID Model}, [Dealer X Model 4]@row), 1), "Not in YT Creative")))))))

I think my SS Model column from sheet A is pulling CX-30 for everything because the index collect is just matching to the CX-30 model 1 and not accoutning for Model 2 CX-90

Again apologies for being so long winded, and confusing but just have reached a stuck point here so any tips, tricks, or fixes are greatly appreciated!! Please let me know if there is any more info I can give

NicoLHC

@ConnorForm its a pleasure to meet you. Nice challenge!

ConnorForm

@NicoLHC quite the challenge indeed!!