[Calc] Partial word searches, using cell references and regular expressions

So I know I can use regular expressions to do something like :

=MATCH("hello.*", $b$2:$b$10000, 0)

But if i have two collums, b and c, and I want to find if the word in the cell at c is included, whole or partially, in list b. For example:

B            C
Helpxo       Help
12345-075    12345
123          123    

So I want to be able to put a formula in D that gives positive matches for all of those examples. I tried:

=MATCH(C2.*, $b$2:$b$10000, 0) 

But, it didn’t work.

EDIT: Found it here’s the code:

=MATCH(C2&".*", $b$2:$b$10000, 0)

If you mark your answer ‘correct’, others will find the answer, there’s no need to include it in the question :slight_smile:

add: &"." instead of just .

Code requested was:

=MATCH(C2&".*", $b$2:$b$10000, 0)

Hi , I am getting error for the above formula for below search criteria. Please guide.
I Need to find the parital matching cells in C for the ones in B

22252 00ASSS022252000232%

The first parameter you give to the MATCH function is a regular expression, expressed as a string. You can express it using the literal form (e.g. "hello.*" = “hello” + zero or more occurrences of any character), or as a reference to a cell containing a regular expression (e.g. C2), or as a combination (e.g. a concatenation: C2&".*" = the value of C2 followed by zero or more occurrences of any character). This will only match when the sought string is at the beginning of the cell.