Excel (EN)

Using array formulas for text search in Excel

Most of the people are not aware of array formulas in Microsoft Excel. These formulas are primary used to linear algebra matrix operation. Matrix multiplication is a typical example of this type of operation. These mathematical operations are not common in business and mathematicians prefer specialized software like Matlab or Octave to perform their calculations.

However, array formulas can be powerful helper in many other operation. We will show how to use array formula for text search. They could save you from writing VBA macros. I will demonstrate usability of array formulas on a text search example. Finding a substring in a string is a trivial task. But what if we want to search a string for multiple strings? It is much more complex task. Let’s find some practical example.