Ask Your Question

Revision history [back]

click to hide/show revision 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.

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.

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:A2Table.A1:A3 are sorted.

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.

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:A3 are sorted.

Thus use (to be safe):
MATCH("TEXT";Table.A1:A3;0)MATCH("TEXT";Table.$A$1:$A$3;0) (i.e. in German: 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 $.

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: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 $.

to avoid shifting while applied to an range.

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: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.

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: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

C:\fakepath\BedingteFormatierungTEAMs.ods

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: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 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

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: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 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) image description

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: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 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) image description

C:\fakepath\BedingteFormatierungTEAMs.ods