Look Up functions

excel_lambdas_lookups.txt

/*
    It searches for a value inside an array and ouputs a list with all the rows that include it.
    
    Inputs:
        - lookup_value [text]
        - lookup_array [array where the lookup value is searched]
        - value_array [array of values to return]
*/
MULTILOOKUPINCLUDE = LAMBDA(
    lookup_value,
    lookup_array,
    value_array,
    ARRAYTOTEXT(
        UNIQUE(
            FILTER(
                value_array,
                ISNUMBER(SEARCH(lookup_value,lookup_array))
            )
        )
    )
);

/*
    It searches for a value inside an array and ouputs a list with all the rows that include it
    considering a coma separated list and values that may start the same.
    Notes:
        -To be fixed: if "R1" is search in a list like "R2,R12,R1", the function won't find the R1.
    
    Inputs:
        - lookup_value [text]
        - lookup_array [array where the lookup value is searched]
        - value_array [array of values to return]
*/
MULTILOOKUPINCLUDEX =  LAMBDA(lookup_value, lookup_array, value_array,
    IFERROR(
        ARRAYTOTEXT(
            UNIQUE(
                FILTER(
                    value_array,
                    ISNUMBER(SEARCH(lookup_value, lookup_array))*
                    (
                        (LEN(lookup_array)=LEN(lookup_value))+
                        (ISNUMBER(SEARCH(lookup_value&",", lookup_array)))+
                        (LEN(lookup_array)-IFERROR(SEARCH(lookup_value, lookup_array),0)=(LEN(lookup_value)-1))
                    )
                )
            )
        ),""
    )
);

Nice!