Функция ПОИСКПОЗ в Excel (MATCH): как работает, пример и формула под вашу таблицу
Синтаксис в русском Excel
В английском Excel, Google Таблицах и внутри файла .xlsx
Аргументы
искомое | что ищем |
просматриваемый_массив | где ищем |
[тип_сопоставления] | 0 — точно |
Пример
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Артикул | Товар | Цена | ||
| 2 | ШВ-1187 | Шуруповёрт 18 В | 7990 | 3 | |
| 3 | ДР-2041 | Дрель ударная 750 Вт | 5490 | ||
| 4 | УШМ-0915 | Болгарка 125 мм | 4750 | ||
| 5 | ЛЗ-4410 | Лазерный уровень | 3780 |
Результат в ячейке E2 посчитан LibreOffice на сервере Формулника.
Частые ошибки
без 0 в конце ищет приблизительно в отсортированном списке
Где работает
| Программа | ПОИСКПОЗ | |
|---|---|---|
| Excel 2010 | есть | |
| Excel 2013 | есть | |
| Excel 2016 | есть | |
| Excel 2019 | есть | |
| Excel 2021 | есть | |
| Excel 2024 | есть | |
| Excel 365 | есть | |
| LibreOffice Calc | есть | |
| Google Таблицы | есть | |
| Яндекс Таблицы | есть | |
| Р7-Офис | есть | |
| МойОфис | есть | |
| MOffice Sheets | есть | проверено |
Похожие функции
ПОИСКПОЗ в работе: номер столбца по заголовку, таблица тарифов, дубли и два условия
ПОИСКПОЗ возвращает не значение, а его номер в диапазоне, поэтому почти всегда работает в паре: с ИНДЕКС — для поиска в любую сторону, с ВПР — чтобы номер столбца брался по заголовку и не ломался от вставки столбцов. Третий аргумент ставьте 0 всегда, кроме шкал: 1 ищет ближайшее меньшее в списке по возрастанию, -1 — ближайшее большее в списке по убыванию.
По шагам
- Выделяйте просматриваемый диапазон в одну строку или в один столбец — прямоугольник из нескольких столбцов даст #Н/Д.
- Помните, что номер считается от начала выделенного диапазона, а не от строки 1 листа: для
A5:A100первая позиция — строка 5. - Для ИНДЕКС берите диапазоны одинаковой высоты и с одной стартовой строки, иначе ответ сдвинется на несколько строк.
- Регистр букв ПОИСКПОЗ не различает: «ооо север» и «ООО Север» для неё одно и то же.
Тариф на пересечении зоны и веса
Тарифная сетка: в A3:A12 — зоны доставки, в B2:G2 — веса 1, 5, 10, 20, 30, 50 кг, внутри — цены. Первый ПОИСКПОЗ находит строку зоны точно, второй — столбец веса с типом 1: посылка 7,4 кг попадёт в колонку «5 кг». Если тариф считается по ближайшему большему весу, разверните заголовок по убыванию и поставьте тип -1.
=ИНДЕКС($B$3:$G$12;ПОИСКПОЗ(K2;$A$3:$A$12;0);ПОИСКПОЗ(L2;$B$2:$G$2;1))Номер столбца для ВПР по названию заголовка
В выгрузке из 1С столбцы меняют порядок от месяца к месяцу. Вместо жёсткой тройки ВПР получает номер столбца, найденный ПОИСКПОЗ по заголовку «Цена». Важно, чтобы строка заголовков начиналась с того же столбца, что и диапазон ВПР, — тогда номера совпадут.
=ВПР(A2;Выгрузка!$A$1:$K$900;ПОИСКПОЗ("Цена";Выгрузка!$A$1:$K$1;0);0)Поиск по двум условиям: клиент и месяц
Перемножение двух проверок даёт массив из единиц и нулей, а ПОИСКПОЗ находит первую единицу. В Microsoft 365 и Excel 2021 формула вводится обычным Enter; в Excel 2019 и раньше — сочетанием Ctrl+Shift+Enter, после чего вокруг неё появятся фигурные скобки.
=ИНДЕКС($D$2:$D$800;ПОИСКПОЗ(1;($A$2:$A$800=G2)*($B$2:$B$800=H2);0))Пометить повторы, оставив первое вхождение
ПОИСКПОЗ всегда находит первое совпадение. Если позиция первого вхождения не совпадает с номером текущей строки, значит, строка — повтор. Для данных с A2 вычтите 1 из СТРОКА, чтобы номера совпадали.
=ЕСЛИ(ПОИСКПОЗ(A2;$A$2:$A$800;0)<>СТРОКА()-1;"повтор";"")Типы сопоставления
| Тип | Как ищет | Требование к данным |
|---|---|---|
| 0 | точное совпадение, можно * и ? | любой порядок |
| 1 или пропущен | наибольшее значение ≤ искомого | по возрастанию |
| -1 | наименьшее значение ≥ искомого | по убыванию |
| Есть ли значение в списке | =ЕЧИСЛО(ПОИСКПОЗ(A2;Склад!$A:$A;0)) | ИСТИНА или ЛОЖЬ |
| Позиция максимума | =ПОИСКПОЗ(МАКС(D2:D50);D2:D50;0) | первый из равных |
Частые ошибки
- Третий аргумент пропущен — по умолчанию тип 1, и в неотсортированном списке ПОИСКПОЗ вернёт случайную позицию без всякой ошибки. Для справочников пишите 0.
- #Н/Д при видимом совпадении — в одном списке код — число, в другом — текст, или в конце названия неразрывный пробел из 1С. Проверьте ЕТЕКСТ и ДЛСТР у обеих ячеек.
- ИНДЕКС вернул соседнюю строку — диапазоны ИНДЕКС и ПОИСКПОЗ начинаются с разных строк, например
C1:C500иA2:A500.
Вопросы и ответы
Как найти последнее вхождение через ПОИСКПОЗ?
В Microsoft 365 и Excel 2021 есть ПОИСКПОЗX с поиском с конца: =ПОИСКПОЗX(F2;A2:A800;0;-1). В старых версиях помогает приём =ПРОСМОТР(2;1/(A2:A800=F2);СТРОКА(A2:A800)) — он вернёт номер строки листа.
Чем ПОИСКПОЗ отличается от ПОИСКПОЗX?
ПОИСКПОЗX по умолчанию ищет точно, умеет искать с конца и не требует сортировки для ближайшего значения. Есть только в Excel 2021 и Microsoft 365; ПОИСКПОЗ работает везде, включая LibreOffice и Р7-Офис.
Можно ли искать часть текста?
Да, с типом 0 и звёздочками: =ПОИСКПОЗ("*"&F2&"*";B2:B800;0) найдёт первую строку, где название содержит текст из F2.
Почему ПОИСКПОЗ по датам не находит дату?
В одной таблице дата с временем, в другой — без. Округлите время в исходнике через ЦЕЛОЕ или ищите по ЦЕЛОЕ(дата) во вспомогательном столбце.