XLookup's

[Garetht2014]

Hi,

I have the below sheet I am working on and need a formula to return certain messages depending on the outcome of results.

My basic criteria is if the below conditions are met then in Column 1 it gives me the message 'Plan In' if the conditions are not met it gives me a message of 'Do Not Plan: High ELF' or 'Do Not Plan: High Absences' or 'Do Not Plan: Low Prod' or any combination of the 3 (Do Not Plan: High ELF, Low Prod' for instance.

To meet the criteria for 'Plan In' the below must be met:

ELF - less that 3.5%
Prod - 80% or higher
Absences - 10 or Less

In Column D there is an unfinished formula because I can not get it to work correctly, all it returns is 'ERROR' even though 2 of the rows (4 & 7) meet the criteria and should say 'Plan In'! What I have noticed is that if I change the absences to 0 then it will give me the message that meets the criteria for Plan In or one of the other messages for Do no Plan in, so I know it is something to do with the Absences part of the formula but can't work it out, even tried ChatGPT but that didn't work either


Please can someone help me with this ?

Copy of Saturday Approval (version 1) - testing one.xlsx
	A	B	C	D		F	G	H	I
1	Name	Territory	Resource Manager	Column1		Elf	Prod	Absences	What is should say
2	Joe Bloggs 1	Avon and Somerset	Manager 1	ERROR		5%	83%	2	Do Not Plan: High ELF
3	Joe Bloggs 2	East Wales and Gloucestershire	Manager 1	ERROR		0%	90%	11	Do Not Plan: High Absences
4	Joe Bloggs 3	Avon and Somerset	Manager 1	ERROR		0%	82%	2	Plan In
5	Joe Bloggs 4	Avon and Somerset	Manager 1	ERROR		3%	78%	2	Do Not Plan: Low Prod
6	Joe Bloggs 5	Avon and Somerset	Manager 1	ERROR		6%	75%	11	Do not Plan: High ELF, Low Prod, High Absences
7	Joe Bloggs 6	Avon and Somerset	Manager 1	ERROR		3%	94%	1	Plan in
8	Hassun Salim	East Wales and Gloucestershire	Manager 1	ERROR		5%	78%	1	Do not Plan: High ELF, Low Prod
9	Jack Latham	East Wales and Gloucestershire	Manager 1	ERROR		1%	74%	12	Do Not Plan: Low Prod High Absences
Low volume do not plan list

Cell Formulas
Range	Formula
D2:D9	D2	=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" )
F9	F9	=XLOOKUP([@Name], ELF!A:A, ELF!H:H, "", 0)
G9	G9	=XLOOKUP([@Name], Prod!A:A, Prod!O:O, "", 0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D8:I177,D2:H7	Cell Value	="Plan in"	text	NO
D8:I177,D2:H7	Cell Value	contains ""	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A2:A177	Text length	<=100
B2:B177	Any value
C2:C177	Any value

 

[etaf]

copied into a sheet from xl2bb and had issues

ELF - less that 3.5%
Prod - 80% or higher
Absences - 10 or Less

=IF( AND(F2 <0.035 , G2>0.8 , H2<=10 ), "Plan In" , IF(

trying to work out the other conditions
as absense H - does seem to be included

which i think are

IF(AND([F2>0.35,G2<0.8), "Do Not Plan, High ELF, Low Prod",
IF(AND([F2>0.35,G2>0.8), "Do Not Plan, High ELF",
IF(AND([F2<0.35,G2<0.8), "Do Not Plan, Low Prod",

F2 / G2 / H2 / expected
0.05 / 0.827272727 / 2 / Do Not Plan: High ELF

in this case
F2 is < 0.35
G2 is > 0,8

BUT to get the expected result
F2 needs to be >0.35 - which it is not


and you dont have a condition for that , so will get error

Book2
	A	B	C	D	E	F	G	H	I
1	Name	Territory	Resource Manager	Column1		Elf	Prod	Absences	What is should say
2	Joe Bloggs 1	Avon and Somerset	Manager 1	Error		0.05	0.827272727	2	Do Not Plan: High ELF
3	Joe Bloggs 2	East Wales and Gloucestershire	Manager 1	Error		0	0.9	11	Do Not Plan: High Absences
4	Joe Bloggs 3	Avon and Somerset	Manager 1	Plan In		0	0.815533981	2	Plan In
5	Joe Bloggs 4	Avon and Somerset	Manager 1	Do Not Plan, Low Prod		0.034482759	0.78	2	Do Not Plan: Low Prod
6	Joe Bloggs 5	Avon and Somerset	Manager 1	Do Not Plan, Low Prod		0.06	0.75	11	Do not Plan: High ELF, Low Prod, High Absences
7	Joe Bloggs 6	Avon and Somerset	Manager 1	Plan In		0.025641026	0.944444444	1	Plan in
8	Hassun Salim	East Wales and Gloucestershire	Manager 1	Do Not Plan, Low Prod		0.05	0.78	1	Do not Plan: High ELF, Low Prod
9	Jack Latham	East Wales and Gloucestershire	Manager 1	Do Not Plan, High ELF		=XLOOKUP([@Name], ELF!A:A, ELF!H:H, "", 0)	=XLOOKUP([@Name], Prod!A:A, Prod!O:O, "", 0)	12	Do Not Plan: Low Prod High Absences
10				Do Not Plan, Low Prod
11				Do Not Plan, Low Prod
12				Do Not Plan, Low Prod
Sheet1

Cell Formulas
Range	Formula
D2:D12	D2	=IF(AND(F2<0.035,G2>0.8,H2<=10),"Plan In",IF(AND(F2>0.35,G2<0.8),"Do Not Plan, High ELF, Low Prod",IF(AND(F2>0.35,G2>0.8),"Do Not Plan, High ELF",IF(AND(F2<0.35,G2<0.8),"Do Not Plan, Low Prod","Error"))))