Formula using If statement with Xmatch

[dharv3y78]

Hi Mr Excel Members,

I have a problem which i hope you vcan help me with i have attached a sample workbook if the issue i am having.

I have a Formula in where if Alt' day be taken In Leiu of a Public Holiday (B3) equals No in N4 i have the following formula below in which it check E4:E15 & F4:F15 and checks to see if they are in K4 & L4 down and if it is the N4 is blank and if not it puts the date from L4.

=IF(ISNA(XMATCH([@State]&[@Date],Table1[Jurisdiction]&Table1[Date])),[@Date],"")

The issue that i have is in the section Formula I Want in to have the the above but also if Alt' day be taken In Leiu of a Public Holiday (B3) equals Yes then i want it to read which State Public Holiday being taken In Lieu of in B4 and add that holiday back in eg: Lets say it Holiday 1 for this shown i N4 as blank but is added back in on T4, and that it now reads and takes that alternate date in A16.

I'm sure i have just alter the above formula a litte but i have not worked out the correct formula as what i have tried has failed.

Hope this is clear enough for you Workbook attached.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Formula Currently			Public Holiday					What I Have		School Holidays				What I Want Also			School Holidays
2
3	Alt' day be taken In Leiu of a Public Holiday	NO			Jurisdiction	Date	Holiday Name	Day		Jurisdiction	State	Date	Day	Date for Field		Jurisdiction	State	Date	Day	Date for Field
4	State Public Holiday being taken In Lieu of				SA	1/1/24	Holiday 1	Monday		SA	SA	1/1/24	Monday			SA	SA	1/1/24	Monday	1/1/24
5					SA	1/26/24	Holiday 2	Friday		SA	SA	1/2/24	Tuesday	1/2/24		SA	SA	1/2/24	Tuesday	1/2/24
6					SA	3/11/24	Holiday 3	Monday		SA	SA	1/3/24	Wednesday	1/3/24		SA	SA	1/3/24	Wednesday
7	Alternative Public Holiday Dates				SA	3/29/24	Holiday 4	Friday		SA	SA	1/4/24	Thursday	1/4/24		SA	SA	1/4/24	Thursday	1/4/24
8	Date	Holiday Name			SA	3/30/24	Holiday 5	Saturday		SA	SA	1/5/24	Friday	1/5/24		SA	SA	1/5/24	Friday	1/5/24
9					SA	3/31/24	Holiday 6	Sunday		SA	SA	1/6/24	Saturday	1/6/24		SA	SA	1/6/24	Saturday	1/6/24
10					SA	4/1/24	Holiday 7	Monday		SA	SA	1/7/24	Sunday	1/7/24		SA	SA	1/7/24	Sunday	1/7/24
11					SA	4/25/24	Holiday 8	Thursday		SA	SA	1/8/24	Monday	1/8/24		SA	SA	1/8/24	Monday	1/8/24
12					SA	6/10/24	Holiday 9	Monday		SA	SA	1/9/24	Tuesday	1/9/24		SA	SA	1/9/24	Tuesday	1/9/24
13					SA	10/7/24	Holiday 10	Monday		SA	SA	1/10/24	Wednesday	1/10/24		SA	SA	1/10/24	Wednesday	1/10/24
14	Alternative Public Holiday Dates				SA	12/25/24	Holiday 11	Wednesday		SA	SA	1/11/24	Thursday	1/11/24		SA	SA	1/11/24	Thursday	1/11/24
15	Date	Holiday Name			SA	12/26/24	Holiday 12	Thursday		SA	SA	1/12/24	Friday	1/12/24		SA	SA	1/12/24	Friday	1/12/24
16	Wednesday, 3 January 2024	Holiday 13
17
18
19
Sheet1

Cell Formulas
Range	Formula
N4:N15	N4	=IF(ISNA(XMATCH([@State]&[@Date],Table1[Jurisdiction]&Table1[Date])),[@Date],"")

 

[dharv3y78]

Hi All,

Just wondering if anyone is able to help me with this issue.

Thanks in advance
Donald