Yes or no answer depending on criteria

[Garetht2014]

I am trying to write a formula that is based on 2 criteria, depending it is meets that critieria it will wither give me a Yes or No answer, but not really getting anywhere with it.

What I am trying to do it get the 'Work Saturday or not' Column to populate with a 'Yes' or 'No' based on 2 things (ELF & PROD). If Engineer 1 has a Total ELF score of 3% or less and a Total Comp % of 75% or more, then it will populate the 'Work Saturday or not column' with a Yes.

If an engineer does not meet a Total ELF score of 3% or lower or a prod Total Comp % of 78% or higher then the 'Work Saturday or not' will be a No.

Hope all that makes sense!

I have 3 Worksheets:

(Saturday worksheet)

ELF Data (ELF worksheet)

PROD Data (Prod worksheet)

 

[KRice]

Do your data exist in official Excel tables, or are the data in ranges only?

 

[KRice]

Try this:

Book2
	A			D
1	Name			Work Saturday or not
2	Engineer 1			No
3	Engineer 2			Yes
4	Engineer 3			No
5	Engineer 4			No
Sat

Cell Formulas
Range	Formula
D2:D5	D2	=IF(AND(XLOOKUP(A2,ELF!$A$2:$A$5,ELF!$H$2:$H$5,"",0)<=0.03,XLOOKUP(A2,Prod!$A$2:$A$5,Prod!$O$2:$O$5,"",0)>0.75),"Yes","No")