SHORT LIST for ROLE and Location using Filters

[robbarba]

20231114 - Active Hires.xlsx
	A	B	C	D	E	F	G	H	I	J
1	First Last	Data2	data3	data4	Role	Role ABBR	Assigned City	Assigned State	TB	TB
2	TECH1				Deploy Technician, iPad		Springfield	CA	Rosemead	Highland
3	TECH2				Tech Bar	TB	Springfield	CA	#VALUE!	TECH2
4	TECH3				CCC Support Irwindale	CCC	Mapleton	CA		TECH6
5	TECH4				Operations Manager	MGT	Springfield	CA		TECH7
6	TECH5				Logistics Technician		Cactus Grove	CA
7	TECH6				Tech Bar	TB	Springfield	CA
8	TECH7				Tech Bar	TB	Highland	CA
9	TECH8				Tier II Field Support Tech		Springfield	CA
10
Sheet2

Cell Formulas
Range	Formula
I3	I3	=FILTER($A$2:$A$9,($F2:$F$9=I1)+(F$2:$G$9=I2))
J3:J5	J3	=FILTER($A$2:$A$9,($F2:$F$9=J1)+($G$2:G$9=J2))
Dynamic array formulas.

What I am trying to do is to get a list of techs from Column A that meet the criteria, in Column I or Column J from columns F and G that meet the requirements in I3 and I4 or J3 and J4 respectively. My results are shown using the formulas. (both are incorrect)

 

[SunnyAlv]

idk what logic u say, but i understand the logic is u just wanted to lookup by 2 criteria, right ? and the criteria on cells I1 & I2 for column I and J1 & J2 for Column J
see below :

copy this and paste on Cell I3

=IFERROR(FILTER(A:A,F:F&G:G=$I$1&$I$2),"No Tech Found")

copy this and paste on Cell J3

=IFERROR(FILTER(A:A,F:F&G:G=$J$1&$J$2),"No Tech Found")

*If there’s any unclear information please let me know

 

[Fluff]

My results are shown using the formulas. (both are incorrect)

It would help if you said what the correct results should be, but maybe

=FILTER($A$2:$A$9,($F2:$F$9=J1)*($G$2:G$9=J2))

 

[robbarba]

the ultimate goal is when I create a site such as Springfield, I would like to have that tech listed, then based on this expand to include other roles as well.

 

[Fluff]

Did you try either of the formulae suggested?