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 in the Reference sheet.

However, I want it to be able to do a wildcard search - if the value of the reference contains wildcards.

Example (the column Category shows the result I want the formula to give):

Main sheet:

Text           | Category (ideally) 
---------------+--------------------
House          | Houses     
House Post     | Unknown            
PreHouse       | Unknown        
PreHouse Post  | Unknown
Dog2#          | Dogs
Dog2# Post     | Dogs       
PreDog2#       | Unknown
PreDog2# Post  | Unknown
Bird           | Birds
Bird Post      | Birds
PreBird        | Birds
PreBird Post   | Birds
Something Else | Unknown

Reference sheet:

SearchTerm  | FoundCategory
------------+----------------
House       | Houses
Dog*        | Dogs
*Bird*      | Birds

Can you please help? 😄

(This relates to my previous question - thanks for the help there!)