Index Collect or Lookup Formula Help

ConnorForm

Good Afternoon All,

Currently having trouble with an index collect formula between 2 SmartSheets. On Sheet A there are 50 Unique ID's that correspond to 50 Creative Names on Sheet A. On Sheet B I am trying to bring those 50 unique Id's over by matching back to the 50 Creative Names on Sheet A. So my formula is as such: INDEX(COLLECT({CUID}, {AdName}, [Ad Name]@row, {Get Audience}, Audience@row), 1) where the CUID is the Unique Id and Ad Name is the Creative Name, Audience is another match back field as well. The problem I am facing is there are Creative Names and Audiences that are the same so the index collect is pulling duplicate Unique Id's and not all 50. My question is there a Collect or Lookup formula that will give me that 1:1 match? or will it always pull the topmost value if there are duplicates? In the picture below, the missing values represent the Unique Id's not pulled into Sheet B, the ones highlighted in red under Sheet B are the duplicate values. EX: The index collect formula in Sheet B pulled 3507 3 times when it should have pulled 3507,3508,3509 I think this is because the Creative Name and Audience are the same for 3507,3508,3509 in Sheet A.

Thank you for the time and effort to try and answer. It is greatly appreciated. Please let me know if I can provide additional information or context.

ConnorForm

I think I figured this out. I had to make a helper column to count the number of times that Creative Name appeared (so first,second,third occurence) I made that column in both Sheet A and B and added that to my Index Collect

So: =INDEX(COLLECT({CUID}, {AdName}, [Ad Name]@row, {Get Audience}, Audience@row, {Get Count}, [Count AD]@row), 1) with the "Get Count" & "Count AD@row" being the helper columns to get rid of the duplicate Unique ID's coming through

ConnorForm

I think I figured this out. I had to make a helper column to count the number of times that Creative Name appeared (so first,second,third occurence) I made that column in both Sheet A and B and added that to my Index Collect

So: =INDEX(COLLECT({CUID}, {AdName}, [Ad Name]@row, {Get Audience}, Audience@row, {Get Count}, [Count AD]@row), 1) with the "Get Count" & "Count AD@row" being the helper columns to get rid of the duplicate Unique ID's coming through