Filter Question

[Tipsey]

I am having trouble cross referencing and filtering 2 tables for matching numbers.

One table has a list of all employees and whether they have passed training. Table1[Number],Table1[Pass]
I have another table with employee numbers that are on shift today. Table2[Number]

I want to filter my list of people on shift today that have NOT completed training yet.

=FILTER(Table1[Number],Table1[Pass]<>"pass")

this formula gives me the list of people who still need their training, but I keep getting errors trying to use filter function on Table2[Number]

Please help :D

 

[Peter_SSs]

Perhaps you give us small dummy examples of Table1 and Table2 and explain, with reference to those sample tables, what the expected result would be?
The best way to provide the small sample tables would be with XL2BB
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[Tipsey]

I don't have XLBB available right now.

but this would be an example
Table1

1	Pass
2
3	Pass
4
5	Pass
6
7	Pass
8
9
10

Table2

1

4

5

6

9

10

Expected Result

4

6

9

10

 

[Peter_SSs]

OK, thanks for the sample (not quite as good as XL2BB but much better than nothing. )

Is this what you want?
Table1 is columns A:B and Table2 is column E

If your Table1 is a bit more complex that what I have set up then the formula might need a bit of a tweak. Post back with more details of Table1 layout if that is the case.

Tipsey.xlsm
	A	B	C	D	E	F	G	H
1	Number	Pass			Number			On today & not trained
2	1	Pass			1			4
3	2				4			6
4	3	Pass			5			9
5	4				6			10
6	5	Pass			9
7	6				10
8	7	Pass
9	8
10	9
11	10
12
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=FILTER(Table2[Number],VLOOKUP(Table2[Number],Table1,2,0)="")
Dynamic array formulas.

 

[Tipsey]

This is currently giving me a #N/A error. Is that an indication there is a problem with my input data?

 

[Peter_SSs]

It's hard to tell. Does Table1 have any #N/A values in it? See the possible consequence of that below. Similar if Table2 has any #N/A error values

If you cannot use XL2BB can you for a start post a picture of each table (& advise the exact table names) and post the the formula if you have altered it at all to suit your circumstances.

Tipsey.xlsm
	A	B	C	D	E	F	G	H
1	Number	Pass			Number			On today & not trained
2	1	Pass			1			#N/A
3	2				4
4	3	Pass			5
5	4	#N/A			6
6	5	Pass			9
7	6				10
8	7	Pass
9	8
10	9
11	10
12
Sheet1

Cell Formulas
Range	Formula
H2	H2	=FILTER(Table2[Number],VLOOKUP(Table2[Number],Table1,2,0)="")
B5	B5	=NA()

 

[Tipsey]

It appears the reason the NA error is coming up is because there are some numbers in table2 that do not exist in table1.
When I added 11 to table2, it gave the NA error.

 

[Tipsey]

=FILTER(Table2[Number],IFERROR(VLOOKUP(Table2[Number],Table1,2,0)="",FALSE))

This is working well now. Thanks for the help Peter, I truly appreciate all of the helpful assistance everyone provides here.

 

[Peter_SSs]

Cheers, glad you got it sorted. Not important, but you could be more specific with the N/A error like this,

Tipsey.xlsm
	A	B	C	D	E	F	G	H
1	Number	Pass			Number			On today & not trained
2	1	Pass			1			4
3	2				4			6
4	3	Pass			5			9
5	4				6			10
6	5	Pass			9
7	6				10
8	7	Pass			11
9	8
10	9
11	10
12
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=FILTER(Table2[Number],IFNA(VLOOKUP(Table2[Number],Table1,2,0)="",0))
Dynamic array formulas.