Функция MATCH в Excel

MATCH — это функция в Excel, используемая для определения положения искомого значения в строке, столбце или таблице. MATCH поддерживает приблизительное и точное совпадение, а также подстановочные знаки (* ?) для частичного совпадения. Часто MATCH сочетается с функцией INDEX для получения значения в совпадающей позиции.

Цель

Получить позицию элемента в массиве.

Возвращаемое значение

Число, представляющее позицию в lookup_array.

Аргументы

  • lookup_value — значение для поиска в lookup_array.
  • lookup_array — диапазон ячеек или ссылка на массив.
  • match_type — [необязательный] 1 = точное или следующее наименьшее (по умолчанию), 0 = точное совпадение, -1 = точное или следующее по величине.

Синтаксис

=MATCH(lookup_value, lookup_array, [match_type])

Примечания по использованию

Функция MATCH используется для определения  положения значения в диапазоне или массиве . Например, на снимке экрана выше формула в ячейке E6 настроена на получение позиции значения в ячейке D6. Функция MATCH возвращает 5, поскольку искомое значение («персик») находится на 5-й позиции в диапазоне B6:B14:

=MATCH(D6,B6:B14,0) // returns 5

Функция MATCH может выполнять точные и приблизительные совпадения и поддерживает  подстановочные знаки (* ?) для частичных совпадений. Существует 3 отдельных режима сопоставления (устанавливаемых аргументом match_type ), как описано ниже.

Примечание: функция MATCH всегда возвращает первое совпадение. Если вам нужно вернуть последнее совпадение (обратный поиск), см. функцию XMATCH .

MATCH поддерживает только одномерные массивы  или диапазоны как по вертикали, так и по горизонтали. Однако вы можете использовать функцию для поиска значений в двумерном диапазоне или таблице, указав MATCH один столбец  (или строку), содержащий искомое значение. Вы даже можете дважды использовать ПОИСКПОЗ в одной формуле,  чтобы одновременно найти совпадающую строку и столбец.

Часто функция ПОИСКПОЗ сочетается с функцией ИНДЕКС для извлечения значения в определенной (совпадающей) позиции. Другими словами, MATCH вычисляет позицию , а INDEX возвращает значение этой позиции.

Информация о типе соответствия

Тип соответствия не является обязательным. Если не указан, match_type по умолчанию равен 1 (точное или следующее наименьшее). Когда match_type равен 1 или -1, его иногда называют «приблизительным совпадением». Однако имейте в виду, что MATCH всегда будет выполнять точное совпадение, когда это возможно, как указано в таблице ниже:

Тип соответствия Поведение Подробности
1 Приблизительный MATCH находит наибольшее значение, меньшее или равное  искомому значению. Массив поиска должен быть отсортирован в порядке возрастания .
0 Точный Функция находит первое значение , равное искомому значению. Массив поиска не нужно сортировать.
-1 Приблизительный Находит наименьшее значение, большее или равное  искомому значению. Массив поиска должен быть отсортирован в порядке убывания .
(опущено) Приблизительный Когда match_type опущен, по умолчанию он равен 1 с поведением, как описано выше.

Предупреждение: Обязательно установите match_type равным нулю (0), если вам нужно точное совпадение. Значение по умолчанию 1 может привести к тому, что MATCH будет возвращать результаты, которые выглядят нормально, но на самом деле неверны. Явное указание значения для match_type является хорошим напоминанием о ожидаемом поведении.

Полное совпадение

Когда match_type равен нулю (0), MATCH выполняет только точное совпадение. В приведенном ниже примере формула в E3 выглядит так:

=MATCH(E2,B3:B11,0) // returns 4

В приведенной выше формуле искомое значение берется из ячейки E2. Если искомое значение жестко закодировано в формуле, оно должно быть заключено в двойные кавычки («»), так как это текстовое значение:

=MATCH("Mars",B3:B11,0)

Примечание. ПОИСКПОЗ в Excel не чувствителен к регистру, поэтому «Марс» и «марс» вернут 4.

Примерное совпадение

Если для параметра match_type установлено значение 1, ПОИСКПОЗ будет выполнять приблизительное сопоставление значений, отсортированных по AZ, находя наибольшее значение, меньшее или равное искомому значению. В приведенном ниже примере формула в E3 выглядит следующим образом:

=MATCH(E2,B3:B11,1) // returns 5

Совпадение с подстановочным знаком

Если параметр match_type равен нулю (0), MATCH может использовать подстановочные знаки. В приведенном ниже примере формула в E3 выглядит следующим образом:

=MATCH(E2,B3:B11,0) // returns 6

Это эквивалентно:

=MATCH("pq*",B3:B11,0)

ИНДЕКС и MATCH

Функция ПОИСКПОЗ в Excel обычно используется вместе с функцией INDEX. Полученная формула называется «INDEX и MATCH». Например, на экране ниже ИНДЕКС и ПОИСКПОЗ используются для возврата стоимости кода, введенного в ячейку F4. Формула в F5:

=INDEX(C5:C12,MATCH(F4,B5:B12,0)) // returns 150

В этом примере MATCH настроен на выполнение точного совпадения. Функция ПОИСКПОЗ находит код ABX-075 и возвращает его позицию (7) непосредственно в функцию ИНДЕКС в качестве номера строки. Затем функция ИНДЕКС возвращает седьмое значение из диапазона C5:C12 в качестве окончательного результата. Формула решается так:

=INDEX(C5:C12,MATCH(F4,B5:B12,0))
=INDEX(C5:C12,7)
=150

Совпадение с учетом регистра

Функция ПОИСКПОЗ не чувствительна к регистру. Однако MATCH можно настроить для выполнения совпадения с учетом регистра в сочетании с функцией ТОЧНОЕ в общей формуле, подобной этой:

=MATCH(TRUE,EXACT(lookup_value,array),0))

Функция EXACT сравнивает каждое значение в массиве с искомым_значением с учетом регистра.

Примечания

  • MATCHне чувствителен к регистру.
  • Возвращает ошибку #Н/Д в Excel, если совпадений не найдено.
  • Работает только с текстом длиной до 255 символов.
  • В случае дубликатов возвращает первое совпадение.
  • Если match_type равен -1 или 1, lookup_array должен быть отсортирован, как указано выше.
  • Если match_type равен 0,  lookup_value может содержать подстановочные знаки .
  • Функция часто используется вместе с функцией ИНДЕКС.
Автор А. Днепров

Обучаю основам работы с офисными программами.

Функция MATCH в Excel
Копирование листа в существующий файл