Array Formula

[Msdtitus]

Good Afternoon

I have an Array formula that I am using. The problem is that, while it accurately displays the name of the item I'm looking for, it does not removing duplicate values. I would like it to display a list of projects allocated to Each employee but also which Regions the project falls within. Below is an example as well as the formula being used

Formula:=(INDEX($D$2:$D$10,SMALL(IF(($E$2=$B$2:$B$10),MATCH(ROW($B$2:$B$10),ROW($B$2:$B$10)),""),ROWS($A$2:A2))))

	Employee	Project	Area	E	Formula
1	Faghmi	Social Media SOP's	Bellville, South Africa	Faghmi	Bellville, South Africa
2	Faghmi	DA CCD Review	Mitchells Plain	Faghmi	Mitchells Plain
3	Faghmi	BoD Annual Review	Bellville, South Africa	Faghmi	Bellville, South Africa
4	Faghmi	Loans Fraud	Cape Town	Faghmi	Cape Town
5	Faghmi	Verifications	Grassy Park	Faghmi	Grassy Park
6	Faghmi	Documents use in Validation of POR	Diep River		Diep River
7	Faghmi	KYC Risk Insight	Grassy Park		Grassy Park
8	Faghmi	ITO Application Support Processes	Lotus River		Lotus River
9	Faghmi	IT Procurement	Kuils River		Kuils River

Any help will be appreciated.

PS. I have tried the Unique formula but not sure if im using it correctly

 

[Fluff]

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

 

[Msdtitus]

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

Office 365 for enterprise

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F
1		Employee	Project	Area	E	Formula
2	1	Faghmi	Social Media SOP's	Bellville, South Africa	Faghmi	Bellville, South Africa
3	2	Faghmi	DA CCD Review	Mitchells Plain		Mitchells Plain
4	3	Faghmi	BoD Annual Review	Bellville, South Africa		Cape Town
5	4	Faghmi	Loans Fraud	Cape Town		Grassy Park
6	5	Faghmi	Verifications	Grassy Park		Diep River
7	6	Faghmi	Documents use in Validation of POR	Diep River		Lotus River
8	7	Faghmi	KYC Risk Insight	Grassy Park		Kuils River
9	8	Faghmi	ITO Application Support Processes	Lotus River
10	9	Faghmi	IT Procurement	Kuils River
11
Data

Cell Formulas
Range	Formula
F2:F8	F2	=UNIQUE(FILTER(D2:D100,B2:B100=E2))
Dynamic array formulas.

 

[Msdtitus]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F
1		Employee	Project	Area	E	Formula
2	1	Faghmi	Social Media SOP's	Bellville, South Africa	Faghmi	Bellville, South Africa
3	2	Faghmi	DA CCD Review	Mitchells Plain		Mitchells Plain
4	3	Faghmi	BoD Annual Review	Bellville, South Africa		Cape Town
5	4	Faghmi	Loans Fraud	Cape Town		Grassy Park
6	5	Faghmi	Verifications	Grassy Park		Diep River
7	6	Faghmi	Documents use in Validation of POR	Diep River		Lotus River
8	7	Faghmi	KYC Risk Insight	Grassy Park		Kuils River
9	8	Faghmi	ITO Application Support Processes	Lotus River
10	9	Faghmi	IT Procurement	Kuils River
11
Data

Cell Formulas
Range	Formula
F2:F8	F2	=UNIQUE(FILTER(D2:D100,B2:B100=E2))
Dynamic array formulas.

Thanks. This works great! when i try and appy the formula with data thats sitting on another sheet over a bigger range (e.g D2:D1000) then it kicks out an error. any way I can avoid that?

 

[Fluff]

What error are you getting & what is the formula?