# Match

Match allows you to test whether or not a string is the same as a text value you provide. This can be a specific value, or a a more complex match using Regular Expressions (regex) - a powerful and commonly used form of syntax for searching for patterns of characters e.g. in search engines.

With most tools, match returns a True|False, but in Excel match returns the index within a range of cells of a cell that meets your criteria. This can be combined with an `INDEX` function to reference values in multiple tables, see [General > Lookups/Joins](https://github.com/concentra-analytics/working-with-data/tree/51c414aad5b91e0da2741a07a436476e362ed6ce/strings/General/Join.md).

## Excel

\=\~MATCH!\~(‹target\_string›, ‹within\_array›«, match\_type»)

Match Type Options:

* "0": `MATCH` finds the first value that is exactly equal to ‹target\_string› and returns its index. The values in the ‹within\_array› argument can be in any order.
* "1" or omitted: `MATCH` finds the largest value that is less than or equal to ‹target\_string›  and returns its index. The values in the ‹within\_array› argument must be placed in ascending order, for example: `-1, 0, 1, 2, 3, A-Z, FALSE, TRUE`
* "-1": `MATCH` finds the smallest value that is greater than or equal to ‹target\_string› and returns its index. The values in the ‹within\_array› argument must be placed in descending order, for example: `TRUE, FALSE, Z-A, 3, 2, 1, 0, -1`

```
=MATCH(2.5,A1:A5,1) // Within [-1,0,1,2,3] returns 4

=MATCH(2.5,A1:A5,-1) // Within [3,2,1,0,-1] returns 1

=MATCH(2.5,A1:A5,0) // Within either array returns #N/A
```

## Tableau

Tableau has no Match function so use an `IF()` to test for exact matches.

```
IF [Job Title] = "PA" THEN "Show" ELSE "Hide" END
```

## Alteryx

\~REGEX\_MATCH!\~(‹within\_string›, "‹target\_string›") where the result is only true when the ‹target\_string› exactly matches the ‹within\_string›.

```
REGEX_MATCH(UPPERCASE([Job Title]), "PA")
```

NB. The ‹target\_string› can also be defined using Regular Expressions, or using the **RegEx** tool (in the **Parse** palette): \
&#x20;![](https://3800670130-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M4uObfHWvsn0aAfuifV%2F-M4uOfElaTa53MyrD8KZ%2F-M4uOvhZyjHClj4Mlupx%2FRegex2.PNG?generation=1586896555209102\&alt=media) \
or the **Fuzzy Match** tool (in the **Join** palette): \


![](https://3800670130-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-M4uObfHWvsn0aAfuifV%2F-M4uOfElaTa53MyrD8KZ%2F-M4uOvhaY77K0l0t6brI%2FFuzzyMatch.PNG?generation=1586896555094013\&alt=media) \
which allows you to test for approximate matches based on user-defined or standard-algorithm parameters. \


## OrgVue

In OrgVue, `match()` can be used to find specified strings and also in conjunction with Regular Expressions. \
&#x20;Example 1 - String matching:

```
node.role.value.toUpperCase().match("MANAGE")
// Returns the word "MANAGE" if it appears in the Role value
```

Example 2 - Sophisticated matching of characters:

```
node.costcentre.value.match(/[0-9]/)
// Matches any numbers within the value for Cost Centre
```

Example 3 - Pattern matching:

```
node.ninumber.value.match(/[A-Za-z]{2}\s[0-9]{2}\s[0-9]{2}\s[0-9]{2}\s[A-Za-z]/g)
// Matches for a specific format of NI number, in this case,
// two letters-six numbers (grouped in pairs)-one letter, all separated with spaces
// e.g.: QQ 12 34 56 C
```

NB. For more information about the `match()` syntax, see the summary of Regular Expressions (Regex) [here](https://developer.mozilla.org/en/docs/Web/JavaScript/Guide/Regular_Expressions) or test them out yourself at [regexr.com](http://regexr.com).

## tSQL

Example 1 - String matching: \
&#x20;‹within\_field› \~LIKE!\~ "‹target\_string›"

```
SELECT *
FROM EmployeesData
WHERE JobTitle LIKE "PA"
```

Example 2: \
&#x20;Instead of matching a specified strings, you can also use pattern matching, similar to Regular Expressions - a powerful and commonly used form of syntax for searching for patterns of characters e.g. in search engines. tSGL supports the following 'wildcard characters' in a pattern:

* `%` - match any string of 0+ characters
* `_` - match any single character
* `[]` - match any single character within the range specified in brackets
*   `[^]` match any single character not within the range specified in brackets

    For more examples, go to: [https://msdn.microsoft.com/en-us/library/ms179859.aspx](https://msdn.microsoft.com/en-us/library/ms179859.aspx).

## Python

In Python, matching can be done on specified strings and also in conjunction with Regular Expressions. \
&#x20;Example 1 - String matching: \
&#x20;‹target\_string› \~in!\~ ‹within\_string›

```
"PA" in jobTitle
```

Example 2 - Sophisticated matching of characters: \
&#x20;\~re.search!\~(‹pattern›, ‹field›)

```
import re

matcher = re.search('[A-Za-z]{2}\s[0-9]{2}\s[0-9]{2}\s[0-9]{2}\s[A-Za-z]', niNumber)

# matcher is None if no match, otherwise matcher is a Match object where matcher.group(0) is the matching string
```

NB.:

* This requires the Regular Expressions library to be imported (`import re`).
* The above examples assume variables have been declared for `jobTitle` and `niNumber`. For more information, go to [General > Variables](https://orgvue.gitbooks.io/formula-translator/content/general/variables.html).
