ПРОСМОТРX (по-английски XLOOKUP) делает всё, что делает ВПР (VLOOKUP), и ошибиться в ней сложнее: по умолчанию она ищет точное совпадение, столбец результата задаётся диапазоном, а не числом, она умеет искать влево и имеет свой аргумент «не найдено». Используйте ВПР, когда файл должен работать в Excel 2019 или старше, где ПРОСМОТРX нет. Таблица ниже выполняет один и тот же поиск обоими способами. Формулы в ней записаны по-английски, но их можно вводить и по-русски, с точкой с запятой: =ВПР(G1;A2:D6;3;ЛОЖЬ).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Pear | |
| 2 | Apple | Fruit | $1.20 | 40 | VLOOKUP | $1.50 | |
| 3 | Pear | Fruit | $1.50 | 25 | XLOOKUP | $1.50 | |
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
=ВПР(G1;A2:D6;3;ЛОЖЬ)Обе возвращают $1.50. Щёлкните каждую формулу: ВПР обводит всю таблицу A2:D6 и отсчитывает в ней 3 столбца; ПРОСМОТРX обводит только столбец, в котором ищет, и столбец, который возвращает.
Сравнение ВПР и ПРОСМОТРX
| ВПР | ПРОСМОТРX | |
|---|---|---|
| Версии Excel | Все | Excel 2021, 2024, Microsoft 365, веб |
| Совпадение по умолчанию | Приблизительное (если 4-й аргумент пропущен) | Точное |
| Столбец результата | Число, отсчитанное внутри таблицы | Диапазон |
| Вставка столбца внутрь таблицы | Возвращает не тот столбец | Продолжает работать |
| Поиск влево | Нет | Да |
| Значение не найдено | #Н/Д, обернуть в ЕСНД | 4-й аргумент, "Not found" |
| Последнее совпадение | Нет | search_mode -1 |
| Несколько столбцов сразу | Один на формулу (или {2;3} как номер столбца в Microsoft 365) | Диапазон результата в несколько столбцов выводит их все |
| Приблизительное совпадение | Следующее меньшее, данные должны быть отсортированы | Следующее меньшее или большее, любой порядок |
| Подстановочные знаки | Включены при ЛОЖЬ | Только с match_mode 2 |
| Горизонтальный поиск | Нужна ГПР | Та же функция |
Обе не учитывают регистр. При точном совпадении обе возвращают первое совпадение сверху, если ПРОСМОТРX не велели искать снизу.
Не найдено: ЕСНД или четвёртый аргумент
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Kiwi | |
| 2 | Apple | Fruit | $1.20 | 40 | VLOOKUP | #N/A | |
| 3 | Pear | Fruit | $1.50 | 25 | with IFNA | Not found | |
| 4 | Carrot | Vegetable | $0.80 | 60 | XLOOKUP | Not found | |
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
#N/A Искомого значения нет в диапазоне поиска.В русском Excel: =ВПР(G1;A2:D6;3;ЛОЖЬ)Обычная ВПР показывает #Н/Д (в таблице по-английски #N/A; таблицы здесь показывают ошибки под английскими именами). Чтобы показать текст, ВПР нужна обёртка ЕСНД (IFNA), а ПРОСМОТРX делает это своим четвёртым аргументом. Введите Milk в G1, и все три совпадут: $1.10.
Поиск влево и перемещение столбцов
Два структурных ограничения ВПР происходят из номера столбца. Она может отсчитывать только вправо от столбца, по которому ищет, а номер не меняется вместе с таблицей: вставьте столбец между Category и Price, и =ВПР(G1;A2:D6;3;ЛОЖЬ) по-прежнему вернёт столбец 3, теперь уже новый. ПРОСМОТРX ссылается на столбец результата как на диапазон, поэтому Excel поправляет его, как любую другую ссылку, а столбец поиска может стоять где угодно.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Price | $0.80 | |
| 2 | Apple | Fruit | $1.20 | 40 | XLOOKUP | Carrot | |
| 3 | Pear | Fruit | $1.50 | 25 | INDEX MATCH | Carrot | |
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
=ПРОСМОТРX(G1;C2:C6;A2:A6)Обе возвращают Carrot. Варианта с ВПР нет: название стоит левее цены. В старом Excel ответ это ИНДЕКС и ПОИСКПОЗ, как в G3, и эта связка тоже переживает вставку столбцов. Её объясняет страница ИНДЕКС и ПОИСКПОЗ.
Приблизительное совпадение обоими способами
Для интервалов ВПР использует ИСТИНА и требует, чтобы первый столбец был отсортирован по возрастанию. ПРОСМОТРX использует match_mode -1 и сортировки не требует, а match_mode 1 даёт вместо этого следующее большее значение, чего ВПР не умеет.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Sales from | Rate | Sales | 4,200 | |
| 2 | 0 | 0% | VLOOKUP | 3% | |
| 3 | 1000 | 3% | XLOOKUP | 3% | |
| 4 | 5000 | 5% | |||
| 5 | 10000 | 8% |
=ВПР(E1;A2:B5;2;ИСТИНА)Обе возвращают 3% для 4,200. Замените E1 на 10000, и обе вернут 8%.
Когда оставить ВПР
- Файлом пользуются люди с Excel 2019, 2016 или старше. Там ПРОСМОТРX показывает #ИМЯ? (#NAME?). Другой вариант, который работает везде, это ИНДЕКС и ПОИСКПОЗ.
- В книге уже сотни работающих ВПР. Переписывать их почти бессмысленно; используйте ПРОСМОТРX для новых формул.
Скорость не причина выбирать ту или другую. На обычных листах обе мгновенны, а на очень больших отсортированных списках быстры и двоичный поиск ПРОСМОТРX (search_mode 2), и ВПР с ИСТИНА.
Google Таблицы поддерживают обе функции с теми же аргументами.
Практика: перепишите ВПР
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Bread | |
| 2 | Apple | Fruit | $1.20 | 40 | Stock | ||
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
Ваша очередь: =VLOOKUP(G1,A2:D6,4,FALSE) возвращает остаток товара из G1. В G2 напишите тот же поиск через ПРОСМОТРX.
Часто задаваемые вопросы
ПРОСМОТРX лучше, чем ВПР?
Для новых формул в Excel 2021 или Microsoft 365 да: она по умолчанию ищет точное совпадение, у неё нет номера столбца, который ломается при перемещении столбцов, она ищет влево и имеет встроенный текст «не найдено». ВПР лучше только тогда, когда файл должен работать в Excel 2019 или старше, где ПРОСМОТРX показывает #ИМЯ?.
ПРОСМОТРX быстрее, чем ВПР?
На обычных листах разницы не заметно: обе мгновенно просматривают несколько тысяч строк. На очень больших отсортированных данных двоичный поиск ПРОСМОТРX (search_mode 2) быстрее линейного, а ВПР с ИСТИНА тоже ищет двоичным поиском.
Чем ПРОСМОТРX отличается от ИНДЕКС и ПОИСКПОЗ?
Они умеют одни и те же виды поиска. ПРОСМОТРX это одна функция с более простыми аргументами и аргументом «не найдено»; ИНДЕКС и ПОИСКПОЗ работают в любой версии Excel. =ПРОСМОТРX(F2;A2:A6;C2:C6) и =ИНДЕКС(C2:C6;ПОИСКПОЗ(F2;A2:A6;0)) возвращают одно и то же значение.
Как переписать ВПР в ПРОСМОТРX?
Оставьте искомое значение, разделите таблицу на первый столбец и столбец, который возвращали, и уберите номер столбца и ЛОЖЬ: =ВПР(F2;A2:D6;3;ЛОЖЬ) превращается в =ПРОСМОТРX(F2;A2:A6;C2:C6).
