VLOOKUP style query with wildcards in range portion

Question

I am working on an automatic tagging project and was wondering if there was a way to search for the first instance of a string contained within the search string:

Tag Table

name contains	tag to apply
A/C	HVAC
Air Conditioner	HVAC
Lights	Lighting
Elevator	Elevator
...

Name table

name	inferred tag
A/C 1	HVAC
A/C 2	HVAC
Elevator 4	Elevator
Elevator 4 Lights	Lighting
...

basically I want the inferred tag to be tag to apply the first time name contains an item in name contains

VLOOKUP almost works but it only supports wildcards in the search item but not the range portion so I can't apply &<name_contains>& wildcards to the Tag Table and do VLOOKUP that way unfortunately

I've done something similar in the past with scripts but I was hoping there was a way to do it with regular functions

One thing I though of doing is transposing the Tag Table and doing a see if each tag table name contains is in the name table name but the sizes of each table caused the sheet to reach the max allowed cells (~40000 names and ~280 tags)

Answer 1

try:

=INDEX(IFERROR(SPLIT(TRIM(FLATTEN(QUERY(TRANSPOSE(IF(REGEXMATCH(D1:D, 
 TRANSPOSE(FILTER(A1:A, A1:A<>""))), 
 TRANSPOSE(FILTER(B1:B&"×", B1:B<>"")), )),,9^9))), "×")),,1)