LET function

[Peter Davison]

I have this formula currently
=LET(a,$DW$13:$DW$1000,b,$EI$13:$EI$1000,ra,ROWS(a),s,SEQUENCE(ra+ROWS(b)),I,IF(s<=ra,INDEX(a,s),INDEX(b,s-ra)),UNIQUE(FILTER(I,I<>0)))

I want to add a third range so I can filter on the range
My third range is - $EH$13:$EH$1000 which I can call c
and I want to filter this range on
c="Ambient"

I tried the following but get an error and I believe I don't have the syntax right in the middle somewhere (I think I need to do something on Rows and Sequence but not sure)
=LET(a,$DW$13:$DW$1000,b,$EI$13:$EI$1000,c,$EH$13:$EH$1000,ra,ROWS(a),s,SEQUENCE(ra+ROWS(b)),I,IF(s<=ra,INDEX(a,s),INDEX(b,s-ra)),UNIQUE(FILTER(I,(I<>0)*(c="Ambient"))))

It you are able to help that would be great

 

[Anbuselvam]

=LET(a,$DW$13:$DW$1000,b,$EI$13:$EI$1000,c,$EH$13:$EH$1000,ra,ROWS(a),s,SEQUENCE(ra+ROWS(b)),I,IF(s<=ra,INDEX(a,s),INDEX(b,s-ra)),UNIQUE(FILTER(I, (I<>0) * (c="Ambient"))))

 

[JvdV]

So you are trying to stack two columns? If so, the c variable is of a completely different range (not the same height) which is a requirement for the `FILTER()` function. For example: if values x and y are in variable a, and the values v and w in variable b, these would stack to 4 values while your variable c only holds 2 >> error.

What exactly do you wish to happen with the 3rd variable? Should avoid both values in variable a and b to be included if not 'Ambient'?

Book1
	A	B	C	D	E
1	a	c	ambient		a
2	b	f	test		c
3	c	g	ambient		g
4	d	h	test
Sheet1

Cell Formulas
Range	Formula
E1:E3	E1	=UNIQUE(TOCOL(FILTER(HSTACK(A1:A4,B1:B4),C1:C4="Ambient")))
Dynamic array formulas.

 

[Fluff]

How about

=LET(a,VSTACK($DW$13:$DW$1000,$EI$13:$EI$1000),UNIQUE(VSTACK(FILTER(a,a<>0),FILTER($EH$13:$EH$1000,$EH$13:$EH$1000="Ambient"))))

 

[Peter Davison]

That works perfectly, thankyou for your help

 

[Fluff]

Glad we could help & thanks for the feedback.