Is there a way to populate a dropdown category in Google Sheets using wildcard matches from another table?

Question

I'm trying to build an expenses sheet to categorize expenses. I have a sheet like this:

Category	Date	Description	Amount
	Jan 1	Lowes Purchase	$2.00
	Jan 2	Walmart Purchase	$3.43

I have another tab where Category is sourced from a dropdown of values (Column A).

A	B	C	D	E	F
Construction	Home Depot	Lowes
Grocery	Walmart	Target	Krogers	Publix	Whole Foods
Restaurant	Burger King	McDonald's	Wendy's

I am trying to build a formula that would auto-populate the Category value in the first table if there is ANY match of the values to the right of the Category. For example, the row Lowes Purchase would automatically update to Construction and the second row would automatically set to Grocery because of the Walmart wildcard match. Is this possible?

I've tried a contains but it only works with one value. Not sure if it's possible to do multiple values, and also have "up to" and entire row of values. Each row could have a different # of possible matches.

Answer 1

You may try-

=FILTER($A$10:$A$12,BYROW($B$10:$F$12,LAMBDA(x,OR(INDEX(x=INDEX(SPLIT(C2," "),1,1))))))

To refer another sheet, use-

=FILTER(Sheet2!$A$1:$A$12,BYROW(Sheet2!$B$1:$F$12,LAMBDA(x,OR(INDEX(x=INDEX(SPLIT(C2," "),1,1))))))