Функция ПРОСМОТРX в Excel (XLOOKUP): как работает, пример и формула под вашу таблицу
Синтаксис в русском Excel
В английском Excel, Google Таблицах и внутри файла .xlsx
Аргументы
искомое | что ищем |
просматриваемый_массив | где ищем |
возвращаемый_массив | откуда вернуть |
[если_не_найдено] | что показать, если нет |
Пример
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Артикул | Товар | Цена | Артикул | ||
| 2 | ШВ-1187 | Шуруповёрт 18 В | 7990 | УШМ-0915 | 4750 | |
| 3 | ДР-2041 | Дрель ударная 750 Вт | 5490 | |||
| 4 | УШМ-0915 | Болгарка 125 мм | 4750 | |||
| 5 | ЛЗ-4410 | Лазерный уровень | 3780 |
Результат в ячейке F2 посчитан LibreOffice на сервере Формулника.
Частые ошибки
функции нет — замена: ИНДЕКС(возврат; ПОИСКПОЗ(искомое; поиск; 0))
Где работает
| Программа | ПРОСМОТРX | |
|---|---|---|
| Excel 2010 | нет | появилась в Excel 2021 |
| Excel 2013 | нет | появилась в Excel 2021 |
| Excel 2016 | нет | появилась в Excel 2021 |
| Excel 2019 | нет | появилась в Excel 2021 |
| Excel 2021 | есть | |
| Excel 2024 | есть | |
| Excel 365 | есть | |
| LibreOffice Calc | есть | проверено |
| Google Таблицы | проверьте | новая функция Excel 2021 — в Google Таблицы не проверена |
| Яндекс Таблицы | проверьте | новая функция Excel 2021 — в Яндекс Таблицы не проверена |
| Р7-Офис | проверьте | новая функция Excel 2021 — в Р7-Офис не проверена |
| МойОфис | проверьте | новая функция Excel 2021 — в МойОфис не проверена |
| MOffice Sheets | есть | проверено |
Похожие функции
ПРОСМОТРX: последняя цена, вся строка сразу, шкалы и поиск по двум осям
ПРОСМОТРX принимает до шести аргументов: кроме трёх обязательных, можно задать текст на случай «не найдено», режим сопоставления (0 — точно, -1 — точно или ближайшее меньшее, 1 — ближайшее большее, 2 — подстановочные знаки) и направление поиска (-1 — с конца). Последний режим решает задачу, которую ВПР не умеет: найти последнюю по дате цену товара. Работает в Excel 2021, Microsoft 365, Excel в браузере и LibreOffice 24.8 и новее.
По шагам
- Убедитесь, что просматриваемый и возвращаемый диапазоны одной высоты — иначе сразу будет #ЗНАЧ!.
- Для поиска с конца или по шкале заполните пятый и шестой аргументы; если «не найдено» не нужно, четвёртый оставьте пустым:
;;. - Чтобы вернуть сразу несколько столбцов, третьим аргументом выделите их все — результат разольётся вправо.
- Освободите ячейки справа и снизу от формулы: если они заняты, появится #ПЕРЕНОС!.
- Перед отправкой файла узнайте версию Excel получателя: в 2016 и 2019 вместо результата будет #ИМЯ?.
Последняя закупочная цена товара
Журнал закупок ведётся по датам сверху вниз: A — дата, B — артикул, C — цена. Артикул ШВ-1187 встречается десятки раз, а нужна свежая цена. Шестой аргумент -1 заставляет искать снизу вверх, и первой найдётся самая поздняя запись. Если журнал не отсортирован, сначала отсортируйте его по дате, иначе «последняя» строка будет просто нижней.
=ПРОСМОТРX(F2;$B$2:$B$2000;$C$2:$C$2000;"нет закупок";0;-1)Вернуть товар, цену и остаток одной формулой
Третьим аргументом выделите три соседних столбца — ПРОСМОТРX вернёт всю найденную строку, и она разольётся в три ячейки. Это заменяет три отдельные ВПР с номерами столбцов 2, 3 и 4 и не ломается, если в прайс вставят новый столбец между ними.
=ПРОСМОТРX(F2;Прайс!$A$2:$A$500;Прайс!$B$2:$D$500;"нет")Скидка по сумме заказа без сортировки шкалы
Пороги 0, 50 000, 100 000 и 300 000 ₽ лежат в H2:H5, скидки — в I2:I5. Режим -1 берёт точное совпадение или ближайшее меньшее значение. В отличие от ВПР с последним аргументом 1, шкалу не обязательно сортировать — ПРОСМОТРX сам найдёт подходящий порог.
=ПРОСМОТРX(B2;$H$2:$H$5;$I$2:$I$5;0;-1)Тариф по зоне доставки и весу: пересечение строки и столбца
Таблица тарифов: в A3:A12 — зоны, в B2:G2 — весовые категории, внутри — цены. Внутренний ПРОСМОТРX находит нужный столбец веса и возвращает его целиком, внешний ищет в этом столбце строку зоны. Раньше ту же задачу решали через ИНДЕКС с двумя ПОИСКПОЗ.
=ПРОСМОТРX(K2;$A$3:$A$12;ПРОСМОТРX(L2;$B$2:$G$2;$B$3:$G$12))Режимы ПРОСМОТРX
| Аргумент | Значение | Что делает |
|---|---|---|
| режим_сопоставления | 0 (по умолчанию) | только точное совпадение |
| режим_сопоставления | -1 | точное или ближайшее меньшее — шкалы скидок и налогов |
| режим_сопоставления | 1 | точное или ближайшее большее — подбор тары по объёму |
| режим_сопоставления | 2 | подстановочные знаки: "*дрель*" |
| режим_поиска | -1 | с последней строки — последняя запись |
| режим_поиска | 2 / -2 | двоичный поиск по отсортированным данным — быстро на сотнях тысяч строк |
Частые ошибки
- #ЗНАЧ! сразу после ввода — диапазоны поиска и возврата разной высоты, например
A2:A500иC2:C499. Выделяйте одинаковые строки. - Вместо цены — #ПЕРЕНОС! — третьим аргументом выделено несколько столбцов, а соседние ячейки заняты. Освободите место или верните один столбец.
- В режиме 2 звёздочка не срабатывает — подстановочные знаки включаются только пятым аргументом 2; в режиме 0 звёздочка ищется как обычный символ.
Вопросы и ответы
Чем ПРОСМОТРX лучше ИНДЕКС и ПОИСКПОЗ?
Одна функция вместо двух, своё сообщение вместо #Н/Д, поиск с конца и возврат нескольких столбцов. ИНДЕКС+ПОИСКПОЗ остаются нужны для файлов, которые откроют в Excel 2016–2019.
Как найти по двум условиям через ПРОСМОТРX?
Перемножьте проверки и ищите единицу: =ПРОСМОТРX(1;($A$2:$A$500=F2)*($B$2:$B$500=G2);$C$2:$C$500;"нет"). Вспомогательный столбец не нужен.
Что увидит коллега с Excel 2019, если отправить ему файл с ПРОСМОТРX?
Пока книга не пересчитана, он увидит сохранённые значения, но после правки любой связанной ячейки появится #ИМЯ?. Для таких получателей замените формулы значениями или используйте ИНДЕКС с ПОИСКПОЗ.
Можно ли использовать результат ПРОСМОТРX как ссылку?
Да, функция возвращает ссылку на ячейку, поэтому её можно ставить по краям диапазона: =СУММ(ПРОСМОТРX(H1;A2:A400;C2:C400):ПРОСМОТРX(H2;A2:A400;C2:C400)) — сумма между двумя датами.
Есть ли ПРОСМОТРX в Р7-Офис?
Зависит от версии редактора: в новых версиях функция поддерживается, в старых её нет. Если не уверены, проверьте на одной строке перед массовой рассылкой файла.