Search a value in a table partially matching

Sintra950 

Yes, we search in a column value by value. SEARCH itself returns the spill, we take from it first matched position. Since you are on 365 it could be modified a bit

=XLOOKUP(
    TRUE,
    ISNUMBER(SEARCH(E4, previous)),
    current,
    XLOOKUP(E4, current, current, "no such")
)

Or you may generate the spill with all results at once

=SCAN(
    "",
    value,
    LAMBDA(_,v,
        XLOOKUP(
            TRUE,
            ISNUMBER(SEARCH(v, previous)),
            current,
            XLOOKUP(v, current, current, "no such")
        )
    )
)

Please check in attached file