Функция ПОИСКПОЗX (XMATCH) в Excel и Google Sheets. Полное руководство

Кратко

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

Где работает:
✅ Excel Microsoft 365, Excel 2024, Excel 2021 и Excel для Интернета
✅ Google Sheets — поддерживается
❌ Excel 2019 и старше — функция недоступна

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

Сложность:
★★☆☆☆ — Средняя

Для чего нужна функция

ПОИСКПОЗX (XMATCH) — это современная замена функции ПОИСКПОЗ (MATCH), которая появилась в Excel 2021 и Microsoft 365. Она предлагает ряд улучшений, делающих поиск позиции значения более гибким и удобным:

• Поиск в любом направлении — можно искать как сверху вниз (по умолчанию), так и снизу вверх
• Точное совпадение по умолчанию — в отличие от ПОИСКПОЗ, где по умолчанию используется приблизительный поиск
• Поддержка подстановочных знаков — отдельный режим для поиска по шаблону
• Бинарный поиск — для работы с отсортированными данными, который может быть эффективнее обычного поиска на больших диапазонах.

Основные сценарии использования:

• Гибкий поиск позиции — определение номера строки или столбца с возможностью поиска с конца диапазона
• Связка с ИНДЕКС — создание современных и устойчивых формул поиска данных
• Поиск последнего совпадения — нахождение самого нового или последнего значения в списке
• Работа с частичными совпадениями — поиск по шаблону через подстановочные знаки

Совместимость

Платформа	Поддержка XMATCH	Примечание
Excel Microsoft 365	✅ Да	Полная поддержка
Excel 2024	✅ Да	Полная поддержка
Excel 2021	✅ Да	Полная поддержка
Excel 2019 и старше	❌ Нет	Функция недоступна
Google Sheets	✅ Да	Функция XMATCH поддерживается

Совет: Если вы работаете в старой версии Excel, используйте классическую функцию ПОИСКПОЗ (MATCH).

Синтаксис

Логика аргументов функции одинакова в Excel и Google Sheets:

=ПОИСКПОЗX(искомое_значение; просматриваемый_массив; [режим_соответствия]; [режим_поиска])

=XMATCH(lookup_value; lookup_array; [match_mode]; [search_mode])

Разбор аргументов

Аргумент	Обязательный	Описание
искомое_значение (lookup_value)	✅ Да	Значение, которое нужно найти в массиве
просматриваемый_массив (lookup_array)	✅ Да	Диапазон из одной строки или одного столбца для поиска
режим_соответствия (match_mode)	❌ Нет	Определяет тип поиска. По умолчанию — 0 (точное совпадение)
режим_поиска (search_mode)	❌ Нет	Определяет направление и метод поиска. По умолчанию — 1 (сначала → последний)

Режим_соответствия

Значение	Описание
0 или опущен	Точное совпадение (по умолчанию)
1	Точное совпадение или следующее большее значение
-1	Точное совпадение или следующее меньшее значение
2	Поиск с подстановочными знаками (* и ?)

Режим_поиска

Значение	Описание
1 или опущен	Поиск от первого до последнего элемента (по умолчанию)
-1	Поиск от последнего до первого элемента (обратный поиск)
2	Бинарный поиск. Требует сортировки по возрастанию
-2	Бинарный поиск. Требует сортировки по убыванию

Простые примеры

Пример 1. Точное совпадение (режим по умолчанию)

Исходные данные: A1:A4 содержит «Москва», «Санкт-Петербург», «Казань», «Сочи».

Формула: =ПОИСКПОЗX("Казань"; A1:A4)

Результат: 3 (позиция значения в диапазоне).

Объяснение: В отличие от ПОИСКПОЗ, здесь не нужно указывать 0 — точное совпадение используется по умолчанию.

Пример 2. Поиск последнего совпадения (режим_поиска = -1)

Исходные данные: A1:A4 содержит «Иванов», «Петров», «Иванов», «Сидоров».

Задача: Найти позицию последнего вхождения «Иванов».

Формула: =ПОИСКПОЗX("Иванов"; A1:A4; 0; -1)

Результат: 3 (последнее вхождение на позиции 3).

Объяснение: режим_поиска = -1 заставляет функцию искать с конца диапазона.

Пример 3. Приблизительный поиск (режим_соответствия = 1)

Исходные данные: A1:A5 содержит 10, 20, 30, 40, 50 (отсортированы по возрастанию).

Задача: Найти позицию числа 25.

Формула: =ПОИСКПОЗX(25; A1:A5; 1)

Результат: 3 (ближайшее большее значение — 30 на позиции 3).

Пример 4. Поиск с подстановочным знаком (режим_соответствия = 2)

Исходные данные: A1:A3 содержит «Яблоко», «Банан», «Груша».

Задача: Найти позицию первого значения, содержащего «ан».

Формула: =ПОИСКПОЗX("*ан*"; A1:A3; 2)

Результат: 2 (значение «Банан» содержит «ан»).

Объяснение: * означает любую последовательность символов. Режим 2 включает поддержку подстановочных знаков.

Подстановочные знаки в ПОИСКПОЗX:

• * — любое количество символов
• ? — один символ
• ~ — позволяет искать сами символы *, ? и ~

Практические сценарии использования

ИНДЕКС + ПОИСКПОЗX — гибкая альтернатива ВПР

Задача: Найти название сотрудника по его ID (поиск в любом направлении).

Исходные данные: Столбец A — имена, C — ID сотрудников.

Формула: =ИНДЕКС(A2:A10; ПОИСКПОЗX(102; C2:C10))

Результат: Имя сотрудника с ID 102.

Объяснение: ПОИСКПОЗX определяет позицию найденного элемента, а ИНДЕКС возвращает соответствующее значение.

Поиск последней транзакции по товару

Задача: Найти сумму самой последней продажи определённого товара.

Исходные данные: A — даты, B — товары, C — суммы.

Формула: =ИНДЕКС(C2:C100; ПОИСКПОЗX("Товар A"; B2:B100; 0; -1))

Результат: Сумма последней продажи товара A.

Объяснение: режим_поиска = -1 находит последнее вхождение «Товар A» в столбце B. Это одна из самых сильных особенностей функции.

Гибкий двумерный поиск

Задача: Найти значение на пересечении строки и столбца.

Формула: =ИНДЕКС(данные; ПОИСКПОЗX(строка; диапазон_строк); ПОИСКПОЗX(столбец; диапазон_столбцов))

Объяснение: Оба поиска используют ПОИСКПОЗX, что делает формулу компактной и гибкой.

Бинарный поиск в больших таблицах

Задача: Быстро найти значение в отсортированном списке из большого количества строк.

Формула: =ПОИСКПОЗX(значение; A2:A10001; 0; 2)

Объяснение: режим_поиска = 2 включает бинарный поиск. Бинарный поиск может быть значительно эффективнее линейного поиска на больших отсортированных диапазонах, однако результат зависит от размера данных и конкретной задачи.

Важно: Бинарный поиск требует предварительной сортировки данных. На несортированных данных он может вернуть некорректный результат.

Поиск с несколькими условиями (продвинутый приём)

Задача: Найти позицию строки, где выполнены два условия.

Формула: =ПОИСКПОЗX(1; (A2:A100="Клиент")*(B2:B100="Товар"))

Объяснение: Два логических массива перемножаются, и 1 получается только там, где оба условия истинны. ПОИСКПОЗX находит позицию первого такого совпадения.

Примечание: Это более продвинутый приём, который требует понимания работы с массивами в Excel.

Сравнение ПОИСКПОЗX и ПОИСКПОЗ

Критерий	ПОИСКПОЗX (XMATCH)	ПОИСКПОЗ (MATCH)
Точное совпадение по умолчанию	✅ Да (0)	❌ Нет (1 — приблизительное)
Поиск с конца диапазона	✅ Да (режим_поиска = -1)	❌ Нет
Подстановочные знаки	✅ Да (режим 2)	Только с 0
Бинарный поиск	✅ Да (режим_поиска 2/-2)	❌ Нет
Работа с массивами	✅ Современная поддержка массивов	✅ Поддерживается, возможности зависят от версии Excel
Доступность	Excel 2021+, Google Sheets	Все версии

Связка функций: что ищет что

Функция	Что делает
ПОИСКПОЗ	Находит позицию значения в диапазоне
ПОИСКПОЗX	Находит позицию с расширенными возможностями
ИНДЕКС	Возвращает значение по позиции
ПРОСМОТРX	Ищет значение и возвращает результат

Связка ИНДЕКС + ПОИСКПОЗX позволяет создавать гибкие формулы поиска, которые могут работать в любом направлении и не имеют некоторых ограничений ВПР.

ПОИСКПОЗX или ПОИСКПОЗ — что выбрать?

Если вы работаете в Excel 2021, Excel 2024 или Microsoft 365, в большинстве новых формул поиска стоит рассмотреть ПОИСКПОЗX вместо ПОИСКПОЗ. ПОИСКПОЗX использует точное совпадение по умолчанию, умеет выполнять обратный поиск и поддерживает дополнительные режимы поиска.

ПОИСКПОЗ по-прежнему полезен при работе со старыми версиями Excel и в существующих книгах, где уже используются классические формулы.

Для поиска самого значения, а не его позиции, стоит рассмотреть ПРОСМОТРX (XLOOKUP). Для построения более универсальных формул можно использовать связку ИНДЕКС + ПОИСКПОЗX.

Частые ошибки и их решение

Ошибка #Н/Д (#N/A)

Причина: Искомое значение не найдено в диапазоне.

Решение: Проверьте наличие значения и используйте ЕСЛИОШИБКА для обработки:

=ЕСЛИОШИБКА(ПОИСКПОЗX(A2; B:B); "Не найдено")

Неверный результат с бинарным поиском

Причина: Данные не отсортированы, но используется режим_поиска = 2 или -2.

Решение: Убедитесь, что данные отсортированы правильно, или используйте режим_поиска = 1.

Важно: Бинарный поиск на несортированных данных возвращает некорректный результат без ошибки.

Ошибка #ЗНАЧ! (#VALUE!)

Причина: Аргумент режим_соответствия или режим_поиска содержит недопустимое значение.

Решение: Используйте только допустимые значения: 0, 1, -1, 2 для режима соответствия; 1, -1, 2, -2 для режима поиска.

Полезные комбинации

ИНДЕКС + ПОИСКПОЗX (гибкая альтернатива ВПР)

=ИНДЕКС(диапазон_возврата; ПОИСКПОЗX(искомое_значение; диапазон_поиска))

ПОИСКПОЗX + ЕСЛИОШИБКА (защита от ошибок)

=ЕСЛИОШИБКА(ПОИСКПОЗX(A2; B:B); "Не найдено")

ПОИСКПОЗX с несколькими условиями (продвинутый приём)

=ПОИСКПОЗX(1; (A2:A100="Клиент")*(B2:B100="Товар"))

Часто задаваемые вопросы

Вопрос: В каких версиях Excel работает ПОИСКПОЗX (XMATCH)?
Ответ: В Excel Microsoft 365, Excel 2024 и Excel 2021. В Excel 2019 и старше — нет. В Google Sheets — поддерживается.

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

Вопрос: Можно ли использовать ПОИСКПОЗX в Google Sheets?
Ответ: Да, функция поддерживается в Google Sheets и имеет тот же синтаксис.

Вопрос: Когда использовать бинарный поиск?
Ответ: Используйте его для больших отсортированных диапазонов, когда важна производительность поиска. Диапазон должен быть предварительно отсортирован в нужном порядке.

Вопрос: Что делает режим_соответствия = 2?
Ответ: Включает поиск с подстановочными знаками: * для любой последовательности символов и ? для одного символа.

Вопрос: Чем ПОИСКПОЗX отличается от ПОИСКПОЗ в работе с массивами?
Ответ: ПОИСКПОЗX имеет современную поддержку массивов, что упрощает создание и редактирование формул в новых версиях Excel.

Заключение

ПОИСКПОЗX (XMATCH) — это современная и более гибкая версия ПОИСКПОЗ, доступная в Excel 2021+, Microsoft 365 и Google Sheets. Она предлагает ключевые улучшения: точное совпадение по умолчанию, поиск с конца диапазона, поддержку подстановочных знаков и бинарный поиск.

Ключевые правила:

1. Для точного совпадения аргумент режим_соответствия можно опустить — он по умолчанию равен 0.
2. Для поиска последнего вхождения используйте режим_поиска = -1.
3. Бинарный поиск (режимы 2 и -2) требует предварительной сортировки данных.
4. В связке с ИНДЕКС ПОИСКПОЗX становится мощной гибкой альтернативой ВПР и работает в любом направлении.

Освойте ПОИСКПОЗX — и вы сможете создавать более гибкие и производительные формулы для поиска данных в Excel и Google Sheets, особенно при работе с большими массивами и сложными структурами данных.