Функция ПОИСКПОЗ (на английском MATCH) отвечает на вопрос «на каком месте?»: =ПОИСКПОЗ("Блендер";A2:A6;0) вернёт 3, если «Блендер» стоит в списке третьим. Не само значение, а его позицию. Последний аргумент почти всегда ставьте 0, это точное совпадение. Позицию затем передают в ИНДЕКС, и получается поиск, который умеет больше, чем ВПР: =ИНДЕКС(C2:C100;ПОИСКПОЗ(E2;A2:A100;0)).
| Раздел функций | Ссылки и массивы |
| Название на английском | MATCH |
| Версии Excel | Все версии Excel, включая Microsoft 365 |
| Волатильность | Не волатильная |
| Похожие функции | ИНДЕКС, ВПР, ПРОСМОТРX, ПОИСКПОЗX, ПОИСК |
Синтаксис функции ПОИСКПОЗ
=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])
- искомое_значение — что ищем: ячейка, число или текст в кавычках;
- просматриваемый_массив — где ищем: строго один столбец или одна строка;
- тип_сопоставления — 0 точное совпадение; 1 наибольшее значение, которое меньше или равно искомому (список по возрастанию); −1 наименьшее значение, которое больше или равно искомому (список по убыванию).
Здесь кроется главная ловушка функции. Если третий аргумент не указать, Excel подставит 1, а не 0. На несортированном списке такая формула вернёт не ту позицию или #Н/Д, и понять причину, не зная этой детали, сложно. Поэтому я советую всегда писать 0 явно, даже когда кажется, что он не нужен. Регистр букв ПОИСКПОЗ не различает.
Номер позиции по точному совпадению
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Товар | Ищем | Позиция | |
| 2 | Чайник | Блендер | 3=ПОИСКПОЗ(C2;$A$2:$A$6;0) | |
| 3 | Тостер | миксер | 4=ПОИСКПОЗ(C3;$A$2:$A$6;0) | |
| 4 | Блендер | Пылесос | #Н/Д=ПОИСКПОЗ(C4;$A$2:$A$6;0) | |
| 5 | Миксер | |||
| 6 | Кофеварка |
«миксер» со строчной буквы найден: регистр не важен. Пылесоса в списке нет, поэтому #Н/Д. Позиция считается от начала диапазона: A2 — первая, A6 — пятая.
В английском Excel: =MATCH(C2,$A$2:$A$6,0)
Позиция сама по себе нужна нечасто, но есть и такие задачи: узнать место участника в рейтинге, номер месяца по его названию (=ПОИСКПОЗ("Март";B1:M1;0) вернёт 3, если месяцы стоят по порядку) или проверить, есть ли значение в списке.
Проверить, есть ли значение в списке
Если ПОИСКПОЗ нашла значение, она возвращает число, если нет — #Н/Д. Эту разницу ловит ЕНД, а ЕСЛИ превращает её в понятный ответ.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Список | Город | Есть в списке? | |
| 2 | Москва | Казань | Есть=ЕСЛИ(ЕНД(ПОИСКПОЗ(C2;$A$2:$A$5;0));"Нет";"Есть") | |
| 3 | Казань | Омск | Нет=ЕСЛИ(ЕНД(ПОИСКПОЗ(C3;$A$2:$A$5;0));"Нет";"Есть") | |
| 4 | Самара | Пермь | Есть=ЕСЛИ(ЕНД(ПОИСКПОЗ(C4;$A$2:$A$5;0));"Нет";"Есть") | |
| 5 | Пермь |
Короче то же самое пишется через ЕЧИСЛО: =ЕЧИСЛО(ПОИСКПОЗ(C2;$A$2:$A$5;0)) вернёт ИСТИНА или ЛОЖЬ. Такую формулу удобно класть в условное форматирование, чтобы подсветить найденные строки.
В английском Excel: =IF(ISNA(MATCH(C2,$A$2:$A$5,0)),"No","Yes")
Кстати, сравнить два списка и найти, чего не хватает в одном из них, можно и без формул. Об этом в статье про поиск по нескольким условиям.
Приблизительный поиск: тип сопоставления 1 и −1
Тип 1 находит последнее значение, которое не больше искомого. Это нужно для шкал: баллы → оценка, сумма → скидка, стаж → надбавка. Шкала обязательно должна быть отсортирована по возрастанию.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Баллы от | Оценка | Баллы | Позиция | Оценка | |
| 2 | 0 | 2 | 64 | 2=ПОИСКПОЗ(D2;$A$2:$A$5;1) | 3=ИНДЕКС($B$2:$B$5;E2) | |
| 3 | 50 | 3 | 85 | 4=ПОИСКПОЗ(D3;$A$2:$A$5;1) | 5=ИНДЕКС($B$2:$B$5;E3) | |
| 4 | 70 | 4 | 12 | 1=ПОИСКПОЗ(D4;$A$2:$A$5;1) | 2=ИНДЕКС($B$2:$B$5;E4) | |
| 5 | 85 | 5 |
64 больше 50, но меньше 70, поэтому ПОИСКПОЗ возвращает позицию 50, то есть 2. 85 совпадает с порогом точно — позиция 4. ИНДЕКС по позиции берёт оценку.
В английском Excel: =MATCH(D2,$A$2:$A$5,1)
Тип −1 работает зеркально: ищет наименьшее значение, которое больше или равно искомому, и требует сортировки по убыванию. На практике он встречается редко.
ПОИСКПОЗ по части текста
При типе сопоставления 0 работают подстановочные знаки: звёздочка заменяет любое количество символов, вопросительный знак — ровно один.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Товар | Слово | Позиция | |
| 2 | Молоко 3,2% 1 л | йогурт | 4=ПОИСКПОЗ("*"&C2&"*";$A$2:$A$5;0) | |
| 3 | Кефир 1% 0,9 л | сметана | 3=ПОИСКПОЗ("*"&C3&"*";$A$2:$A$5;0) | |
| 4 | Сметана 20% 300 г | |||
| 5 | Йогурт питьевой 2,5% |
"*"&C2&"*" превращается в шаблон «*йогурт*»: любое начало, слово, любой конец. Если подходящих строк несколько, вернётся первая. Чтобы искать сам символ * или ?, поставьте перед ним тильду: ~*.
В английском Excel: =MATCH("*"&C2&"*",$A$2:$A$5,0)
Для поиска подстроки внутри одной ячейки нужна другая функция, ПОИСК: она возвращает позицию символа в тексте, а не позицию строки в списке. Названия похожи, задачи разные.
ПОИСКПОЗ в связке с ИНДЕКС
Самое частое применение функции. ПОИСКПОЗ находит номер строки, ИНДЕКС достаёт значение с этим номером из любого столбца:
=ИНДЕКС(C2:C100;ПОИСКПОЗ(E2;A2:A100;0))
Формула ищет E2 в столбце A и возвращает соседнее значение из столбца C. Столбец результата может стоять левее столбца поиска, и связка не сломается, если в таблицу вставят новый столбец. Подробные примеры, включая поиск сразу по строке и столбцу, есть на странице функции ИНДЕКС.
В Microsoft 365 появилась ПОИСКПОЗX (XMATCH): по умолчанию точное совпадение, поиск с конца списка и не нужна сортировка для приблизительного поиска. А для обычной подстановки значений там проще сразу взять ПРОСМОТРX.
Почему ПОИСКПОЗ возвращает #Н/Д
Ошибка значит одно: значение не найдено. А вот причин у этого несколько, и почти все они в данных:
- Не указан тип сопоставления 0. Без него ПОИСКПОЗ ищет приблизительно и на несортированном списке ошибается.
- Пробелы в конце ячеек: «Москва» и «Москва » для Excel разные значения. Уберите лишние пробелы или оберните искомое в СЖПРОБЕЛЫ.
- Число в одном списке и текст в другом: 1024 и «1024» не совпадут. Поможет преобразование чисел, сохранённых как текст.
- Латинские буквы вместо русских: «А-1024» с русской А и «A-1024» с латинской выглядят одинаково. Проверка — замена кириллицы на латиницу.
- Просматриваемый массив из нескольких столбцов: ПОИСКПОЗ работает только с одной строкой или одним столбцом.
Чтобы показать вместо ошибки понятный текст, оберните формулу в ЕСЛИОШИБКА: =ЕСЛИОШИБКА(ПОИСКПОЗ(E2;A2:A100;0);"Не найдено").
Функция ПОИСКПОЗ — частые вопросы
Что возвращает функция ПОИСКПОЗ?
Номер позиции значения в диапазоне, а не само значение: =ПОИСКПОЗ("Блендер";A2:A6;0) вернёт 3, если «Блендер» стоит третьим.
Как ПОИСКПОЗ называется на английском?
MATCH. Формула =ПОИСКПОЗ(E2;A2:A100;0) в английском Excel — =MATCH(E2,A2:A100,0).
Что означает 0 в конце ПОИСКПОЗ?
Точное совпадение. Без него Excel подставит 1 и будет искать приблизительно, что на несортированных данных даёт неверный результат.
Как найти значение по части текста?
Подстановочными знаками: =ПОИСКПОЗ("*"&C2&"*";A2:A100;0).
Как проверить, есть ли значение в списке?
Формулой =ЕЧИСЛО(ПОИСКПОЗ(C2;A2:A100;0)): ИСТИНА, если значение есть, ЛОЖЬ — если нет.
Половина #Н/Д в ПОИСКПОЗ — это пробелы, числа как текст и латиница вместо кириллицы. С !SEMTools такие ячейки находятся и исправляются прямо на месте, во всём столбце сразу, без вспомогательных формул.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: функция ПОИСКПОЗ в Excel
| Задача | Формула |
|---|---|
| Позиция значения в списке | =ПОИСКПОЗ(E2;A2:A100;0) |
| Подставить значение (вместо ВПР) | =ИНДЕКС(C2:C100;ПОИСКПОЗ(E2;A2:A100;0)) |
| Есть ли значение в списке | =ЕЧИСЛО(ПОИСКПОЗ(E2;A2:A100;0)) |
| Позиция по части текста | =ПОИСКПОЗ("*"&E2&"*";A2:A100;0) |
| Позиция в шкале (по возрастанию) | =ПОИСКПОЗ(D2;A2:A5;1) |
| Номер месяца по названию | =ПОИСКПОЗ("Март";B1:M1;0) |
Надеюсь, на этой странице вы нашли то, что искали, и теперь понятно, почему ПОИСКПОЗ почти не встречается в одиночку. Запомните одно правило — ноль в конце, — и большая часть странных результатов исчезнет. Остальное, как правило, лечится чисткой данных.