Unique + Filter?

[OvernightCellebrity]

I would like to pull all unique values from collumn D:D based on one criteria.

Criteria
Portfolio Name in (A2)
Collumn of portfolio names: AA

I want to avoid using an array formula is possible, whatis the simplest way to achieve this i have tried the below:

=UNIQUE(FILTER(D:D, AA:AA=A2))

 

[Fluff]

Hi & welcome to MrExcel.

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what is wrong with the formula you posted?

 

[OvernightCellebrity]

Thanks Fluff - have uopdated accordingly thank you

For the avoidance of doubt i am using windows version of office 365

 

[Fluff]

Thanks for that & what is wrong with your formula?

 

[OvernightCellebrity]

Simply returns #NA

Validating the criteria match, and confirming the absence of spaces through LEN, ensures that A2 successfully matches a value in AA, with no spaces present within the value.

 

[Fluff]

Do you have any #N/A errors in columns D or AA?

 

[OvernightCellebrity]

Assuming the date looks like the below:

I would like the formula to a return the values that have the portfolio name Chief:

OGF
Stanley Cooper

Portfolio	Issuer_Name	Portfolio Names
Chief	OGF	Chief
	PPF	Big Token
	DOCMARTIN	Chief ii
	OGF	Chief
	PPF	Big Token
	DOCMARTIN	Chief ii
	Stanley Cooper	Chief
	PPF	Big Token
	SUSHIDOG	Salmon 4
	PORTER 4	Salmon 5
	PORTER 5	Gatehouse
	PORTER 6	Salmon 7
	PORTER 7	Salmon 8

 

[OvernightCellebrity]

Do you have any #N/A errors in columns D or AA?

there are N/A values in collumn AA

 

[RoryA]

In that case, try:

=UNIQUE(FILTER(D:D, IFERROR(AA:AA=A2,FALSE)))

 

[OvernightCellebrity]

In that case, try:

=UNIQUE(FILTER(D:D, IFERROR(AA:AA=A2,FALSE)))

Thank you all for the help - this is my solution

how can i expan the formula to add another condition based on a value in B2 in collumn BB:BB?