ВПР, ПРОСМОТРX и ИНДЕКС+ПОИСКПОЗ: что и когда использовать

Короткий ответ

• ПРОСМОТРX (XLOOKUP) — современный выбор: ищет и влево, и вправо, по умолчанию использует точное совпадение и умеет сам обработать «не найдено». Есть в Excel 365, Excel 2021 и новее, а также в Google Таблицах.
• ИНДЕКС + ПОИСКПОЗ (INDEX + MATCH) — работает везде, включая старые версии Excel, ищет в любом направлении и не ломается при вставке столбцов.
• ВПР (VLOOKUP) — самая известная, но самая хрупкая: ищет только в первом столбце диапазона и возвращает значение по номеру столбца.

Примеры ниже написаны для русской версии Excel, где аргументы разделяются точкой с запятой. В английском интерфейсе и в Google Таблицах используются английские названия функций, а разделитель зависит от региональных настроек.

Пример данных

На листе «Товары»: столбец A — артикул, B — название, C — цена. На листе «Заказы» в ячейке A2 стоит артикул, и нужно подтянуть его цену.

ВПР

=ВПР(A2; Товары!A:C; 3; ЛОЖЬ)

• Аргументы: что ищем, где ищем (ключ обязательно в первом столбце), номер столбца с результатом, тип совпадения.
• ЛОЖЬ (или 0) — точное совпадение. Если четвёртый аргумент пропустить, ВПР включит приблизительный поиск и на несортированных данных вернёт чужую строку без всякой ошибки. Это самая частая проблема с ВПР.
• Искать влево нельзя: если артикул стоит правее названия, придётся переставлять столбцы.
• Номер столбца «зашит» в формулу. Вставили столбец в середину таблицы — формула молча начнёт возвращать другие данные.

ИНДЕКС + ПОИСКПОЗ

=ИНДЕКС(Товары!C:C; ПОИСКПОЗ(A2; Товары!A:A; 0))

• ПОИСКПОЗ находит номер строки с ключом. 0 в конце — точное совпадение; по умолчанию стоит 1, то есть приблизительный поиск.
• ИНДЕКС берёт значение из нужного столбца по этому номеру.
• Столбец результата может быть где угодно, в том числе левее ключа.
• Формула ссылается на конкретные столбцы, поэтому вставка новых её не ломает.
• Удобна для поиска по строке и столбцу одновременно: =ИНДЕКС(B2:M50; ПОИСКПОЗ(H1; A2:A50; 0); ПОИСКПОЗ(H2; B1:M1; 0)) — например, продажи товара из H1 за месяц из H2.

ПРОСМОТРX

=ПРОСМОТРX(A2; Товары!A:A; Товары!C:C; "Нет в каталоге")

• Аргументы: что ищем, где ищем, откуда берём результат, что показать, если ничего не найдено.
• Точное совпадение по умолчанию — забыть про «ЛОЖЬ» уже не получится.
• Пятый аргумент — режим сопоставления: 0 — точное, -1 — точное или ближайшее меньшее, 1 — точное или ближайшее большее, 2 — с подстановочными знаками * и ?.
• Шестой аргумент — направление: -1 ищет с конца и находит последнее вхождение, например последний заказ клиента.
• Может вернуть сразу несколько столбцов: =ПРОСМОТРX(A2; Товары!A:A; Товары!B:C).

Точный и приблизительный поиск

Приблизительный поиск нужен для шкал: скидки от суммы заказа, ставки, грейды. Например, таблица «от 0 — 0 %, от 1 000 000 — 5 %, от 5 000 000 — 10 %», и нужно найти скидку для суммы 3 200 000.

• ВПР с ИСТИНА и ПОИСКПОЗ с 1 требуют, чтобы первый столбец был отсортирован по возрастанию, иначе результат непредсказуем.
• ПРОСМОТРX с режимом -1 сортировки не требует: =ПРОСМОТРX(D2; Шкала!A:A; Шкала!B:B; ; -1).

Во всех остальных случаях — справочники, артикулы, ID, e-mail — используйте только точное совпадение.

Ошибки и скорость

На таблицах в несколько тысяч строк разница в скорости между функциями незаметна. На больших файлах помогают простые приёмы:

• ограничивать диапазоны или использовать «умные таблицы» Excel (Ctrl+T), а не ссылаться на весь лист без необходимости;
• если из одной строки нужно достать несколько полей, считать ПОИСКПОЗ один раз во вспомогательном столбце и ссылаться на него из нескольких ИНДЕКС;
• на гарантированно отсортированных данных включать двоичный поиск — значения 2 или -2 в шестом аргументе ПРОСМОТРX.

Не прячьте все ошибки за ЕСЛИОШИБКА: она скрывает и опечатки в самой формуле. Для поиска лучше ЕСНД — она перехватывает только #Н/Д.

Как выбрать

1. Файл открывают только в Excel 365/2021+ или Google Таблицах — ПРОСМОТРX.
2. Файлом пользуются и в старых версиях Excel — ИНДЕКС+ПОИСКПОЗ.
3. Маленькая стабильная таблица, ключ слева, структура не меняется — ВПР допустим.