Google Sheets: VLOOKUP to do "contains" instead of "equals"

I have the following formula in my main sheet:

=ARRAYFORMULA(IF(B2:B="", "", IFERROR(VLOOKUP(B2:B
, Reference!A:B, 2, FALSE), "Unknown")))

This works if there's an exact match ("equals") between the main sheet value and one of the values of column A on the Reference sheet.

However, I want it to be enough if the value of the Reference sheet is contained in the value from the mainsheet.

Example:

* Main Sheet:

Foo
Bar

* Reference Sheet:

Foo <- should match (Foo) (this case works already)
Fo <- should also match (Foo)
ar <- should also match (Bar)
Go <- shouldn't match

Can you please help? 😄

Solution

Can you test this one out:

=map(D:D,lambda(Σ,if(Σ="",,let(Λ,sort(A:B,len(A:A),),ifna(+filter(index(Λ,,2),len(index(Λ,,1)),not(iserror(search(index(Λ,,1),Σ)))),"Unknown")))))

Adapt to your sheet ranges accordingly