 # [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 ``=MATCH(C2&".*", \$b\$2:\$b\$10000, 0)``
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.