Removing Duplicates When Using XLOOKUPs, IF, and TEXTJOIN

[renee_crozier]

I am trying to compile a list of people from a different sheet named "Asset" that are associated with a certain asset in Excel (Office 365). Using this formula, I want to return a name if the asset was modified less than 6 months ago, return a name if the asset was created less than 6 months ago, and then the owner of the asset. Since these can be the same person, I am looking to reduce the multiple names that are returned.

=TEXTJOIN("; ", TRUE, IF(XLOOKUP(E15, Asset_Path, Asset_ModifiedDate)>TODAY()-184, XLOOKUP(E15, Asset_Path, Asset_ModifiedBy), "Mod > 6 months"), IF(XLOOKUP(E15, Asset_Path, Asset_CreatedDate)>TODAY()-184, XLOOKUP(E15, Asset_Path, Asset_CreatedBy), "Created > 6 months"), XLOOKUP(E15, Asset_Path, Asset_Owner))

Asset_Path, Asset_ModifiedDate, Asset_ModifiedBy, Asset_CreatedDate, Asset_CreatedBy, Asset_Owner are all named ranges.

Sample source data I'm working with:

Path	ContentOwner	Created	CreatedBy	ExpirationDate	LinkURL	Modified	ModifiedBy	Title
/sites/us/Assets0001/cat-in-the-hat.pdf	Jill Denvers	9/3/2021	Dan Smith	11/28/2023	https://website.com/sites/us/Assets0001/cat-in-the-hat.pdf	11/28/2023	Alex White	Cat in the Hat
/sites/us/Assets0001/the-hobbit.pdf	John Thomas	12/11/2024	Jessica Samson	2/16/2024	https://website.com/sites/us/Assets0001/the-hobbit.pdf	1/16/2024	Greg Jones	The Hobbit
/sites/us/Assets0001/the-art-of-war.pdf	John Doe	4/15/2022	Rachel Richards	4/15/2025	https://website.com/sites/us/Assets0001/the-art-of-war.pdf	3/27/2024	John Doe	The Art of War

Current result with the above formula:

LinkURL	Title	Asset Contributor
/sites/us/Assets0001/the-art-of-war.pdf	The Art of War	John Doe; Created > 6 months; John Doe

Result I am looking to achieve:

LinkURL	Title	Asset Contributor
/sites/us/Assets0001/the-art-of-war.pdf	The Art of War	John Doe; Created > 6 months;

I've tried removing the TEXTJOIN and adding &"; "& between each IF statement, using CONCAT, and adding UNIQUE before TEXTJOIN but the duplicate names kept appearing.

[#2 · Accepted solution]

@renee_crozier 

Try this.

=TEXTJOIN("; ",TRUE,
UNIQUE(VSTACK(
IF(XLOOKUP(A15,Tabelle1[Path],Tabelle1[Modified])>TODAY()-184,XLOOKUP(A15,Tabelle1[Path],Tabelle1[ModifiedBy]),"Mod > 6 months"),
IF(XLOOKUP(A15,Tabelle1[Path],Tabelle1[Created])>TODAY()-184,XLOOKUP(A15,Tabelle1[Path],Tabelle1[CreatedBy]),"Created > 6 months"),
XLOOKUP(A15,Tabelle1[Path],Tabelle1[ContentOwner]))))

[#3]

Thanks! That worked! I didn't know about the VSTACK function.