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

Question

Level 1

4 points

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

I have a Transactions table, with transactions in the first column. I want to group them into Vendors by somehow matching/looking up a substring of them to values in the Vendors table. I think I need a wildcard expression (or Regex) in the lookup somehow.

I have this

and a lookup table

and I want to create this

I've tried using wildcards, but not been able to get it to work. Any help or suggestion would be welcome.. Exact/specific syntax would be perfect!

MacBook Air (M3, 2024)

Posted on Dec 15, 2025 9:46 AM

Reply

Answer 1

Top-ranking reply

Camelot

Level 9

58,299 points

Dec 15, 2025 3:20 PM in response to DMBKeisha

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()

Reply

Answer 2

DMBKeisha Author

Level 1

4 points

Dec 15, 2025 11:36 AM in response to DMBKeisha

Oops...just noticed the first Transaction record should read 'Detail from Marks and Spencer in Nottingham'

Reply