Combining LET with IF

[Wishmaster89]

Hi All

I'm trying to combine LET with IF but i'm having some trouble. It seems like when i get to the false bit of the IF, the LET statement is out of scope so i can't do a name value 2/name 2 calculation.

In column E, I currently have xlookup and if it doesn't return 0 it performs the original xlookup, which is why i want to use LET to avoid writing out the xlookup twice

This is what i have tried so far , the second function i refer to below is XLOOKUP(A6&C6,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6))),XLOOKUP(A6&C6,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6)

=if(let(a,xlookup(A2&B2,$M$2:$M$6&$N$2:N6,$Q$2:$Q$6))<>0,a,[B]b(2nd function)[/B],

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ID	Start date	Course ID	Course	Grade								ID	Start date	Course ID	Course	Grade
2	1	01/03/2019	33	Maths	B								2	15/10/2020	55	English	F
3	2	15/10/2020	55	English	F								4	28/09/2021	75	Geography	A
4	3	07/10/2021	66	IT	U								3	03/06/2018	66	IT	U
5	4	28/09/2021	75	Geography	A								1	01/03/2019	33	Maths	B
6	7	01/03/2021	12	Sports	FALSE								7	01/03/2021	12	Sports
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=IFERROR(IF(XLOOKUP(A2&B2,$M$2:$M$6&$N$2:$N$6,$Q$2:$Q$6)<>0,XLOOKUP(A2&B2,$M$2:$M$6&$N$2:$N$6,$Q$2:$Q$6,XLOOKUP(A2&C2,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6))),XLOOKUP(A2&C2,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6))

 

[Fluff]

The contsruct is variable calculation, variable calculation etc & then the final formula.

 

[Wishmaster89]

Ahh i see. thanks @Mehmet23 and @jasonb75 both solutions work and make sense to me.

 

[Wishmaster89]

Thanks again

 

[Wishmaster89]

The contsruct is variable calculation, variable calculation etc & then the final formula.

Got it, thanks again Fluff

 

[Fluff]

Glad we could help & thanks for the feedback.