First time here? Check out the FAQ!

1 | initial version |

Hello,

from my perspective you may fail due to leaving off the third parameter of function `MATCH`

, which tells MATCH how the data on **search matrix** is ordered.

From Help:

```
Syntax
MATCH(SearchCriterion; LookupArray; Type)
......
Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column in sorted in descending order. This corresponds to the same function in Microsoft Excel.
```

Thus use:
`MATCH("TEXT";Table.A1:A3;0)`

(i.e. in German: `VERGLEICH("TEXT";Table.A1:A3;0)`

)

**Notice:** To match ordered data is faster than unordered data

Hope that helps.

2 | No.2 Revision |

Hello,

from my perspective you may fail due to leaving off the third parameter of function `MATCH`

, which tells MATCH how the data on **search matrix** is ordered.

From Help:

```
Syntax
MATCH(SearchCriterion; LookupArray; Type)
......
Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column in sorted in descending order. This corresponds to the same function in Microsoft Excel.
```

This means, that your formula will only work, if the data in `Table.A1:A2`

are sorted.

Thus ~~use:
~~use (to be safe):

`MATCH("TEXT";Table.A1:A3;0)`

(i.e. in German: `VERGLEICH("TEXT";Table.A1:A3;0)`

)

**Notice:** To match ordered data is faster than unordered data

Hope that helps.

3 | No.3 Revision |

Hello,

from my perspective you may fail due to leaving off the third parameter of function `MATCH`

, which tells MATCH how the data on **search matrix** is ordered.

From Help:

```
Syntax
MATCH(SearchCriterion; LookupArray; Type)
......
Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column in sorted in descending order. This corresponds to the same function in Microsoft Excel.
```

This means, that your formula will only work, if the data in

are sorted. ~~Table.A1:A2~~Table.A1:A3

Thus use (to be safe):

`MATCH("TEXT";Table.A1:A3;0)`

(i.e. in German: `VERGLEICH("TEXT";Table.A1:A3;0)`

)

**Notice:** To match ordered data is faster than unordered data

Hope that helps.

4 | No.4 Revision |

Hello,

`MATCH`

, which tells MATCH how the data on **search matrix** is ordered.

From Help:

```
Syntax
MATCH(SearchCriterion; LookupArray; Type)
......
Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column in sorted in descending order. This corresponds to the same function in Microsoft Excel.
```

This means, that your formula will only work, if the data in `Table.A1:A3`

are sorted.

Thus use (to be safe):

(i.e. in German: ~~MATCH("TEXT";Table.A1:A3;0)~~MATCH("TEXT";Table.$A$1:$A$3;0)

)~~VERGLEICH("TEXT";Table.A1:A3;0)~~VERGLEICH("TEXT";Table.$A$1:$A$3;0)

**Notice:** To match ordered data is faster than unordered data

Hope that helps.

**Update** Fixed the search matrix, using `$`

.

5 | No.5 Revision |

Hello,

`MATCH`

, which tells MATCH how the data on **search matrix** is ordered.

From Help:

```
Syntax
MATCH(SearchCriterion; LookupArray; Type)
......
Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column in sorted in descending order. This corresponds to the same function in Microsoft Excel.
```

This means, that your formula will only work, if the data in `Table.A1:A3`

are sorted.

Thus use (to be safe):

`MATCH("TEXT";Table.$A$1:$A$3;0)`

(i.e. in German: `VERGLEICH("TEXT";Table.$A$1:$A$3;0)`

)

**Notice:** To match ordered data is faster than unordered data

Hope that helps.

**Update** Fixed the search ~~matrix, ~~matrix (i.e. LookupArray), using `$`

~~.~~

6 | No.6 Revision |

Hello,

`MATCH`

, which tells MATCH how the data on **search matrix** is ordered.

From Help:

```
Syntax
MATCH(SearchCriterion; LookupArray; Type)
......
Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column in sorted in descending order. This corresponds to the same function in Microsoft Excel.
```

This means, that your formula will only work, if the data in `Table.A1:A3`

are sorted.

Thus use (to be safe):

`MATCH("TEXT";Table.$A$1:$A$3;0)`

(i.e. in German: `VERGLEICH("TEXT";Table.$A$1:$A$3;0)`

)

**Notice:** To match ordered data is faster than unordered data

Hope that helps.

**Update** Fixed the search matrix (i.e. LookupArray), using `$`

to avoid shifting while applied to ~~an ~~a range.

7 | No.7 Revision |

Hello,

`MATCH`

, which tells MATCH how the data on **search matrix** is ordered.

From Help:

```
Syntax
MATCH(SearchCriterion; LookupArray; Type)
......
Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column in sorted in descending order. This corresponds to the same function in Microsoft Excel.
```

This means, that your formula will only work, if the data in `Table.A1:A3`

are sorted.

Thus use (to be safe):

`MATCH("TEXT";Table.$A$1:$A$3;0)`

(i.e. in German: `VERGLEICH("TEXT";Table.$A$1:$A$3;0)`

)

**Notice:** To match ordered data is faster than unordered data

Hope that helps.

** UpdateUpdate 1** Fixed the search matrix (i.e. LookupArray), using

`$`

to avoid shifting while applied to a range.**Update 2** Please see reworked file

8 | No.8 Revision |

Hello,

`MATCH`

, which tells MATCH how the data on **search matrix** is ordered.

From Help:

```
Syntax
MATCH(SearchCriterion; LookupArray; Type)
......
Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column in sorted in descending order. This corresponds to the same function in Microsoft Excel.
```

This means, that your formula will only work, if the data in `Table.A1:A3`

are sorted.

`MATCH("TEXT";Table.$A$1:$A$3;0)`

(i.e. in German: `VERGLEICH("TEXT";Table.$A$1:$A$3;0)`

)

**Notice:** To match ordered data is faster than unordered data

Hope that helps.

**Update 1** Fixed the search matrix (i.e. LookupArray), using `$`

to avoid shifting while applied to a range.

**Update 2** Please see reworked file

9 | No.9 Revision |

Hello,

`MATCH`

, which tells MATCH how the data on **search matrix** is ordered.

From Help:

```
Syntax
MATCH(SearchCriterion; LookupArray; Type)
......
Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column in sorted in descending order. This corresponds to the same function in Microsoft Excel.
```

This means, that your formula will only work, if the data in `Table.A1:A3`

are sorted.

`MATCH("TEXT";Table.$A$1:$A$3;0)`

(i.e. in German: `VERGLEICH("TEXT";Table.$A$1:$A$3;0)`

)

**Notice:** To match ordered data is faster than unordered data

Hope that helps.

**Update 1** Fixed the search matrix (i.e. LookupArray), using `$`

to avoid shifting while applied to a range.

**Update 2** Please see reworked file

C:\fakepath\BedingteFormatierungTEAMs.ods

**Update 3** Screenshot of final version (all in a single condition)

10 | No.10 Revision |

Hello,

`MATCH`

, which tells MATCH how the data on **search matrix** is ordered.

From Help:

```
Syntax
MATCH(SearchCriterion; LookupArray; Type)
......
Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is missing, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column in sorted in descending order. This corresponds to the same function in Microsoft Excel.
```

This means, that your formula will only work, if the data in `Table.A1:A3`

are sorted.

`MATCH("TEXT";Table.$A$1:$A$3;0)`

(i.e. in German: `VERGLEICH("TEXT";Table.$A$1:$A$3;0)`

)

**Notice:** To match ordered data is faster than unordered data

Hope that helps.

**Update 1** Fixed the search matrix (i.e. LookupArray), using `$`

to avoid shifting while applied to a range.

**Update 2** Please see reworked file

~~C:\fakepath\BedingteFormatierungTEAMs.ods~~-- deleted --

**Update 3** Screenshot of final version (all in a single condition)

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.