Filter Formula to exclude everything except one value

[hananak]

Dear All,

I am trying to do do a lookup based on two criteria, if the criteria matches, then it should not give all the results but the result I want. I tried various formulas on internet on Xlookup but not working and then I came across Filter function which is close to what I want but still not able to get the desired outcome.

Please see the attached picture, to see if that is possible.

Your expert advice will be appreciated.

 

[hananak]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3	Cost Code	Employee	Allowances	Column 4		Cost Code
4	1245	11068246	Allowance 1	Something		1297			Employee	Allowances
5	1245	11068246	Allowance 2	Something					11068246	Allowance 4
6	1245	11068246	Allowance 3	Something		Employee No			11068246	Allowance 3
7	1245	11068246	Allowance 4	Something		11068246
8	1297	10903301	Allowance 1	Something
9	1297	10903301	Allowance 2	Something		not found
10	1297	10903301	Allowance 3	Something
11	1297	10903301	Allowance 4	Something
12	1297	10903301	Allowance 5	Something
13	1297	10903301	Allowance 6	Something		Employee No
14	1297	10903301	Allowance 7	Something		10903301
15	1297	10903301	Allowance 8	Something
16						Allowance 1
17						Allowance 2
18						Allowance 3
19						Allowance 4
20						Allowance 5
21						Allowance 6
22						Allowance 7
23						Allowance 8
Sheet2

Cell Formulas
Range	Formula
F9,F16:F23	F9	=LET(u,UNIQUE(VSTACK(Table1[[Cost Code]:[Allowances]],HSTACK(EXPAND("",ROWS(Exclude),,""),Exclude)),,1), FILTER(CHOOSECOLS(u,3),(CHOOSECOLS(u,2)=F7)*(CHOOSECOLS(u,1)=$F$4),"not found"))

The formula is not excluding the allowances, despite populating the exclusion list (column I and J), rest is working.

please see the picture.

 

[hananak]

Sorry, the formula is now giving totally opposite. instead of giving me everything, it now gives what is being added to the Exclusion list.

 

[hananak]

Ignore post#19 it doesn't consider the Exclude list and this is shorter.

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3	Cost Code	Employee	Allowances	Column 4		Cost Code
4	1245	11068246	Allowance 1	Something		1245			Employee	Allowances
5	1245	11068246	Allowance 2	Something					11068246	Allowance 4
6	1245	11068246	Allowance 3	Something		Employee No			11068246	Allowance 3
7	1245	11068246	Allowance 4	Something		11068246
8	1297	10903301	Allowance 1	Something
9	1297	10903301	Allowance 2	Something		Allowance 1
10	1297	10903301	Allowance 3	Something		Allowance 2
11	1297	10903301	Allowance 4	Something
12	1297	10903301	Allowance 5	Something
13	1297	10903301	Allowance 6	Something		Employee No
14	1297	10903301	Allowance 7	Something		10903301
15	1297	10903301	Allowance 8	Something
16						not found
Sheet2

Cell Formulas
Range	Formula
F9:F10,F16	F9	=LET(u,UNIQUE(VSTACK(FILTER(Table1[[Employee]:[Allowances]],Table1[Cost Code]=$F$4,""),Exclude),,1), FILTER(CHOOSECOLS(u,2),(CHOOSECOLS(u,1)=F7),"not found"))
Dynamic array formulas.

Thank you very much Cubist. I really appreciate the efforts and time you have put in resolving my query and all those quick responses. Awesome!!! I can't thank enough!

 

[Cubist]

You're welcome.