xlookup extender

[iingLK]

untuk menghasilkan dynamic array

[taruma]

Solusi sekarang menggunakan recursive. Berikut fungsinya:

RECURSIVE_LOOKUP = LAMBDA(
    ntry,
    lookup_value,
    lookup_vector,
    return_array,
    [if_not_found],
    [match_mode],
    [search_mode],
    LET(
        lookup_value, TOCOL(lookup_value),
        LET(
            selected_value, VALUE(
                ARRAYTOTEXT(CHOOSEROWS(lookup_value, ntry))
            ),
            result, XLOOKUP(
                selected_value,
                lookup_vector,
                return_array,
                if_not_found,
                match_mode,
                search_mode
            ),
            IF(
                ntry = 1,
                result,
                VSTACK(
                    RECURSIVE_LOOKUP(
                        ntry - 1,
                        lookup_value,
                        lookup_vector,
                        return_array,
                        if_not_found,
                        match_mode,
                        search_mode
                    ),
                    result
                )
            )
        )
    )
);

XLOOKUP_VECTOR = LAMBDA(
    lookup_value,
    lookup_vector,
    return_array,
    [if_not_found],
    [match_mode],
    [search_mode],
    LET(
        lookup_value, TOCOL(lookup_value),
        ntry, ROWS(lookup_value),
        RECURSIVE_LOOKUP(
            ntry,
            lookup_value,
            lookup_vector,
            return_array,
            if_not_found,
            match_mode,
            search_mode
        )
    )
);