IF and Xlookup not showing expected result

[PaulyK]

Hi,

So I am having some issues with Status & RAG Report I have adapted from a previous question. It had all been working fine, but i came back from being away to find it wasn't working properly.

The Main report has automated data from a Data Extract (for Budget, Dates, Project Name etc) which you just enter the Project Number into Column A and it updates the fields There is a RAG Scoring table which looks to see if 3 criteria are matched or not and gives a score. (Criteria Tracker Updated (Yes / No) End Date passed (Yes / No) Budget Exceeds amount (Yes / No)

When i reverse sort the Status Report Project Number the results don't match, It doesn't appear to be from not locking the range, but that I am missing a step where it is selecting the correct row to look up (thus allowing me to sort in any order) The Result is leaving blank cells when there should be a Red, Amber, Green etc.

The RAG Score is calculated using the below formula:

=IF('Project Status Summary'!C3="Tier 3","No RAG (Tier 3)",SWITCH(IFNA(XLOOKUP('Project Status Summary'!B3,[Project],[Score]),""),$H$2,$J$2,$H$3,$J$3,$H$4,$J$4,$H$5,$J$5,""))

but it seems to be a little unstable...

I've included all below... Any help much appreciated.

Pauly


Project Report

Project Status Summary - Update.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
2	Project #	Project Name	Project Tier	Project Manager	Status	Overall RAG	Tracker	Approved Budget	Actual Spend	Difference	Start Date	Completion Date
3	200000	Project 1	Tier 2	Person 2	Pending	Amber	No	£ 12,000.00	£ 3,500.00	£8,500.00	03/05/22	29/09/22
4	200001	Project 2	Tier 2	Person 2	Pending	Green	Yes	£ -	£ -	£0.00	31/05/22	29/09/22
5	200002	Project 3	Tier 2	Person 17	Pending	Amber	No	£ -	£ -	£0.00	18/07/22	02/09/22
6	200003	Project 4	Tier 3	Person 3	Work in Progress	No RAG (Tier 3)	Yes	£ 14,000.00	£ 13,000.00	£1,000.00	01/07/22	31/10/22
7	200004	Project 5	Tier 3	Person 10	Work in Progress	No RAG (Tier 3)	Not on Tracker	£ 7,450.00	£ 1,670.00	£5,780.00	01/06/22	30/09/22
8	200005	Project 6	Tier 3	Person 10	Work in Progress	No RAG (Tier 3)	Yes	£ 1,200.00	£ 1,300.00	-£100.00	01/06/22	31/10/22
9	200006	Project 7	Tier 1	Person 38	Pending	Amber	No	£ -	£ -	£0.00	31/05/22	29/09/22
10	200007	Project 8	Tier 2	Person 36	Pending	Green	Yes	£ 11,000.00	£ -	£11,000.00	31/05/22	30/08/22
11	200008	Project 9	Tier 2	Person 13	Work in Progress	Green	Yes	£ 76,000.00	£ -	£76,000.00	18/07/22	17/11/22
12	200009	Project 10	Tier 1	Person 14	Pending	Green	Yes	£ -	£ -	£0.00	31/05/22	30/12/22
13	200010	Project 11	Tier 1	Person 9	Pending	Amber	Yes	£ -	£ 261,404.00	-£261,404.00	21/04/22	29/06/22
14	200011	Project 12	Tier 3	Person 1	Work in Progress	No RAG (Tier 3)	Yes	£ 76,000.00	£ 54,000.00	£22,000.00	06/06/22	23/06/22
15	200012	Project 13	Tier 3	Person 24	Work in Progress	No RAG (Tier 3)	Yes	£ -	£ -	£0.00	30/05/22	09/06/22
16	200013	Project 14	Tier 2	Person 17	Work in Progress	Green	Yes	£ 62,000.00	£ 45,000.00	£17,000.00	30/05/22	29/09/22
17	200014	Project 15	Tier 3	Person 7	Work in Progress	No RAG (Tier 3)	Yes	£ 18,000.00	£ 800.00	£17,200.00	25/04/22	24/03/23
18	200015	Project 16	Tier 2	Person 13	Work in Progress	Green	Yes	£ 54,840.00	£ 23,232.00	£31,608.00	30/05/22	28/07/22
19	200016	Project 17	Tier 1	Person 16	Pending	Green	Yes	£ -	£ -	£0.00	04/05/22	28/07/22
20	200017	Project 18	Tier 3	Person 10	Work in Progress	No RAG (Tier 3)	Yes	£ 1,500.00	£ 1,600.00	-£100.00	16/05/22	01/08/22
21	200020	Project 21	Tier 3	Person 16	Work in Progress	Amber	No	£ -	£ 2,720.00	-£2,720.00	07/02/22	30/06/22
22	200021	Project 22	Tier 1	Person 8	Work in Progress	Amber	Yes	£ 160,300.00	£ 160,000.00	£300.00	19/01/22	21/02/22
23	200022	Project 23	Tier 2	Person 1	Work in Progress		No	£ 15,250.00	£ 10,561.00	£4,689.00	07/02/22	01/06/22
24	200023	Project 24	Tier 1	Person 5	Work in Progress		NO	£ 170,600.00	£ 40,242.00	£130,358.00	13/01/22	30/05/22
25
26
27	Enter 200024 in A25
Project Status Summary

Cell Formulas
Range	Formula
B3:B25	B3	=IFERROR(XLOOKUP(TEXT($A3,"######"),TblPPMEx[Project number],TblPPMEx[Project name]),"")
C3:C25	C3	=IFERROR(XLOOKUP(TEXT($A3,"######"),TblPPMEx[Project number],TblPPMEx[Type]),"")
D3:D25	D3	=IFERROR(XLOOKUP(TEXT($A3,"######"),TblPPMEx[Project number],TblPPMEx[Project manager]),"")
E3:E25	E3	=IFERROR(XLOOKUP(TEXT($A3,"######"),TblPPMEx[Project number],TblPPMEx[Status(state)]),"")
F3:F25	F3	=IFERROR(XLOOKUP([@[Project '#]],'RAG Score'!$A$2#,TblRAGScore[RAG]),"")
H3:H25	H3	=IFERROR(XLOOKUP(TEXT($A3,"######"),TblPPMEx[Project number],TblPPMEx[Approved budget]),"")
I3:I25	I3	=IFERROR(XLOOKUP(TEXT($A3,"######"),TblPPMEx[Project number],TblPPMEx[Actual spend]),"")
J3:J25	J3	=IFERROR([@[Approved Budget]]-[@[Actual Spend]]," ")
K3:K25	K3	=IFERROR(XLOOKUP(TEXT($A3,"######"),TblPPMEx[Project number],TblPPMEx[Planned start date]),"")
L3:L25	L3	=IFERROR(XLOOKUP(TEXT($A3,"######"),TblPPMEx[Project number],TblPPMEx[Forecast end date]),"")

Named Ranges
Name	Refers To	Cells
rngActSpnd	=TblPPMEx[Actual spend]	I3:I25
rngAppBud	=TblPPMEx[Approved budget]	H3:H25
rngForEnd	=TblPPMEx[Forecast end date]	L3:L25
rngPM	=TblPPMEx[Project manager]	D3:D25
rngProjID	=TblPPMEx[Project number]	B3:E25, H3:I25, K3:L25
rngProjIDRAG	='RAG Score'!$A$2:$A$24	F3:F25
rngProjName	=TblPPMEx[Project name]	B3:B25
rngProjStat	=TblPPMEx[Status(state)]	E3:E25
rngStart	=TblPPMEx[Planned start date]	K3:K25
rngTier	=TblPPMEx[Type]	C3:C25

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F3:F25	Expression	=$F3=Lists!$B$5	text	NO
F3:F25	Expression	=$F3=Lists!$B$2	text	NO
G3:G25	Cell Value	contains "Not on Tracker"	text	NO
F3:F25	Expression	=$F3=Lists!$B$3	text	NO
F3:F25	Expression	=$F3=Lists!$B$4	text	NO
J3:J25	Cell Value	<-1000	text	NO
I3:I25	Expression	=ISBLANK	text	NO
G3:G25	Expression	=$G3="Tier 3 Project"	text	NO
I3:I25	Expression	=$I3>$H3	text	NO
I3:I25	Expression	=$I3<=$H3	text	NO
G3:G29	Expression	=$G3="No"	text	NO
G3:G29	Expression	=$G3="Yes"	text	NO
L3:L25	Expression	=AND($L3>TODAY(), $L3-TODAY()<=30)	text	NO
L3:L25	Expression	=$L3<TODAY()	text	NO
L3:L25	Expression	=$L3=TODAY()+30	text	NO
B3:B25	Cell Value	duplicates	text	NO

PPM Extract

Project Status Summary - Update.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Project number	Project name	Type	Category	Budget category	Status(state)	Approved budget	Actual spend	Project manager	Planned start date	Forecast end date
2	200000	Project 1	Tier 2	Cat 1	Blank	Pending	£12,000.00	£3,500.00	Person 2	03/05/22	29/09/22
3	200001	Project 2	Tier 2	Cat 1	Blank	Pending	£0.00	£0.00	Person 2	31/05/22	29/09/22
4	200002	Project 3	Tier 2	Cat 1	Blank	Pending	£0.00	£0.00	Person 17	18/07/22	02/09/22
5	200003	Project 4	Tier 3	Cat 1	Blank	Work in Progress	£14,000.00	£13,000.00	Person 3	01/07/22	31/10/22
6	200004	Project 5	Tier 3	Cat 1	Blank	Work in Progress	£7,450.00	£1,670.00	Person 10	01/06/22	30/09/22
7	200005	Project 6	Tier 3	Cat 1	Blank	Work in Progress	£1,200.00	£1,300.00	Person 10	01/06/22	31/10/22
8	200006	Project 7	Tier 1	Cat 1	Blank	Pending	£0.00	£0.00	Person 38	31/05/22	29/09/22
9	200007	Project 8	Tier 2	Cat 1	Blank	Pending	£11,000.00	£0.00	Person 36	31/05/22	30/08/22
10	200008	Project 9	Tier 2	Cat 1	Blank	Work in Progress	£76,000.00	£0.00	Person 13	18/07/22	17/11/22
11	200009	Project 10	Tier 1	Cat 1	Blank	Pending	£0.00	£0.00	Person 14	31/05/22	30/12/22
12	200010	Project 11	Tier 1	Cat 1	Blank	Pending	£0.00	£261,404.00	Person 9	21/04/22	29/06/22
13	200011	Project 12	Tier 3	Cat 1	Blank	Work in Progress	£76,000.00	£54,000.00	Person 1	06/06/22	23/06/22
14	200012	Project 13	Tier 3	Cat 1	Blank	Work in Progress	£0.00	£0.00	Person 24	30/05/22	09/06/22
15	200013	Project 14	Tier 2	Cat 1	Blank	Work in Progress	£62,000.00	£45,000.00	Person 17	30/05/22	29/09/22
16	200014	Project 15	Tier 3	Cat 1	Blank	Work in Progress	£18,000.00	£800.00	Person 7	25/04/22	24/03/23
17	200015	Project 16	Tier 2	Cat 1	Blank	Work in Progress	£54,840.00	£23,232.00	Person 13	30/05/22	28/07/22
18	200016	Project 17	Tier 1	Cat 1	Blank	Pending	£0.00	£0.00	Person 16	04/05/22	28/07/22
19	200017	Project 18	Tier 3	Cat 1	Blank	Work in Progress	£1,500.00	£1,600.00	Person 10	16/05/22	01/08/22
20	200018	Project 19	Tier 2	Cat 1	Blank	Work in Progress	£50,000.00	£0.00	Person 23	04/05/22	29/07/22
21	200019	Project 20	Tier 2	Cat 1	Blank	Work in Progress	£65,450.00	£0.00	Person 11	31/03/22	31/05/22
22	200020	Project 21	Tier 3	Cat 1	Blank	Work in Progress	£0.00	£2,720.00	Person 16	07/02/22	30/06/22
23	200021	Project 22	Tier 1	Cat 1	Blank	Work in Progress	£160,300.00	£160,000.00	Person 8	19/01/22	21/02/22
24	200022	Project 23	Tier 2	Cat 1	Blank	Work in Progress	£15,250.00	£10,561.00	Person 1	07/02/22	01/06/22
25	200023	Project 24	Tier 1	Cat 1	Blank	Work in Progress	£170,600.00	£40,242.00	Person 5	13/01/22	30/05/22
26	200024	Project 25	Tier 1	Cat 1	Blank	Work in Progress	£130,000.00	£24,918.00	Person 6	16/12/21	31/03/23
PPM Extract

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B26	Cell Value	duplicates	text	NO

RAG Score Table

Project Status Summary - Update.xlsm
	A	B	C	D	E	F	G
1	ID	Project	Tracker	Budget	Date	Score	RAG
2	200000	Project 1	2	1	1	4	Amber
3	200001	Project 2	1	1	1	3	Green
4	200002	Project 3	2	1	1	4	Amber
5	200003	Project 4	1	1	1	3	No RAG (Tier 3)
6	200004	Project 5	2	1	1	4	No RAG (Tier 3)
7	200005	Project 6	1	1	1	3	No RAG (Tier 3)
8	200006	Project 7	2	1	1	4	Amber
9	200007	Project 8	1	1	1	3	Green
10	200008	Project 9	1	1	1	3	Green
11	200009	Project 10	1	1	1	3	Green
12	200010	Project 11	1	2	2	5	Amber
13	200011	Project 12	1	1	2	4	No RAG (Tier 3)
14	200012	Project 13	1	1	2	4	No RAG (Tier 3)
15	200013	Project 14	1	1	1	3	Green
16	200014	Project 15	1	1	1	3	No RAG (Tier 3)
17	200015	Project 16	1	1	1	3	Green
18	200016	Project 17	1	1	1	3	Green
19	200017	Project 18	1	1	1	3	No RAG (Tier 3)
20	200018	Project 19	2	2	2	6	No RAG (Tier 3)
21	200019	Project 20	1	1	2	4	Amber
22	200020	Project 21	2	1	2	5	Amber
23	200021	Project 22	2	1	2	5	Amber
24	200022	Project 23	2	1	1	4
25	200023	Project 24	2	1	2	5
26	200024	Project 25	2	1	2	5
RAG Score

Cell Formulas
Range	Formula
A2:A26	A2	=UNIQUE(TblPPMEx[Project number])
B2:B26	B2	=XLOOKUP(A2,TblPPMEx[Project number],TblPPMEx[Project name])
C2:C26	C2	=IF('Project Status Summary'!G3="Yes", "1", "2")
D2:D26	D2	=IF('Project Status Summary'!J3<-1000,"2","1")
E2:E26	E2	=IF('Project Status Summary'!L3<=TODAY(),"2","1")
F2:F26	F2	=C2+D2+E2
G2:G26	G2	=IF('Project Status Summary'!C3="Tier 3","No RAG (Tier 3)",SWITCH(IFNA(XLOOKUP('Project Status Summary'!B3,[Project],[Score]),""),$H$2,$J$2,$H$3,$J$3,$H$4,$J$4,$H$5,$J$5,""))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
rngProjID	=TblPPMEx[Project number]	A2:B26
rngProjIDRAG	='RAG Score'!$A$2:$A$24	B2
rngProjName	=TblPPMEx[Project name]	B2:B26

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G26	Expression	=$G2=Lists!$B$5	text	NO
G2:G26	Expression	=$G2=Lists!$B$3	text	NO
G2:G26	Expression	=$G2=Lists!$B$4	text	NO
G2:G26	Expression	=$G2=Lists!$B$2	text	NO
F2:F26	Expression	=$F2=3	text	NO
F2:F26	Expression	=$F2=4	text	NO
F2:F26	Expression	=$F2=5	text	NO
F2:F26	Expression	=$F2=6	text	NO

RAG Criteria

Project Status Summary - Update.xlsm
	H	I	J
1	Score	Criteria	RAG
2	3	All 3 Criteria Matched	Green
3	4	Total 4 Points	Amber
4	5	Total 5 Points	Amber
5	6	No Criteria Matched	Red
6	Exception	TIER 3 Project	No RAG (Tier 3)
RAG Score

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J6	Expression	=$J2="No RAG (Tier 3)"	text	NO
J2:J6	Expression	=$J2="Red"	text	NO
J2:J6	Expression	=$J2="Amber"	text	NO
J2:J6	Expression	=$J2="Green"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
J2:J6	List	=rngRAG
H2:H5	List	=rngRAGCnt

Project Status Summary - Update.xlsm
	K
1	RAG Score criteria
2	Tracker
3	Was Tracker updated? 1 = Yes, 2 = No
4	Budget
5	If Difference is >£1000 =2, Less than or equal to =1
6	Date
7	If Date has passed =2, otherwise =1
8	Total Score out of 6
9	TIER 3 Projects are automatically 'NO RAG (Tier 3)'
RAG Score

 

[PaulyK]

Not sure if the Title is correct for this post?

 

[PaulyK]

Anyone?