How to do a lookup based on a sub string of what is in a cell?

This is possible using a regular expression.

First, you have to combine all your potential vendors into a list, which you can do via TEXTJOIN() function:

=TEXTJOIN("|",TRUE,Vendors::A)

Will result in something like:

Aldi|Boots|Marks and Spencer

(the | character is used by regular expressions to separate parts of the query).

This can then be passed into REGEX.EXTRACT() to perform the search. In cell B2, set the formula to:

=REGEX.EXTRACT(A,TEXTJOIN("|",1,Vendors::A),1)

This will look at the values in the Transaction column (column A) of this table, and run a regular expression comparison using the list of vendors extracted via TEXTJOIN()

Just be aware that the results might not be perfect. You might get false hits. It will take more work to reduce/eliminate the false hits. For example, "aldi" will return a hit for any string that contains the letters "aldi" in that order, such as "Scalding pot from Walmart". Instead of searching for "aldi", you would need to search for "^aldi | aldi | aldi$" to find "aldi" as a separate word at the beginning, within, or end of the string. And same for the other vendors. If you add a new column to the Vendor table, you can create these search strings for each of your vendors and use that column in the TEXTJOIN instead of column A. You can hide the new column after it is all set up.

formula in B2 ="^"&A2&" | "&A2&" | "&A2&"$"

Of course, you can still have problems if your transactions have things like "Boots from Walmart" because it won't be able to tell if the vendor is Boots or Walmart. A solution to that is to include the string "from " three times in the formula given above so the search will be "from Walmart" not just "Walmart".

Another and much easier solution to all that is to have all of your transactions end in "from vendor" so all you have to do is search for " from vendor$". The formula in B2 would then be =" from "&A2"&"$"

deleted. too much going on here today and I am writing gibberish