=ПОИСКПОЗ(E2;A2:A6;0) (по-английски MATCH) возвращает позицию значения из E2 в A2:A6: 1 для первой ячейки, 2 для второй и так далее. 0 требует точного совпадения. ПОИСКПОЗ возвращает число, а не само значение, поэтому её обычно используют вместе с ИНДЕКС. Таблицы на этой странице показывают английскую запись, но формулы в них можно вводить и по-русски, с точкой с запятой.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | Position | |
| 2 | Apple | Fruit | $1.20 | Bread | 4 | |
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
=ПОИСКПОЗ(E2;A2:A6;0)Bread четвёртая ячейка A2:A6, поэтому F2 показывает 4. Отсчёт начинается с первой ячейки диапазона, а не со строки 1 листа: замените диапазон на A1:A6, и ответ станет 5. Введите Kiwi в E2, и ПОИСКПОЗ вернёт #Н/Д (в таблице по-английски #N/A; таблицы здесь показывают ошибки под английскими именами).
Синтаксис ПОИСКПОЗ и типы сопоставления
=MATCH(lookup_value, lookup_array, [match_type])
| match_type (тип_сопоставления) | Находит | Список должен быть |
|---|---|---|
0 | Первое значение, точно равное lookup_value. Для текста допустимы подстановочные знаки. | в любом порядке |
1 или пропущен | Наибольшее значение, которое меньше lookup_value или равно ему. | отсортирован по возрастанию |
-1 | Наименьшее значение, которое больше lookup_value или равно ему. | отсортирован по убыванию |
По умолчанию стоит 1, а не 0. ПОИСКПОЗ без третьего аргумента на неотсортированном списке имён не жалуется: она возвращает позицию, которая может относиться к другой строке. Пишите 0 каждый раз, когда ищете текст, коды или идентификаторы.
Приблизительный поиск: в какой интервал попадает значение
С типом сопоставления 1 ПОИСКПОЗ отвечает на вопрос «какой интервал»: это позиция последнего порога, которого значение достигло. Здесь балл 75 прошёл 0, 60 и 70, поэтому он в интервале 3, оценка C.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Min score | Grade | Score | Band | Grade | |
| 2 | 0 | F | 75 | 3 | C | |
| 3 | 60 | D | 59 | 1 | F | |
| 4 | 70 | C | 90 | 5 | A | |
| 5 | 80 | B | 100 | 5 | A | |
| 6 | 90 | A |
=ПОИСКПОЗ(D2;$A$2:$A$6;1)59 всё ещё ниже 60, поэтому это интервал 1 (F). 90 точно совпадает с последним порогом, а 100 выше него, поэтому оба попадают в интервал 5 (A). Пороги должны идти по возрастанию: именно это позволяет ПОИСКПОЗ остановиться в нужном месте.
Тип сопоставления -1 это зеркальный вариант для списка, отсортированного от большего к меньшему. Он возвращает позицию наименьшего значения, которое всё ещё больше искомого или равно ему: если в столбце размеры {50;25;12;5}, =ПОИСКПОЗ(18;D2:D5;-1) возвращает 2, размер 25, наименьший, в который помещается 18.
Подстановочные знаки и регистр
ПОИСКПОЗ не учитывает регистр, а с типом сопоставления 0 принимает подстановочные знаки * (любые символы) и ? (ровно один символ). Для поиска с учётом регистра сравните список функцией СОВПАД (EXACT) и ищите первое ИСТИНА.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Category | Price | Starts with "C" | 3 |
| 2 | Apple | Fruit | $1.20 | Lower case "milk" | 5 |
| 3 | Pear | Fruit | $1.50 | Exactly "milk" | #N/A |
| 4 | Carrot | Vegetable | $0.80 | ||
| 5 | Bread | Bakery | $2.40 | ||
| 6 | Milk | Dairy | $1.10 |
=ПОИСКПОЗ("C*";A2:A6;0)"C*" совпадает с Carrot, позиция 3. "milk" находит Milk на позиции 5, потому что регистр не учитывается. Вариант с СОВПАД сравнивает каждое название с «milk» с учётом регистра, не находит ни одного ИСТИНА и возвращает #Н/Д. Замените A6 на milk, и формула вернёт 5. В Excel 2019 и старше подтверждайте формулу с СОВПАД через Ctrl+Shift+Enter (Cmd+Shift+Enter на Mac); в Excel 2021 и Microsoft 365 достаточно Enter. Тильда делает подстановочный знак обычным символом: "~*" ищет настоящую звёздочку.
Есть ли значение в списке
ПОИСКПОЗ возвращает число, когда находит значение, и #Н/Д, когда не находит. ЕЧИСЛО превращает это в ИСТИНА или ЛОЖЬ, и получается быстрая проверка «есть ли это в списке».
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ordered | In stock? | In stock | |
| 2 | Pear | Yes | Apple | |
| 3 | Kiwi | No | Pear | |
| 4 | Milk | Yes | Bread | |
| 5 | Plum | No | Milk | |
| 6 | Bread | Yes |
=ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(A2;$D$2:$D$5;0));"Yes";"No")Pear, Milk и Bread есть в D2:D5, поэтому показывают Yes. Kiwi и Plum показывают No. В русском Excel формула в B2 пишется =ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ(A2;$D$2:$D$5;0));"Yes";"No"). СЧЁТЕСЛИ($D$2:$D$5;A2)>0 даёт тот же ответ; ПОИСКПОЗ останавливается на первом совпадении, СЧЁТЕСЛИ считает все.
ПОИСКПОЗ, ПОИСКПОЗX и ИНДЕКС
ПОИСКПОЗ это половина классического поиска =ИНДЕКС(C2:C6;ПОИСКПОЗ(E2;A2:A6;0)), который объясняется на странице ИНДЕКС и ПОИСКПОЗ. В Excel 2021 и Microsoft 365 появилась ПОИСКПОЗX: по умолчанию она ищет точное совпадение, умеет искать с последнего элемента вверх и находит «следующее большее» без сортировки по убыванию. ПОИСКПОЗ по-прежнему работает везде, в том числе в файлах, которые должны открываться в Excel 2019.
Практика: позиция сотрудника
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Team | Start year | Name | Position | |
| 2 | Ana | Sales | 2019 | Dev | ||
| 3 | Ben | Support | 2021 | |||
| 4 | Cara | Sales | 2018 | |||
| 5 | Dev | Finance | 2022 | |||
| 6 | Eli | Support | 2020 | |||
| 7 | Fay | Finance | 2023 |
Ваша очередь: В F2 верните позицию имени из E2 в списке столбца A.
Часто задаваемые вопросы
Что возвращает ПОИСКПОЗ в Excel?
Позицию, а не значение: =ПОИСКПОЗ("Bread";A2:A6;0) возвращает 4, когда Bread четвёртая ячейка A2:A6. Используйте её вместе с ИНДЕКС, чтобы получить значение на этой позиции из другого столбца.
Чем отличаются типы сопоставления 0, 1 и -1?
0 находит точное совпадение в любом порядке. 1 (по умолчанию) находит наибольшее значение, которое меньше искомого или равно ему, и требует списка, отсортированного по возрастанию. -1 находит наименьшее значение, которое больше искомого или равно ему, и требует списка, отсортированного по убыванию.
Учитывает ли ПОИСКПОЗ регистр?
Нет. =ПОИСКПОЗ("milk";A2:A6;0) находит Milk. Для поиска с учётом регистра используйте =ПОИСКПОЗ(ИСТИНА;СОВПАД(A2:A6;"milk");0), а в Excel 2019 и старше вводите её через Ctrl+Shift+Enter (Cmd+Shift+Enter на Mac).
Как проверить через ПОИСКПОЗ, есть ли значение в списке?
ПОИСКПОЗ возвращает #Н/Д, когда значения нет, поэтому проверьте, вернула ли она число: =ЕЧИСЛО(ПОИСКПОЗ(A2;$D$2:$D$5;0)) даёт ИСТИНА или ЛОЖЬ. Оберните в ЕСЛИ, чтобы показать свой текст.
