Embedding IF in single cell formula

[sharshra]

I have a formula working well, but is failing when I embed it in a single cell formula. Something is going wrong. Can the experts help please?

Table 1 has name, C & L values. Zone is derived from a reference table. The column 'both' will be populated if columns C & L are populated. I´m getting the correct values in 'both' column with the IF formula. I would like to write single cell formula to get the values for columns 'zone' & 'both'. Formula for 'zone' is working correctly. But, when I embed the formula for 'both', it is throwing error.

Table 1:

excel problems.xlsx
	AE	AF	AG	AH	AI
2	name	C	L	zone	both
3	ffafds	ffafds	ffafds	north	ffafds
4	qewq		qewq	west
5	tyery	tyery	tyery	east	tyery
6	ryut	ryut		south
7	hjg	hjg	hjg	south	hjg
8	zvzvc	zvzvc		west
9	vnc	vnc		north
10	gsdg		gsdg	east
11	fhd	fhd	fhd	east	fhd
12	fjgg	fjgg	fjgg	south	fjgg
13	lghl		lghl	north
Sheet2

Cell Formulas
Range	Formula
AH3:AH13	AH3	=LET( n,$AE$3:$AE$13,c,$AF$3:$AF$13,l,$AG$3:$AG$13,nRef,$AP$3:$AP$13,zRef,$AQ$3:$AQ$13, z,MAP(n,LAMBDA(x,XLOOKUP(x,nRef,zRef))), both,MAP(n,LAMBDA(x,IF(LEN(c)*LEN(l)>0,n,""))), z)
AI3:AI13	AI3	=IF(LEN($AF$3:$AF$13)*LEN($AG$3:$AG$13)>0,$AE$3:$AE$13,"")
Dynamic array formulas.

Single cell formula for the derived values:

excel problems.xlsx
	AK
3	#CALC!
Sheet2

Cell Formulas
Range	Formula
AK3	AK3	=LET( n,$AE$3:$AE$13,c,$AF$3:$AF$13,l,$AG$3:$AG$13,nRef,$AP$3:$AP$13,zRef,$AQ$3:$AQ$13, z,MAP(n,LAMBDA(x,XLOOKUP(x,nRef,zRef))), both,MAP(n,LAMBDA(x,IF(LEN(c)*LEN(l)>0,n,""))), HSTACK(z,both))

Reference table:

excel problems.xlsx
	AP	AQ
2	name	zone
3	ffafds	north
4	qewq	west
5	tyery	east
6	ryut	south
7	hjg	south
8	zvzvc	west
9	vnc	north
10	gsdg	east
11	fhd	east
12	fjgg	south
13	lghl	north
Sheet2

 

[hagia_sofia]

Hello, why not just:

=LET(
n,$AE$3:$AE$13,c,$AF$3:$AF$13,l,$AG$3:$AG$13,nRef,$AP$3:$AP$13,zRef,$AQ$3:$AQ$13,
z,MAP(n,LAMBDA(x,XLOOKUP(x,nRef,zRef))),
both,IF(LEN(c)*LEN(l)>0,n,""),
HSTACK(z,both))

 

[Cubist]

Try:

Book2
	AE	AF	AG	AH	AI
1
2	name	C	L	zone	both
3	ffafds	ffafds	ffafds	north	ffafds
4	qewq		qewq	west
5	tyery	tyery	tyery	east	tyery
6	ryut	ryut		south
7	hjg	hjg	hjg	south	hjg
8	zvzvc	zvzvc		west
9	vnc	vnc		north
10	gsdg		gsdg	east
11	fhd	fhd	fhd	east	fhd
12	fjgg	fjgg	fjgg	south	fjgg
13	lghl		lghl	north
Sheet1

Cell Formulas
Range	Formula
AH3:AI13	AH3	=LET(n,AE3:AE13,c,AF3:AF13,l,AG3:AG13,HSTACK(XLOOKUP(n,AP3:AP13,AQ3:AQ13),IF(LEN(c)*LEN(l)>0,n,"")))
Dynamic array formulas.

 

[sharshra]

Thanks, @hagia_sofia, @Cubist. I just overlooked using MAP!!! Thanks for pointing out