LAMBDA Function and Using Cell Values as Ranges

[chuckjitsu]

Hello. I've been playing around with LAMBDA via 365. I came up with the below, which checks two ranges to see if they intersect. In this particular case with the used parameter values, this resolves to True, meaning no intersect.
No problem there. Now let's say cell A1 contains the value for x, in this case the text A1:E1. What syntax would I use so that the x value below in the second function would refer to the value in cell A1, in this case A1:E1, not the address of A1? Basically, x needs to be populated with a cell value, not a cell address.


=LAMBDA(x,y,ISERROR(x y))(A1:E1,C2:C13)

=LAMBDA(x,y,ISERROR(x y))

 

[Fluff]

How about

=LAMBDA(x,y,ISERROR(x y))(INDIRECT(A1),C2:C13)

 

[chuckjitsu]

=LAMBDA(x,y,ISERROR(x y))(INDIRECT(A1),C2:C13)

Hi Fluff. I played around a bit more after posting my question and discovered that INDIRECT seemed to work. That said, I appreciate the response and will mark this one as resolved. Thanks again!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[chuckjitsu]

You're welcome & thanks for the feedback.

Hi again Fluff. I have a question related to this, which I can create a new thread for if need be. Relative to the formula below, the value in A1 = A1:E1. B1 = B1:B4. B2 = B2:E2 B3 = B2:B5

Based on those values, the returned results are below, which are correct as it looks for intersects between A1:E1 and the other range values listed above. Where it fails is when it compares ranges that overlap instead of merely intersecting. For example, instead of Cell B1 equaling B1:B4, let's say it equals B1:D1. Now it returns the #CALC! (Nested Arrays) error. It's the same result if the ranges are vertical instead of horizontal and overlap.

Any thought on how to fix this so that the error value isn't thrown in the scenario I described?

Correct result:
FALSE
TRUE
TRUE

Error result:
#CALC!

=BYROW(B1:B3,LAMBDA(r, ISERROR(INDIRECT(r) INDIRECT(A1))))

 

[Fluff]

You would need to do it like

=BYROW(B1:B3,LAMBDA(r,SUM(--ISERROR(INDIRECT(r) INDIRECT(A1)))>0))

 

[chuckjitsu]

You would need to do it like

=BYROW(B1:B3,LAMBDA(r,SUM(--ISERROR(INDIRECT(r) INDIRECT(A1)))>0))

=BYROW(B1:B2,LAMBDA(r,SUM(--ISERROR(INDIRECT(r) INDIRECT(A1)))>0))

I used the above where A1 still equals A1:E1. For my test, B1 = B1:E1 and B2 = A2:A5. The expected result would be

False
True

but the result returned was 0. So, no CALC error, but the results weren't what I was expecting either.

 

[Fluff]

The expected result would be

False
True

That's exactly what I get, where is the formula?

 

[chuckjitsu]

That's exactly what I get, where is the formula?

Originally, I entered the formula in cell K1, but tried other cells and still only got 0 as a result. What cell did you enter the formula in?

 

[Fluff]

Any cell that is not in the ranges in A1, B1:B2

Fluff.xlsm
	A	B	C	D
1	a1:e1	b1:e1
2		a2:a5
3
4				FALSE
5				TRUE
6
Master

Cell Formulas
Range	Formula
D4:D5	D4	=BYROW(B1:B2,LAMBDA(r,SUM(--ISERROR(INDIRECT(r) INDIRECT(A1)))>0))
Dynamic array formulas.