Problem

Trying to create a formula that gets me to the result of cell G3 without the intermediary step of having 5 formulas (i.e. E3:E8) for each row of my Data Set

excel 

E3=EVAL_FORMULA(CONCAT(VALUETOTEXT(C3,1), A3, VALUETOTEXT(B3,1)))
G3=AND(E3:E8)

EVAL_FORMULA refers to =LAMBDA(x, EVALUATE(x)) in Name Manager

Data Set

'Calc Detail' Data

Methods Tested

---

Attempt #1

Result: Success when Using Direct Ranges but Fails (i.e. #VALUE! error) when Using Named Ranges with LET()/TOCOL()

Using Direct Ranges

excel 
        
I3=MAP(A3:A8,B3:B8,C3:C8,LAMBDA(s,val,input,
COUNTIF(input,s&val)))
        
K3=AND(MAP(A3:A8,B3:B8,C3:C8,LAMBDA(s,val,input,
COUNTIF(input,s&val))))

Using Named Ranges with LET()/TOCOL()

excel

I12=LET(
    ss,     A12:A17,
    vals,   B12:B17,
    inputs, TOCOL('Calc Detail'!$H23:$M23),
    MAP(
        ss,
        vals,
        inputs,
        LAMBDA(s,val,input,
            COUNTIF(input, s & val)
        )
    )
)

K12=AND(LET(
    ss,     A12:A17,
    vals,   B12:B17,
    inputs, TOCOL('Calc Detail'!$H23:$M23),
    MAP(
        ss,
        vals,
        inputs,
        LAMBDA(s,val,input,
            COUNTIF(input, s & val)
        )
    )
))

---

Attempt #2

Result: Success both when Using Direct Ranges and when Using Named Ranges with LET()/TOCOL()

Using Direct Ranges

excel

M3=MAP(
    A3:A8,
    B3:B8,
    C3:C8,
    LAMBDA(s,val,input,
        SWITCH(
            s,
            ">=", input >= val,
            "=",  input =  val,
            ">",  input >  val,
            "<",  input <  val,
            "<=", input <= val,
            "<>", input <> val,
            "Invalid Operator"
        )
    )
)

O3=AND(
    MAP(
        A3:A8,
        B3:B8,
        C3:C8,
        LAMBDA(s,val,input,
            SWITCH(
                s,
                ">=", input >= val,
                "=",  input =  val,
                ">",  input >  val,
                "<",  input <  val,
                "<=", input <= val,
                "<>", input <> val,
                "Invalid Operator"
            )
        )
    )
)

Using Named Ranges with LET()/TOCOL()

M12=LET(
    ss,     A12:A17,
    vals,   B12:B17,
    inputs, TOCOL('Calc Detail'!$H23:$M23),
    MAP(
        ss,
        vals,
        inputs,
        LAMBDA(s,val,input,
            SWITCH(
                s,
                ">=", input >= val,
                "=",  input =  val,
                ">",  input >  val,
                "<",  input <  val,
                "<=", input <= val,
                "<>", input <> val,
                "Invalid Operator"
            )
        )
    )
)

O12=AND(LET(
    ss,     A12:A17,
    vals,   B12:B17,
    inputs, TOCOL('Calc Detail'!$H23:$M23),
    MAP(
        ss,
        vals,
        inputs,
        LAMBDA(s,val,input,
            SWITCH(
                s,
                ">=", input >= val,
                "=",  input =  val,
                ">",  input >  val,
                "<",  input <  val,
                "<=", input <= val,
                "<>", input <> val,
                "Invalid Operator"
            )
        )
    )
))

• In a prior iteration inputs were defined as TRANSPOSE('Calc Detail'!$H23:$M23) and this caused volatility in the results when filtering (e.g. TRUE results would change to FALSE). Once TRANSPOSE() was changed to TOCOL() the results did not change when filtering on the field.

There can be problems using a named Excel 4 macro inside a LAMBDA function on the worksheet. In your simplified example, I would suggest using the MAP and SWITCH functions instead:

=MAP(
    A2:A7,
    B2:B7,
    C2:C7,
    LAMBDA(s, v, inp,
        SWITCH(
            s,
            ">=", inp >= v,
            "=", inp = v,
            "<=", inp <= v,
            "<>", inp <> v,
            "Invalid Operator"
        )
    )
)