Formulas and Functions
இந்தக் கட்டுப்பாட்டை மாற்றினால் இந்தப் பக்கம் தானாக மாற்றப்படும்

MATCH

The MATCH function returns the position of a value within a collection.

Tip: For an enhanced search that works in any direction, use XMATCH.

MATCH(search-for, search-where, matching-method)

search-for: The value to find. search-for can contain any value or a REGEX string.

search-where: The collection containing the values to be searched. search-where can contain any values.

matching-method: An optional modal value specifying how value matching is performed.

find largest value (1 or omitted): Find the cell with the largest value less than or equal to search-for. If you use find largest value, you can’t use wildcards in search-for, and the values in search-where must be sorted in ascending order.

find value (0): Find the first cell with a value that exactly matches search-for. This matching method is best for locating text. If you use find value, you can use wildcards in search-for. You can use a ? (question mark) to represent one character, an * (asterisk) to represent multiple characters, and a ~ (tilde) to specify that the following character should be matched rather than used as a wildcard.

find smallest value (-1): Find the cell with the smallest value greater than or equal to search-for. If you use find smallest value, you can’t use wildcards in search-for.

Notes

  • MATCH works only on a collection that is part of a single row or column; you can’t use it to search a two-dimensional collection.

  • Cell numbering starts with 1 at the top or left cell for vertical and horizontal collections, respectively. Searches are performed top-to-bottom or left-to-right.

  • When searching for text, case is ignored.

Examples

Given the following table:

A

B

C

1

10

vel

40

2

20

elit

20

3

30

lorex

30

4

40

felis

50

5

50

facit

10

=MATCH(40, A1:A5) returns 4 because 40 (search-for) is the fourth item in the specified collection (search-where).

=MATCH(40, C1:C5) returns 1 because 40 is the first item in the specified collection.

=MATCH(35, C1:C5, 1) returns 3 because 30 is the largest value less than or equal to 35 (matching-method is 1, find largest value).

=MATCH(35, C1:C5, -1) returns 1 because 40 is the smallest value greater than or equal to 35 (matching-method is -1, find smallest value).

=MATCH(35, C1:C5, 0) returns an error because no exact match can be found (matching-method is 0, find value).

=MATCH("vel", B1:B5) returns 1, because "vel" appears in the first cell of the specified range.

=MATCH("*x", B1:B5, 0) returns 3, because "lorex", which ends with an "x", appears in the third cell of the range.

=MATCH("vel", B1:C5) returns an error, because the search-for collection can only include one row or one column.

=MATCH(REGEX("^f.*"), B1:B5,0) returns 4, because the first word that starts with an "f" is the fourth item in the collection.

See alsoLOOKUP
Morty Proxy This is a proxified and sanitized view of the page, visit original site.
உதவியாக இருந்ததா?
எழுத்தின் அளவு: 250
அதிகபட்ச எழுத்தின் அளவு: 250
உங்கள் கருத்திற்கு நன்றி.
Morty Proxy This is a proxified and sanitized view of the page, visit original site.