Xlookup

[Garetht2014]

Hi,

I am trying to create a formula for the below where the 'Plan In' column will auto populate with a Yes or No depending on some criteria in other work sheets which are ELF, PROD & absences.

My current formula is this which works without looking at the Absences sheet.

=IF(AND(XLOOKUP([@Name],ELF!A:A,ELF!H:H,"",0) <0.35, (XLOOKUP([@Name],Prod!A:A,Prod!O:O,"",0)>0.8)), "Yes", "No")

I now want to add in information about absences and want to modify the formula above to include this. The end result of the formula is to return a Yes if the the ELF is under 0.35, PROD is above 0.8 and total amount of absences are below 10. If someone doesn't meet all 3 criteria then it just returns a NO in the plan in column.


This is the formula I have tried which it isn't working but don't understand what is wrong:

IF(AND(XLOOKUP([@Name],ELF!A:A,ELF!H:H,"",0) <0.35, (XLOOKUP([@Name],Prod!A:A,Prod!O:O,"",0)>0.8)), (XLOOKUP([@Name],Absences!A:A,Absences!T:T,"",0)>10)), "Yes", "No")

Name	Territory	Manager	Plan in	Absences
Joe Bloggs 1	Wales	Manager 1	Yes
Joe Bloggs 2	Wales	Manager 2	No

Absence sheet layout:

Engineer Name	AM	GM	Length of Service	January	February	March	April	May	June	July	August	September	October	November	December	January	February	March	Total
Joe Bloggs 1								8	20	7	2				6	6		2	51
Joe Bloggs 2				3		9	1	3		2	2	1			6	8	4	4	43

Appreciate any guidance someone can give.

 

[SunnyAlv]

below 10

that you want below 10

(XLOOKUP([@Name],Absences!A:A,Absences!T:T,"",0)>10)

but this XlookUp formula describes values above 10


so try like this

IF(AND(XLOOKUP([@Name],ELF!A:A,ELF!H:H,"",0) <0.35, (XLOOKUP([@Name],Prod!A:A,Prod!O:O,"",0)>0.8)), (XLOOKUP([@Name],Absences!A:A,Absences!T:T,"",0)<10)), "Yes", "No")

 

[Garetht2014]

Thank you for that.

I have tried the formula you posted but get this message:

 

[Fluff]

Try it like

=IF(AND(XLOOKUP([@Name],ELF!A:A,ELF!H:H,"",0) <0.35, XLOOKUP([@Name],Prod!A:A,Prod!O:O,"",0)>0.8, XLOOKUP([@Name],Absences!A:A,Absences!T:T,"",0)<10), "Yes", "No")

 

[Garetht2014]

That worked, cheers Fluff

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Garetht2014]

Ok, I have found a problem that can't fix! which was something I hadn't picked up on till now unfortunately.
I have noticed that if the name of the person I am looking for is not on the absence spreadsheet it returns "Do Not Plan: High Absence". So I had another go at trying to resolve this but have come unstuck.

Currently I have this unfinished formula I am testing but it keep returning ERROR when the ELF is over 3.5%, but it should return "Do Not Plan, High ELF", but I am also getting an error when the ELF is below 3.5%, Prod above 80% and Absences below 10 when it should return "Plan In" but I am getting error!

My requirement for the formula is if the below requirements are met return "Plan In", if one of the conditions are not met return a message with the reason (i.e. Do Not Plan: High ELF, Do Not Plan: Low Prod, Do Not Plan: High Absences or any mixture of the 3 (Do Not Plan: High ELF, Low Prod):

Absences under 10
ELF under 3.5%
Prod over 80%

=IF(OR([@Absences]="",[@Absences]=0),
IF(AND([@[Elf ]]<0.35,[@[Prod ]]>0.8,[@Absences]<10),
"Plan In",
IF(AND([@[Elf ]]>0.35,[@[Prod ]]<0.8),
"Do Not Plan, High ELF, Low Prod",
IF(AND([@[Elf ]]>0.35,[@[Prod ]]>0.8),
"Do Not Plan, High ELF",
IF(AND([@[Elf ]]<0.35,[@[Prod ]]<0.8),
"Do Not Plan, Low Prod",
"ERROR"
)
)
)
),
"ERROR"
)

Column1	Elf	Prod	Absences
ERROR​	4%​	83%​	2​
Plan In​	0%​	82%​	0​
ERROR​	2%​	86%​	2​

 

[Fluff]

As this is a completely different question, it needs a new thread. Thanks

 

[Garetht2014]

Ok, thought it was ok here as it is just a variation on what you helped with before.