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.

 

[awoohaw]

try this:

Book1
	A	B	C	D	E	F	G	H
1	Employee	Allowances
2	10903301	Standard Allowance	50		Emp		Amount
3	10903301	Allowance 1	100		10903301		50
4	10903301	Allowance 2	200				60
5	10903301	Allowance 3	300
6	10903301	Allowance 4	400
7	10903301	Allowance 5	500		Allowance
8	10903301	Allowance 6	600		Standard Allowance
9	10903301	Allowance 7	700
10	10903301	Standard Allowance	60
11	10903301	Allowance 9	900
12	10903301	Allowance 10	1000
13
Sheet13

Cell Formulas
Range	Formula
G3:G4	G3	=FILTER($C$2:$C$12,($A$2:$A$12=$E$3)*($B$2:$B$12=$E$8))
Dynamic array formulas.

 

[hananak]

try this:

Book1
	A	B	C	D	E	F	G	H
1	Employee	Allowances
2	10903301	Standard Allowance	50		Emp		Amount
3	10903301	Allowance 1	100		10903301		50
4	10903301	Allowance 2	200				60
5	10903301	Allowance 3	300
6	10903301	Allowance 4	400
7	10903301	Allowance 5	500		Allowance
8	10903301	Allowance 6	600		Standard Allowance
9	10903301	Allowance 7	700
10	10903301	Standard Allowance	60
11	10903301	Allowance 9	900
12	10903301	Allowance 10	1000
13
Sheet13

Cell Formulas
Range	Formula
G3:G4	G3	=FILTER($C$2:$C$12,($A$2:$A$12=$E$3)*($B$2:$B$12=$E$8))
Dynamic array formulas.

Thanks alot. It worked perfectly.
I need to learn this Filter formula, looks.like very can do so much with this.

 

[awoohaw]

You're welcome.

Best Wishes.

 

[hananak]

You're welcome.

Best Wishes.

Can you help me with this as well.

The data is in (Column B and C):

Column F, where I would like to extract the list based on the Employee no, the highlighted in yellow is where I would like to have formula.
Column I and J - is the exclusion list, where based on the Employee no and Allowance Description, it should filter the list in column F - cells highlighted in yellow.

If there is nothing for any employee on the exclusion list then it should return all the allowances for each employee on highlighted cells in column F.

Please see the attachment.

Your help would be really apprecaited.

 

[awoohaw]

Can you help me with this as well.

The data is in (Column B and C):

Column F, where I would like to extract the list based on the Employee no, the highlighted in yellow is where I would like to have formula.
Column I and J - is the exclusion list, where based on the Employee no and Allowance Description, it should filter the list in column F - cells highlighted in yellow.

If there is nothing for any employee on the exclusion list then it should return all the allowances for each employee on highlighted cells in column F.

Please see the attachment.

Your help would be really apprecaited.

Although your question is slightly related, it is different from the original. You should probably put it in a new post. (When you do that you could post a link to that post in this thread to give it some continuity).

Some clarification please?
If there is any number of exclusions do you want a list of the remaining allowance?. And if none, do you want all? Is that correct?

Does you data look like the image?
If so, that complicates things (for me, others forum members may know how to work it out.
Are only Allowance Types and Employee Numbers in Column F?

Can you change the format of the resulting output? (horizontal instead of vertical.)

How do you get this data? Can your source get it to you differently?

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3		Employee	Allowances
4		11068246	Allowance 1			Employee No			Employee	Allowances
5		11068246	Allowance 2			11068246			11068246	Allowance 4
6		11068246	Allowance 3						10903301	Allowance 8
7		11068246	Allowance 4			Allowance 1
8		10903301	Allowance 1			Allowance 2
9		10903301	Allowance 2			Allowance 3
10		10903301	Allowance 3
11		10903301	Allowance 4			Employee No
12		10903301	Allowance 5			10903301
13		10903301	Allowance 6
14		10903301	Allowance 7			Allowance 1
15		10903301	Allowance 8			Allowance 2
16						Allowance 3
17						Allowance 4
18						Allowance 5
19						Allowance 6
20						Allowance 7
Sheet2

Cell Formulas
Range	Formula
F7:F9,F14:F20	F7	=FILTER($C$4:$C$15,($B$4:$B$15=F5)*($C$4:$C$15<>FILTER($J$5:$J$6,$I$5:$I$6=F5)))
Dynamic array formulas.

 

[hananak]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3		Employee	Allowances
4		11068246	Allowance 1			Employee No			Employee	Allowances
5		11068246	Allowance 2			11068246			11068246	Allowance 4
6		11068246	Allowance 3						10903301	Allowance 8
7		11068246	Allowance 4			Allowance 1
8		10903301	Allowance 1			Allowance 2
9		10903301	Allowance 2			Allowance 3
10		10903301	Allowance 3
11		10903301	Allowance 4			Employee No
12		10903301	Allowance 5			10903301
13		10903301	Allowance 6
14		10903301	Allowance 7			Allowance 1
15		10903301	Allowance 8			Allowance 2
16						Allowance 3
17						Allowance 4
18						Allowance 5
19						Allowance 6
20						Allowance 7
Sheet2

Cell Formulas
Range	Formula
F7:F9,F14:F20	F7	=FILTER($C$4:$C$15,($B$4:$B$15=F5)*($C$4:$C$15<>FILTER($J$5:$J$6,$I$5:$I$6=F5)))
Dynamic array formulas.

Thanks for your reply.

The formula is working but see below.

The only bit left is, if that can be built in this formula is. Currently, I have to put something in I and J columns (Exclusion List), otherwise the formula will not work. What I want is, if I don't want exclude anything for an employee, then the formula should show all the allowances in column F, without putting any thing in I and J columns (Exclusion List).

 

[Cubist]

Minor change to the formula.

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3		Employee	Allowances
4		11068246	Allowance 1			Employee No			Employee	Allowances
5		11068246	Allowance 2			11068246			11068246	Allowance 4
6		11068246	Allowance 3
7		11068246	Allowance 4			Allowance 1
8		10903301	Allowance 1			Allowance 2
9		10903301	Allowance 2			Allowance 3
10		10903301	Allowance 3
11		10903301	Allowance 4			Employee No
12		10903301	Allowance 5			10903301
13		10903301	Allowance 6
14		10903301	Allowance 7			Allowance 1
15		10903301	Allowance 8			Allowance 2
16						Allowance 3
17						Allowance 4
18						Allowance 5
19						Allowance 6
20						Allowance 7
21						Allowance 8
Sheet2

Cell Formulas
Range	Formula
F7:F9,F14:F21	F7	=FILTER($C$4:$C$15,($B$4:$B$15=F5)*($C$4:$C$15<>FILTER($J$5:$J$6,$I$5:$I$6=F5,TRUE)))
Dynamic array formulas.

 

[hananak]

Minor change to the formula.

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3		Employee	Allowances
4		11068246	Allowance 1			Employee No			Employee	Allowances
5		11068246	Allowance 2			11068246			11068246	Allowance 4
6		11068246	Allowance 3
7		11068246	Allowance 4			Allowance 1
8		10903301	Allowance 1			Allowance 2
9		10903301	Allowance 2			Allowance 3
10		10903301	Allowance 3
11		10903301	Allowance 4			Employee No
12		10903301	Allowance 5			10903301
13		10903301	Allowance 6
14		10903301	Allowance 7			Allowance 1
15		10903301	Allowance 8			Allowance 2
16						Allowance 3
17						Allowance 4
18						Allowance 5
19						Allowance 6
20						Allowance 7
21						Allowance 8
Sheet2

Cell Formulas
Range	Formula
F7:F9,F14:F21	F7	=FILTER($C$4:$C$15,($B$4:$B$15=F5)*($C$4:$C$15<>FILTER($J$5:$J$6,$I$5:$I$6=F5,TRUE)))
Dynamic array formulas.

Another Limitation jut found now in the formula is, if I have to exclude multiple Allowances for a particular Employee, then it gives error.

Can this be fixed please!