Поиск позиций с помощью MATCH

Если INDEX получает значение по позиции, то MATCH выполняет обратную задачу: сообщает, на какой позиции находится значение.

Вы передаёте MATCH значение для поиска и диапазон, в котором нужно искать, а функция возвращает число — позицию, на которой было найдено это значение.

MATCH не возвращает само значение, а только его местоположение. Именно это число нужно INDEX для работы.

MATCH принимает три аргумента:

• значение_для_поиска — значение, которое Вы хотите найти
• массив_для_поиска — диапазон для поиска
• тип_сопоставления — способ сопоставления: 0, 1 или -1

Обычно функция записывается так: =MATCH("Cherry", B2:B6, 0). Последний 0 означает "найти точное совпадение".

Для обычного поиска почти всегда используется 0.

Предположим, в B2:B6 находятся Яблоко, Банан, Вишня, Финик и Инжир. Выполнение =MATCH("Cherry", B2:B6, 0) возвращает 3.

Почему 3? Потому что Вишня — 3-й элемент диапазона. MATCH отсчитал позиции от начала B2:B6 и нашёл Вишню на позиции 3.

Обратите внимание: результатом является номер позиции, а не слово «Вишня». Эту позицию Вы передадите INDEX в следующем уроке.

Третий аргумент управляет способом поиска MATCH:

• 0 — точное совпадение; значение должно быть найдено без изменений, порядок может быть любым
• 1 — наибольшее значение, меньшее или равное искомому; данные должны быть отсортированы по возрастанию
• -1 — наименьшее значение, большее или равное искомому; данные должны быть отсортированы по убыванию

Для поиска конкретного элемента, например кода товара или имени, всегда используйте 0. Остальные режимы предназначены для отсортированных числовых диапазонов.

MATCH одинаково хорошо работает и с числами, и с текстом. Если в C2:C8 находятся идентификаторы заказов и Вы хотите узнать, где находится ID 5042, введите =MATCH(5042, C2:C8, 0).

Если 5042 находится в 4-й строке этого диапазона, MATCH возвращает 4.

Искомым значением также может быть ссылка на ячейку. Формула =MATCH(E1, C2:C8, 0) находит любой идентификатор, введённый в E1, поэтому поиск обновляется при изменении E1.

MATCH неважно, расположен ли Ваш диапазон вертикально или горизонтально. Функция просто отсчитывает позиции.

Предположим, в строке 1 в B1:M1 находятся заголовки месяцев: Янв, Фев, Мар и так далее. Чтобы узнать, в каком столбце находится «Мар», введите =MATCH("Mar", B1:M1, 0); результатом будет 3.

Такое горизонтальное использование необходимо для двустороннего поиска, при котором позиция строки и позиция столбца находятся отдельно.

Если искомого значения нет в диапазоне, MATCH возвращает ошибку #N/A, которая просто означает "недоступно".

Например, формула =MATCH("Mango", B2:B6, 0) для списка без Манго возвращает #N/A.

Это нормальное поведение, а не признак неисправности формулы. Позже Вы обернёте формулы поиска в IFNA или IFERROR, чтобы вместо необработанной ошибки показывать понятное сообщение.

Большое преимущество match_type 0 заключается в том, что данные не нужно сортировать. MATCH просматривает диапазон и возвращает позицию первого найденного точного совпадения.

Поэтому независимо от того, расположен ли список регионов по алфавиту или полностью перемешан, формула =MATCH("West", A2:A20, 0) всё равно правильно находит Запад.

Именно эта свобода делает точное совпадение стандартным выбором почти для любого поиска в реальных задачах.

Тип сопоставления 1 полезен для диапазонов и уровней. В отсортированном по возрастанию списке пороговых значений MATCH находит наибольшее значение, которое всё ещё меньше или равно искомому.

Если в A2:A6 находятся значения 0, 100, 500, 1000 и 5000, а Вы выполняете =MATCH(750, A2:A6, 1), результатом будет 3, поскольку 500 — самый высокий порог, не превышающий 750.

Так таблицы оценок и цен относят значение к подходящему диапазону.

Сам по себе MATCH сообщает только номер позиции, что не очень полезно в качестве окончательного ответа. Фраза «клиент находится в строке 7» — лишь промежуточный результат.

Обычно Вам нужны данные на этой позиции: адрес электронной почты, баланс или статус клиента.

Поэтому MATCH почти никогда не используется отдельно. В следующем уроке Вы передадите позицию, найденную MATCH, непосредственно в INDEX, чтобы получить нужное Вам значение.

Вы можете дважды выполнить MATCH, чтобы независимо определить строку и столбец. Один вызов MATCH находит позицию строки по столбцу меток, другой — позицию столбца по строке заголовков.

Например, найдите строку с меткой «Восток» с помощью =MATCH("East", A2:A10, 0), а столбец с заголовком «3-й квартал» — с помощью =MATCH("Q3", B1:E1, 0).

Вместе эти два числа однозначно определяют ячейку, которую можно передать INDEX.

Убедитесь, что Вы понимаете, что возвращает MATCH.

Вы узнали, что MATCH возвращает позицию значения внутри диапазона:

• =MATCH(lookup_value, lookup_array, match_type)
• Используйте 0 для точного совпадения; данные не обязательно сортировать
• Используйте 1 для данных, отсортированных по возрастанию, чтобы найти подходящий диапазон или уровень
• Функция работает вертикально и горизонтально и возвращает #N/A, если совпадение не найдено

MATCH сообщает позицию, а INDEX превращает позицию в значение. Далее Вы объедините их в одну мощную формулу поиска.