Removing Duplicates When Using XLOOKUPs, IF, and TEXTJOIN

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 w...

excel

Formulas and Functions

office 365

Detlef_Lewin

Apr 17, 2024

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

Detlef_Lewin

Silver Contributor

Apr 17, 2024

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

renee_crozier

Copper Contributor

Apr 17, 2024

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

Forum Discussion

Removing Duplicates When Using XLOOKUPs, IF, and TEXTJOIN

Resources