XLOOKUP и VLOOKUP (ПРОСМОТРX и ВПР): отличия, примеры и что выбрать

Если выбирать между XLOOKUP и VLOOKUP, используйте XLOOKUP, когда у всех, кто открывает файл, установлен Excel для Microsoft 365, Excel 2021 или новее либо Excel для Интернета. Он делает всё то же, что и VLOOKUP, но с меньшим числом ошибок: по умолчанию ищет точное совпадение, умеет искать влево и не ломается при вставке столбца. VLOOKUP стоит использовать, только если книга должна работать в Excel 2019 или более старых версиях.

Совместимость — главная оговорка. В Excel 2016 и 2019 функции XLOOKUP нет, поэтому в файле, полном формул XLOOKUP, у тех, кто всё ещё работает в этих версиях, появятся ошибки #ИМЯ? (#NAME?).

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

В английской версии вместо ; ставится запятая.

Синтаксис бок о бок

В русской версии Excel:

=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
=ПРОСМОТРX(искомое_значение; просматриваемый_массив; возвращаемый_массив; [если_не_найдено]; [режим_сопоставления]; [режим_поиска])

В английской версии Excel:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Главное отличие в том, как вы сообщаете Excel, где искать ответ. VLOOKUP принимает всю таблицу и номер столбца, который отсчитывается от левого края этой таблицы. XLOOKUP принимает два отдельных диапазона: столбец, в котором нужно искать, и столбец, из которого нужно вернуть результат.

Один и тот же поиск, записанный двумя способами

Вот небольшой список товаров в A1:D6 и идентификатор товара, введённый в G2 (данные примера приведены на английском, как на рисунках):

Чтобы найти цену товара P-104:

=ВПР(G2; A2:D6; 4; ЛОЖЬ)
=ПРОСМОТРX(G2; A2:A6; D2:D6)

Обе формулы вернут $59.00. ВПР ищет значение в первом столбце диапазона A2:D6 и возвращает значение из 4-го столбца; ЛОЖЬ требует точного совпадения. ПРОСМОТРX ищет в A2:A6 и возвращает соответствующую ячейку из D2:D6, причём точное совпадение действует по умолчанию.

Если вы будете протягивать формулу вниз по столбцу, зафиксируйте таблицу абсолютными ссылками, например =ПРОСМОТРX(G2; $A$2:$A$6; $D$2:$D$6). Нажмите F4 в Windows или Cmd+T на Mac, когда курсор находится в ссылке, чтобы добавить знаки $.

Две классические ловушки ВПР

1. Забытая ЛОЖЬ (или 0)

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

Попробуйте =ВПР("P-110"; A2:D6; 4) на таблице выше. Товара P-110 нет, но вместо #Н/Д (#N/A) вы получите $32.75, цену P-105, потому что «P-105» — ближайшее меньшее значение. На неотсортированных данных результаты могут быть ещё более случайными.

Всегда заканчивайте ВПР с точным совпадением значением ЛОЖЬ или 0. ПРОСМОТРX обходит эту проблему, потому что использует точное совпадение, если вы не укажете иное.

2. Жёстко заданный номер столбца

Цифра 4 в формуле ВПР — просто число. Если кто-то вставит столбец Supplier между Product и Category, таблица станет A:E, Price переместится в столбец E, а формула по-прежнему будет возвращать 4-й столбец, которым теперь стал Category. Вы увидите «Video» вместо $59.00.

ПРОСМОТРX указывает на сам столбец с ценами (D2:D6), поэтому Excel автоматически обновит его до E2:E6, и ответ останется верным.

Что умеет XLOOKUP и не умеет VLOOKUP

Искать влево

VLOOKUP может возвращать только столбцы справа от столбца поиска. XLOOKUP направление не волнует. Чтобы найти идентификатор товара по названию из G2:

=ПРОСМОТРX(G2; B2:B6; A2:A6)

Если в G2 находится «Desk Lamp», формула вернёт P-105, хотя столбец A находится левее столбца B.

Показать понятное сообщение, если ничего не найдено

=ПРОСМОТРX(G2; A2:A6; D2:D6; "Не найдено")

Аналог на ВПР требует обёртки: =ЕНД(ВПР(G2; A2:D6; 4; ЛОЖЬ); "Не найдено").

Вернуть несколько столбцов одной формулой

=ПРОСМОТРX(G2; A2:A6; B2:D6)

Для P-104 формула разольёт Webcam HD, Video и $59.00 по трём ячейкам в ряд. Оставьте эти ячейки пустыми, иначе получите ошибку #ПЕРЕНОС! (#SPILL!).

Найти последнее совпадение вместо первого

В журнале, где один и тот же клиент встречается много раз, задайте режим_поиска равным -1, чтобы искать снизу вверх и получить самую свежую запись:

=ПРОСМОТРX(G2; A2:A500; C2:C500; "Не найдено"; 0; -1)

Приблизительное совпадение без сортировки

Для ступенчатых цен или ставок комиссии приблизительное совпадение ВПР работает, только если первый столбец отсортирован по возрастанию. Значения режим_сопоставления -1 (точное совпадение или ближайший меньший элемент) и 1 (точное совпадение или ближайший больший элемент) в XLOOKUP не требуют отсортированных данных. Пусть минимальные суммы заказа находятся в J2:J5, а скидки в K2:K5:

=ВПР(B2; J2:K5; 2; ИСТИНА)
=ПРОСМОТРX(B2; J2:J5; K2:K5; ; -1)

Подстановочные знаки

Обе функции поддерживают * и ?, но XLOOKUP требует режим_сопоставления 2, чтобы воспринимать их как подстановочные знаки:

=ВПР("Desk*"; B2:D6; 3; ЛОЖЬ)
=ПРОСМОТРX("Desk*"; B2:B6; D2:D6; ; 2)

Обе формулы возвращают $32.75 для Desk Lamp.

INDEX/MATCH (ИНДЕКС/ПОИСКПОЗ): классическая альтернатива

До появления XLOOKUP стандартным решением ограничений VLOOKUP была связка ИНДЕКС с ПОИСКПОЗ. Она умеет искать влево, переживает вставку столбцов и работает во всех версиях Excel, поэтому это лучший выбор для файлов, которые должны открываться в Excel 2019 и старше:

=ИНДЕКС(D2:D6; ПОИСКПОЗ(G2; A2:A6; 0))
=ИНДЕКС(A2:A6; ПОИСКПОЗ(G2; B2:B6; 0))

Первая формула возвращает цену для идентификатора из G2; вторая — пример поиска влево. Нуль в ПОИСКПОЗ означает точное совпадение, и, как и ЛОЖЬ в ВПР, о нём легко забыть. XLOOKUP — это просто более короткая и безопасная версия той же идеи.

Как преобразовать формулы ВПР в ПРОСМОТРX

Встроенного конвертера нет, но преобразование механическое:

1. Найдите свои ВПР: нажмите Ctrl+F (Cmd+F на Mac), введите ВПР, нажмите Параметры, если дополнительные настройки скрыты, в списке Область поиска выберите формулы и нажмите Найти все.
2. Первый аргумент (искомое значение) оставьте как есть.
3. Замените таблицу её первым столбцом в качестве просматриваемый_массив.
4. Замените номер столбца соответствующим столбцом в качестве возвращаемый_массив. Столбец 5 таблицы, которая начинается со столбца A, — это столбец E.
5. Уберите ЛОЖЬ. Если исходная формула заканчивалась на ИСТИНА или последнего аргумента не было, добавьте режим_сопоставления -1, чтобы сохранить поведение «ближайший меньший».

Например:

=ВПР(A2; Products!$A$2:$F$500; 5; ЛОЖЬ)
=ПРОСМОТРX(A2; Products!$A$2:$A$500; Products!$E$2:$E$500)

Прежде чем преобразовывать целый лист, сравните несколько результатов со старой формулой. ВПР с приблизительным совпадением на неотсортированных данных, возможно, всё это время возвращал неверные значения, поэтому ответы ПРОСМОТРX могут закономерно отличаться.

Что происходит, когда файл с XLOOKUP открывают в Excel 2016 или 2019

Книга откроется, но эти версии не знают такой функции. В строке формул она отображается как =_xlfn.XLOOKUP(...). Сначала ячейки могут по-прежнему показывать последние сохранённые результаты, но как только формула пересчитается, она вернёт #ИМЯ?.

Если вы обмениваетесь файлами с людьми, у которых старые версии:

• используйте вместо этого ИНДЕКС/ПОИСКПОЗ или ВПР, либо
• отправьте копию, где результаты вставлены как значения (Копировать → Специальная вставка → Значения).

То же относится к другим новым функциям, например ФИЛЬТР, УНИК и ПОИСКПОЗX (XMATCH).

XLOOKUP в Google Sheets

В Google Sheets тоже есть XLOOKUP с тем же порядком аргументов: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]). Формулы из этой статьи работают там в том же виде (с английскими названиями функций, а при русскоязычных настройках аргументы разделяются точкой с запятой; в русскоязычном интерфейсе названия функций могут отображаться иначе), поэтому XLOOKUP — надёжный выбор для файлов, которые переходят между Excel и Sheets.

Почему поиск возвращает #Н/Д или неверное значение

• Лишние пробелы. «P-104 » с пробелом в конце не совпадёт с «P-104». Очистите данные с помощью СЖПРОБЕЛЫ.
• Числа, сохранённые как текст. Идентификатор, введённый как текст, не совпадёт с тем же идентификатором, сохранённым как число. Найдите зелёный треугольник в углу ячейки и приведите столбец к одному типу.
• Нет ЛОЖЬ в ВПР. Вместо #Н/Д вы получаете правдоподобный, но неверный ответ.
• Диапазоны сместились при копировании формулы. Используйте для таблицы поиска абсолютные ссылки со знаком $.
• #ССЫЛКА! от ВПР. Номер столбца больше, чем число столбцов в таблице.
• #ЗНАЧ! от ПРОСМОТРX. Диапазон поиска и диапазон результатов имеют разный размер, например A2:A6 и D2:D60.
• #ИМЯ? Название функции написано с ошибкой, либо файл открыт в версии, где нет XLOOKUP.

Часто задаваемые вопросы

XLOOKUP лучше, чем VLOOKUP? Да, почти для любых задач. Он по умолчанию ищет точное совпадение, смотрит в любом направлении, не ломается при перемещении столбцов и обрабатывает отсутствующие значения без обёртки. Единственное реальное преимущество VLOOKUP — работа во всех версиях Excel.

XLOOKUP быстрее, чем VLOOKUP? В обычных книгах разницы вы не заметите. На очень больших отсортированных списках режимы двоичного поиска XLOOKUP (режим_поиска 2 или -2) могут ускорить работу, но дают неверные результаты, если данные не отсортированы.

Почему на моём компьютере XLOOKUP выдаёт #ИМЯ? В вашей версии Excel нет XLOOKUP. Он есть в Microsoft 365, Excel 2021 и новее и Excel для Интернета, но не в Excel 2019 и 2016. Используйте вместо него ИНДЕКС/ПОИСКПОЗ.

XLOOKUP заменяет и ГПР (HLOOKUP)? Да. Если диапазоны поиска и результатов представляют собой строки, а не столбцы, XLOOKUP ищет по горизонтали с тем же синтаксисом.

Может ли XLOOKUP вернуть все совпадения, а не только первое? Нет, он возвращает одно совпадение (или одну строку столбцов). Чтобы получить все подходящие строки, используйте ФИЛЬТР, например =ФИЛЬТР(A2:D6; C2:C6="Office") (в английской версии =FILTER(A2:D6, C2:C6="Office")).

Нужно ли ещё изучать VLOOKUP? Его стоит уметь распознавать, потому что вы встретите его в старых книгах и шаблонах. Для новых формул изучайте XLOOKUP и помните об ИНДЕКС/ПОИСКПОЗ для файлов, которые должны открываться в старых версиях Excel.

Пусть формулу поиска напишет ИИ

Именно в формулах поиска мелочи, вроде пропущенной ЛОЖЬ или смещённого диапазона, приводят к самым незаметным ошибкам. CoreGPT для Excel позволяет описать, что вам нужно, например: «найди цену по идентификатору товара из G2» или «отметь каждую строку как выше или ниже плана», и записывает формулу на ваш лист с коротким пояснением. Начать можно бесплатно, а работает он в Excel для Windows, Mac и Интернета.

Попробуйте: CoreGPT для Excel

Читайте также:

• Как удалить дубликаты в Excel: 5 способов (с удалением данных и без него)
• Советы по Excel в Microsoft 365, которые экономят время каждую неделю
• Как использовать ChatGPT в Excel: полное руководство