How to use Filter with multiple criteria

[jayjay123]

Hi there,

This is a work question (using 365), so I've tried to recreate mock data on my home laptop. So unfortunately the FILTER function doesn't work on my mac.

Heres a mock data set:

Date	Description 1	Description 2	Description 3	Combined 1+2+3	Amount
10/10/21​	MERCH 9812374 10.10	BOB MARLEY	Rejected	MERCH 9812374 10.10 BOB MARLEY Rejected	560​
28/10/21​	RJ 7395821 28.10	HARRY JAMES	Payment Stop	RJ 7395821 28.10 HARRY JAMES Payment Stop	835​
31/10/21​	RJ 9812374 31.10		Payment Stop	RJ 9812374 31.10 Payment Stop	-340​
1/11/21​	MERCH 9812374 1.11	BOB MARLEY	Rejected	MERCH 9812374 1.11 BOB MARLEY Rejected	300​
3/11/21​	SETT 9812374 3.11	BOB MARLEY	Closed	SETT 9812374 3.11 BOB MARLEY Closed	250​
27/11/21​	RJ 9812374 31.10		Payment Stop	RJ 9812374 31.10 Payment Stop	-340​
2/12/21​	RJ 9812374 2.12		Payment Stop	RJ 9812374 2.12 Payment Stop	-250​

In this example, BOB MARLEY is linked to the ID: 9812374

NAME	ID
BOB MARLEY	9812374​

From the dataset, in Excel 365, I have used the filter/index function and have successfully created a dropdown list to search based on the BOB MARLEY (Description 2 - Name).

Name	BOB MARLEY
10/10/21​	MERCH 9812374 10.10 BOB MARLEY Rejected	560​
1/11/21​	MERCH 9812374 1.11 BOB MARLEY Rejected	300​
3/11/21​	SETT 9812374 3.11 BOB MARLEY Closed	250​

However, I would like to capture transactions with the ID related to Bob as well. These transactions are missing:

31/10/21​	RJ 9812374 31.10 Payment Stop	-340​
27/11/21​	RJ 9812374 31.10 Payment Stop	-340​
2/12/21​	RJ 9812374 2.12 Payment Stop	-250​

What I would like to achieve is, a drop down menu where I can select / retrieve multiple criteria...so both BOB MARLET & ID 9812374. In an ideal scenario, I'd love it to pick up all the transactions like below:

Name	BOB MARLEY
10/10/21​	MERCH 9812374 10.10 BOB MARLEY Rejected	560​
1/11/21​	MERCH 9812374 1.11 BOB MARLEY Rejected	300​
3/11/21​	SETT 9812374 3.11 BOB MARLEY Closed	250​
31/10/21​	RJ 9812374 31.10 Payment Stop	-340​
27/11/21​	RJ 9812374 31.10 Payment Stop	-340​
2/12/21​	RJ 9812374 2.12 Payment Stop	-250​

Is there a way to do this without VBA & just use excel formulas?

Thanks for your help!!

 

[jasonb75]

Does this help?

Book1
	A	B	C	D	E	F
1	Date	Description 1	Description 2	Description 3	Combined 1+2+3	Amount
2	10/10/2021	MERCH 9812374 10.10	BOB MARLEY	Rejected	MERCH 9812374 10.10 BOB MARLEY Rejected	560
3	28/10/2021	RJ 7395821 28.10	HARRY JAMES	Payment Stop	RJ 7395821 28.10 HARRY JAMES Payment Stop	835
4	31/10/2021	RJ 9812374 31.10		Payment Stop	RJ 9812374 31.10 Payment Stop	-340
5	01/11/2021	MERCH 9812374 1.11	BOB MARLEY	Rejected	MERCH 9812374 1.11 BOB MARLEY Rejected	300
6	03/11/2021	SETT 9812374 3.11	BOB MARLEY	Closed	SETT 9812374 3.11 BOB MARLEY Closed	250
7	27/11/2021	RJ 9812374 31.10		Payment Stop	RJ 9812374 31.10 Payment Stop	-340
8	02/12/2021	RJ 9812374 2.12		Payment Stop	RJ 9812374 2.12 Payment Stop	-250
9
10
11
12	NAME	ID			MERCH 9812374 10.10 BOB MARLEY Rejected	560
13	BOB MARLEY	9812374			RJ 9812374 31.10 Payment Stop	-340
14					MERCH 9812374 1.11 BOB MARLEY Rejected	300
15					SETT 9812374 3.11 BOB MARLEY Closed	250
16					RJ 9812374 31.10 Payment Stop	-340
17					RJ 9812374 2.12 Payment Stop	-250
Sheet1

Cell Formulas
Range	Formula
E12:F17	E12	=FILTER(E2:F8,ISNUMBER(SEARCH(A13,E2:E8))+ISNUMBER(SEARCH(B13,E2:E8)),"Not found")
Dynamic array formulas.

 

[jayjay123]

Wow thanks so much! is there a way to incorporate the corresponding date in the beginning as well?
Employees copy and paste the ID from a database and sometimes the ID contains 00 in front. For example "009812374". Is it possible to amend the formula so that it picks up the digits not including 00?

Thanks for your help @jasonb75. you're amazing!

 

[Peter_SSs]

Is this what you are after?

21 12 06.xlsm
	A	B	C	D	E	F
1	Date	Description 1	Description 2	Description 3	Combined 1+2+3	Amount
2	10/10/2021	MERCH 9812374 10.10	BOB MARLEY	Rejected	MERCH 9812374 10.10 BOB MARLEY Rejected	560
3	28/10/2021	RJ 7395821 28.10	HARRY JAMES	Payment Stop	RJ 7395821 28.10 HARRY JAMES Payment Stop	835
4	31/10/2021	RJ 9812374 31.10		Payment Stop	RJ 9812374 31.10 Payment Stop	-340
5	1/11/2021	MERCH 9812374 1.11	BOB MARLEY	Rejected	MERCH 9812374 1.11 BOB MARLEY Rejected	300
6	3/11/2021	SETT 9812374 3.11	BOB MARLEY	Closed	SETT 9812374 3.11 BOB MARLEY Closed	250
7	27/11/2021	RJ 9812374 31.10		Payment Stop	RJ 9812374 31.10 Payment Stop	-340
8	2/12/2021	RJ 9812374 2.12		Payment Stop	RJ 9812374 2.12 Payment Stop	-250
9
10
11
12	NAME	ID		Date	Combined 1+2+3	Amount
13	BOB MARLEY	9812374		10/10/2021	MERCH 9812374 10.10 BOB MARLEY Rejected	560
14				31/10/2021	RJ 9812374 31.10 Payment Stop	-340
15				1/11/2021	MERCH 9812374 1.11 BOB MARLEY Rejected	300
16				3/11/2021	SETT 9812374 3.11 BOB MARLEY Closed	250
17				27/11/2021	RJ 9812374 31.10 Payment Stop	-340
18				2/12/2021	RJ 9812374 2.12 Payment Stop	-250
19
FILTER

Cell Formulas
Range	Formula
D13:F18	D13	=FILTER(FILTER(A2:F8,(C2:C8=A13)+ISNUMBER(FIND(B13,B2:B8)),""),COUNTIF(D12:F12,A1:F1))
Dynamic array formulas.

 

[jayjay123]

Thanks so much @Peter_SSs!!

 

[Peter_SSs]

You're welcome.