Index, Match - with 2 "contains" conditions

[psrs0810]

I have a long file path that I am trying to match up to a table so that I can pull in the needed reports. How can I add in 2 "contain" formulas to identify the report path?
for example, I have one column that would have a location: BMH
then I have another column that would have a department number: 1005
so in the sample below, it would bring back the second report path.

E:\Productivity\ActionOI\Reports\Calendar 2022\Annual\Custom Comparison 40%\BMHa-ccr-Pediatrics_1006.PDF

E:\Productivity\ActionOI\Reports\Calendar 2022\Annual\Custom Comparison 40%\BMHa-ccr-Perinatal Testing Center_1005.PDF

 

[James006]

Hi,
With cell A1 for BMH and cell B1 for 1005, you can test following:

="E:\Productivity\ActionOI\Reports\Calendar  2022\Annual\Custom Comparison 40%\"&A1&"*"&B1&".PDF"

 

[JGordon11]

delme.xlsx
	A	B	C	D	E	F
1	Path		Location	Department No.	Path
2	E:\Productivity\ActionOI\Reports\Calendar 2022\Annual\Custom Comparison 40%\BMHa-ccr-Pediatrics_1006.PDF		BMH	1005	E:\Productivity\ActionOI\Reports\Calendar 2022\Annual\Custom Comparison 40%\BMHa-ccr-Perinatal Testing Center_1005.PDF
3	E:\Productivity\ActionOI\Reports\Calendar 2022\Annual\Custom Comparison 40%\BMHa-ccr-Perinatal Testing Center_1005.PDF		BMH	1006	E:\Productivity\ActionOI\Reports\Calendar 2022\Annual\Custom Comparison 40%\BMHa-ccr-Pediatrics_1006.PDF
4			BMZ	1006	#N/A
5			BMH	2006	#N/A
6
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=INDEX($A$2:$A$3,MATCH(TRUE,ISNUMBER(SEARCH(C2,$A$2:$A$3)*SEARCH(D2,$A$2:$A$3)),0))

 

[psrs0810]

JGordon - I tried your formula and it worked perfectly - thank you both for replying