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]

You need to put the IF inside the LET

=LET(a,XLOOKUP(A2&B2,$M$2:$M$6&$N$2:$N$6,$Q$2:$Q$6,0),IF(a<>0,a,XLOOKUP(A2&C2,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6,"")))

 

[Wishmaster89]

Thanks! @Fluff

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Wishmaster89]

One more question, say i wanted to call the second xlookup "b", is it possible? If i try to put B below i get an error saying too few arguments. I mean its not essential, your original answer does the job, just curious.

=LET(a,XLOOKUP(A2&B2,$M$2:$M$6&$N$2:$N$6,$Q$2:$Q$6,0),B,IF(a<>0,a,XLOOKUP(A2&C2,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6,"")))

 

[Fluff]

You would need to put the xlookup after the b.

 

[Wishmaster89]

=LET(a,XLOOKUP(A2&B2,$M$2:$M$6&$N$2:$N$6,$Q$2:$Q$6,0),IF(a<>0,a,b,XLOOKUP(A2&C2,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6,"")))

Sorry i'm not sure where though? Ifi put it where it is now , it messes up the logic of the IF.

 

[Wishmaster89]

=LET(a,XLOOKUP(A2&B2,$M$2:$M$6&$N$2:$N$6,$Q$2:$Q$6,0),IF(a<>0,a),b,XLOOKUP(A2&C2,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6,"")))

This also doesn't work, i get a LET parameter error.

 

[Mehmet23]

Hi Try it if your problem is not solved

LET(a;XLOOKUP(A2&B2,$M$2:$M$6&$N$2:$N$6,$Q$2:$Q$6);b;XLOOKUP(A2&C2,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6);IFERROR(IF(a<>0,a,b)),b))

 

[jasonb75]

You need to define b, then add the formula part separately.

=LET(a,XLOOKUP(A2&B2,$M$2:$M$6&$N$2:$N$6,$Q$2:$Q$6,0),b,XLOOKUP(A2&C2,$M$2:$M$6&$O$2:$O$6,$Q$2:$Q$6,""),IF(a<>0,a,b))