Combining XLOOKUP and IF/S or SWITCH

[PaulyK]

I am trying (but so far failing) to use an Xlookup inside my IF (or SWITCH) formula.

The scenario: Currently the IF looks for a Cell ref and gives the result based on data in that row. However I want to be able to to look just for the corresponding data in a specified Cell (which will be a Project ID) and return a result related to that. I have two tables that the data is taken from (a Master and an Overview) but if I sort or filter the rows in Overview, the formula displays different results to what it should.

Therefore in Columns D, E, F on Master I wanted to 'lock' the formula to the ID in Column A on Overview and match it to Column A on Master.

Overview

Project Status Summary v0.2 - test2.xlsm
	A	B	C	D	E	F	G	H	I	J
2	Project #	Project Name	Project Type	Overall RAG	Update	Approved Budget	Actual Spend	Difference	Start Date	Completion Date
3	220000	Project A	Tier 2	Green	Yes	£ 150.00	£ 500.00	-£ 350.00	06/06/22	29/07/22
4	220001	Project B	Tier 3	No RAG (Tier 3)	No	£ 4,500.00	£ 2,000.00	£ 2,500.00	01/02/22	23/07/22
5	220002	Project C	Tier 1	Amber	No	£ 75,000.00	£ 68,000.00	£ 7,000.00	01/01/22	28/07/22
6	220003	Project D	Tier 2	Amber	No	£ 3,000.00	£ 1,500.00	£ 1,500.00	01/02/22	29/12/22
7	220004	Project E	Tier 1	Amber	Yes	£ 138,000.00	£ 115,000.00	£ 23,000.00	01/03/22	20/07/22
8	220005	Project F	Tier 1	Amber	No	£ 72,000.00	£ 68,500.00	£ 3,500.00	01/04/22	13/01/23
9	220006	Project G	Tier 2	Amber	Not on Tracker	£ 1,000.00	£ 2,200.00	-£ 1,200.00	01/05/22	04/07/22
10	220007	Project H	Tier 2	Amber	Not on Tracker	£ 7,500.00	£ 9,000.00	-£ 1,500.00	01/06/22	02/09/22
11	220008	Project I	Tier 2	Green	Yes	£ 4,000.00	£ 3,400.00	£ 600.00	05/05/22	02/11/22
12	220009	Project J	Tier 2	Red	No	£ 18,600.00	£ 20,000.00	-£ 1,400.00	03/01/22	30/06/22
13	220010	Project K	Tier 3	No RAG (Tier 3)	Not on Tracker	£ 32,000.00	£ -	£ 32,000.00	23/09/22	10/02/23
Project Overview

Cell Formulas
Range	Formula
D3:D13	D3	=XLOOKUP([@[Project '#]],Master!$A$2:$A$12,TblRAGScore[Check])
H3:H13	H3	=IFERROR([@[Approved Budget]]-[@[Actual Spend]]," ")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:D13	Expression	=$D3="No RAG (Tier 3)"	text	NO
D3:D13	Expression	=$D3="Green"	text	NO
D3:D13	Expression	=$D3="Red"	text	NO
D3:D13	Expression	=$D3="Amber"	text	NO
E3:E13	Expression	=$E3="Not on Tracker"	text	NO
E3:E13	Expression	=$E3="No"	text	NO
E3:E13	Expression	=$E3="Yes"	text	NO
G3:G6	Expression	=ISBLANK	text	NO
G3:G6	Expression	=$G3>$F3	text	NO
G3:G6	Expression	=$G3<=$F3	text	NO
B3:B13	Cell Value	duplicates	text	NO
H3:H13	Cell Value	<-1000	text	NO
B7:B13	Cell Value	duplicates	text	NO
J3:J13	Expression	=AND($J3>TODAY(), $J3-TODAY()<=30)	text	NO
J3:J13	Expression	=$J3<TODAY()	text	NO
J3:J13	Expression	=$J3=TODAY()+30	text	NO

Master

Project Status Summary v0.2 - test2.xlsm
	A	B	C	D	E	F	G	H		J	K	L	M
1	ID	Project	Tier	Tracker	Budget	Date	Score	RAG		Check2	Score	Criteria	RAG
2	220000	Project A	Tier 2	1	1	1	3	Green		Green	3	All 3 Criteria Matched	Green
3	220001	Project B	Tier 3	2	1	2	5	No RAG (Tier 3)		Amber	4	Total 4 Points	Amber
4	220002	Project C	Tier 1	2	1	1	4	Amber		Amber	5	Total 5 Points	Amber
5	220003	Project D	Tier 2	2	1	1	4	Amber		Amber	6	No Criteria Matched	Red
6	220004	Project E	Tier 1	1	1	2	4	Amber		Amber	Exception	TIER 3 Project	No RAG (Tier 3)
7	220005	Project F	Tier 1	2	1	1	4	Amber		Amber
8	220006	Project G	Tier 2	1	2	2	5	Amber		Amber
9	220007	Project H	Tier 2	1	2	1	4	Amber		Amber
10	220008	Project I	Tier 2	1	1	1	3	Green		Green
11	220009	Project J	Tier 2	2	2	2	6	Red		Red
12	220010	Project K	Tier 3	1	1	1	3	No RAG (Tier 3)		Green
Master

Cell Formulas
Range	Formula
A2:A12	A2	=UNIQUE(TblSummary[Project '#])
B2:B12	B2	=XLOOKUP(A2,TblSummary[Project '#],TblSummary[Project Name])
C2:C12	C2	=XLOOKUP(A2,TblSummary[Project '#],TblSummary[Project Type])
D2:D12	D2	=SWITCH('Project Overview'!E3,"Yes", "1", "Not on Tracker","1","No","2")
E2:E12	E2	=IF('Project Overview'!H3<-1000,"2","1")
F2:F12	F2	=IF('Project Overview'!J3<=TODAY(),"2","1")
G2:G12	G2	=D2+E2+F2
H2:H12	H2	=IF([@Tier]="Tier 3","No RAG (Tier 3)",SWITCH(IFNA(XLOOKUP(A2,$A$2:$A$12,[Score]),""),$K$2,$M$2,$K$3,$M$3,$K$4,$M$4,$K$5,$M$5,""))
J2:J12	J2	=SWITCH([@Score],$K$2,$M$2,$K$3,$M$3,$K$4,$M$4,$K$5,$M$5,$K$6,$M$6)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
rngBudgetsc	=TblRAGScore[Budget]	G2
rngDatescr	=TblRAGScore[Date]	G2
rngRAGSc	=TblRAGScore[Score]	H2:H12, J2
rngRATSc	=TblRAGScore[Tracker]	G2
rngTotalscr	=TblRAGScore[Score]	H2:H12, J2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M2:M6	Expression	=$M2="No RAG (Tier 3)"	text	NO
M2:M6	Expression	=$M2="Red"	text	NO
M2:M6	Expression	=$M2="Amber"	text	NO
M2:M6	Expression	=$M2="Green"	text	NO
H2:H12	Expression	=$H2="No RAG (Tier 3)"	text	NO
H2:H12	Expression	=$H2=$M$3	text	NO
H2:H12	Expression	=$H2=$M$5	text	NO
H2:H12	Expression	=$H2=$M$2	text	NO
G2:G12	Expression	=$G2=3	text	NO
G2:G12	Expression	=$G2=4	text	NO
G2:G12	Expression	=$G2=5	text	NO
G2:G12	Expression	=$G2=6	text	NO

Cells with Data Validation
Cell	Allow	Criteria
M2:M6	List	=rngRAG
K2:K5	List	=rngRAGCnt

 

[SanjayGMusafir]

Instead of -

=SWITCH('Project Overview'!E3,"Yes", "1", "Not on Tracker","1","No","2")

Try This in cell D3 of Master -

=Let(LUU,XLOOKUP($A2,'Project Overview'!A:A,'Project Overview'!E:E),IFS(LUU="Yes","1",LUU="Not on Tracker","1",LUU="No","2",True,"")