Функции поиска в Excel нужны в тех случаях, когда нужно быстро подтянуть данные из одной таблицы в другую: цену по артикулу, фамилию по табельному номеру, остаток по складу, статус заказа, ставку НДС, номер договора или любую другую связанную информацию. Долгое время главным инструментом для таких задач была функция ВПР. Она до сих пор активно используется в отчетах, прайс-листах и рабочих файлах, но в новых версиях Excel у нее появился более гибкий и удобный преемник — ПРОСМОТРX, также известный как XLOOKUP.
Excel: функции ВПР и ПРОСМОТРX — полное руководство
В этом руководстве разберем, чем отличаются ВПР и ПРОСМОТРX, как правильно писать формулы, почему появляются ошибки, как искать точное совпадение, как подтягивать данные слева от ключевого столбца и в каких случаях лучше использовать новую функцию. Все примеры ниже построены на одной логике: есть справочник товаров с артикулами, названиями, ценами и остатками, а в отдельной таблице нужно получить нужные данные по артикулу.
Важно: в названии функции ПРОСМОТРX последняя буква — латинская X, а не русская «Х». Визуально они почти одинаковые, но для Excel это разные символы. Если написать русскую «Х», формула может не распознаться и вернуть ошибку имени функции.
Что делает функция ВПР в Excel
ВПР расшифровывается как «вертикальный просмотр». Функция ищет значение в первом столбце указанного диапазона и возвращает данные из другого столбца той же строки. Это удобно, когда справочник устроен классическим способом: слева находится ключ, например артикул или код клиента, а справа расположены нужные сведения.
Типичный пример: в одной таблице есть заказ с артикулами товаров, а во второй — справочник товаров с названиями и ценами. Вместо ручного копирования цены можно написать формулу ВПР, и Excel сам найдет нужную строку.
Синтаксис функции выглядит так:
=ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)
Где:
— искомое_значение — то, что нужно найти, например артикул товара.
— таблица — диапазон, в котором выполняется поиск.
— номер_столбца — номер столбца внутри выбранного диапазона, из которого нужно вернуть результат.
— интервальный_просмотр — режим поиска. Для точного совпадения обычно используется ЛОЖЬ или 0.
В примере со справочником товаров артикулы находятся в столбце A, названия — в столбце B, цены — в столбце C, остатки — в столбце D. Если в ячейке F5 указан артикул из заказа, название товара можно вернуть так:
=ВПР(F5;$A$5:$D$12;2;ЛОЖЬ)
Эта формула ищет значение из ячейки F5 в первом столбце диапазона A5:D12 и возвращает значение из второго столбца найденной строки, то есть название товара.
Главное правило ВПР: ключевой столбец должен быть первым
Самое важное ограничение ВПР состоит в том, что функция ищет значение только в первом столбце выбранного диапазона. Если вы указали диапазон A5:D12, поиск будет выполняться только в столбце A. Вернуть данные можно из столбцов B, C или D, но нельзя вернуть значение из столбца, который находится левее ключевого столбца.
Например, если в справочнике структура такая:
— столбец A — название товара;
— столбец B — артикул;
— столбец C — цена.
И нужно найти название по артикулу, ВПР напрямую не справится, потому что артикул находится не в первом столбце. Придется переставлять столбцы, создавать вспомогательный диапазон или использовать другую функцию. Именно здесь особенно полезна ПРОСМОТРX.
В таком примере артикул для поиска находится в ячейке E5, список артикулов — в диапазоне B5:B10, а названия товаров — в A5:A10. Рабочая формула через ПРОСМОТРX будет такой:
=ПРОСМОТРX(E5;$B$5:$B$10;$A$5:$A$10;"Не найдено")
Здесь Excel ищет артикул PR-104 в столбце B и возвращает название из столбца A. Для ВПР такая задача неудобна, потому что нужный результат находится слева от ключевого столбца.
Точный и приблизительный поиск в ВПР
Четвертый аргумент ВПР часто становится причиной ошибок. Если вам нужно найти строго тот же артикул, код или номер, используйте ЛОЖЬ или 0.
=ВПР(F5;$A$5:$D$12;3;ЛОЖЬ)
Так Excel вернет результат только при точном совпадении. В этом примере формула ищет артикул из F5 в справочнике A5:D12 и возвращает цену из третьего столбца диапазона.
Если указать ИСТИНА или оставить четвертый аргумент пустым, Excel будет использовать приблизительный поиск. Такой вариант подходит не для артикулов и идентификаторов, а для интервальных задач: шкалы скидок, налоговых ставок, тарифных диапазонов, оценок по баллам. При приблизительном поиске первый столбец диапазона должен быть отсортирован по возрастанию, иначе результат может быть неверным.
Для обычного поиска товаров, клиентов, заказов и других точных идентификаторов почти всегда безопаснее использовать ЛОЖЬ.
Типичные ошибки при использовании ВПР
Ошибка #Н/Д означает, что Excel не нашел искомое значение. Причины могут быть разными: значения действительно нет в справочнике, в одной таблице число записано как текст, в ячейке есть лишние пробелы или выбран неправильный диапазон.
Ошибка #ССЫЛКА! часто появляется, когда номер столбца в формуле больше, чем количество столбцов в выбранном диапазоне. Например, если диапазон состоит из четырех столбцов, а в формуле указан номер 5, Excel не сможет вернуть результат.
Ошибка #ИМЯ? или #NAME? появляется, когда Excel не распознает имя функции. В случае с ПРОСМОТРX это может случиться, если функция недоступна в вашей версии Excel или если в названии функции вместо латинской X случайно введена русская Х.
Чтобы сделать формулу аккуратнее для пользователя, можно использовать функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ВПР(F5;$A$5:$D$12;2;ЛОЖЬ);"Не найдено")
Вместо ошибки в ячейке появится понятное сообщение «Не найдено». Это удобно для отчетов, которые смотрят другие сотрудники.
Что такое ПРОСМОТРX в Excel
ПРОСМОТРX — современная функция поиска, которая во многих задачах заменяет ВПР, ГПР и связку ИНДЕКС плюс ПОИСКПОЗ. В английской версии Excel она называется XLOOKUP. Функция доступна в современных версиях Excel, включая Microsoft 365 и Excel 2021. В старых бессрочных версиях Excel она может отсутствовать, поэтому при обмене файлами с коллегами это стоит учитывать.
Главное преимущество ПРОСМОТРX в том, что она отдельно принимает диапазон поиска и диапазон результата. Благодаря этому можно искать значение в любом столбце и возвращать данные как справа, так и слева. Также точное совпадение используется по умолчанию, что снижает риск случайной ошибки.
Синтаксис функции:
=ПРОСМОТРX(искомое_значение; просматриваемый_массив; возвращаемый_массив; если_не_найдено; режим_сопоставления; режим_поиска)
На практике чаще всего достаточно первых трех или четырех аргументов:
=ПРОСМОТРX(E5;$A$5:$A$11;$B$5:$B$11;"Не найдено")
Эта формула ищет значение из E5 в диапазоне A5:A11 и возвращает соответствующее значение из диапазона B5:B11. В демонстрационной таблице это означает: найти артикул и вернуть название товара.
Простой пример ПРОСМОТРX
Допустим, в справочнике столбец A содержит артикулы, столбец B — названия товаров, столбец C — цены. В отдельном блоке проверки в столбце E указаны артикулы, по которым нужно получить данные.
Чтобы вернуть название товара, используйте формулу:
=ПРОСМОТРX(E5;$A$5:$A$11;$B$5:$B$11;"Не найдено")
Чтобы вернуть цену:
=ПРОСМОТРX(E5;$A$5:$A$11;$C$5:$C$11;"Не найдено")
Формула читается почти как обычная фраза: найти значение E5 в списке артикулов A5:A11 и вернуть соответствующую цену из C5:C11. Это проще воспринимается, чем номер столбца в ВПР, особенно когда в справочнике много колонок.
На скриншоте с примером видно, что для артикула PR-106 функция возвращает товар «SSD 1 ТБ FastDrive» и цену 6490. Если указать артикул, которого нет в справочнике, например PR-999, формула выводит текст «Не найдено», а не стандартную ошибку.
Поиск слева с помощью ПРОСМОТРX
Одно из главных преимуществ ПРОСМОТРX — возможность искать данные слева от ключевого столбца. Например, если в столбце B находится артикул, а в столбце A — название товара, можно найти название по артикулу без перестановки столбцов.
Формула будет такой:
=ПРОСМОТРX(E5;$B$5:$B$10;$A$5:$A$10;"Не найдено")
Здесь Excel ищет значение из E5 в столбце B и возвращает результат из столбца A. Для ВПР такая задача неудобна, а для ПРОСМОТРX — стандартный сценарий.
Как вернуть сразу несколько столбцов
В современных версиях Excel ПРОСМОТРX может возвращать не только одну колонку, но и несколько соседних столбцов. Например, если нужно по артикулу вывести название, цену и остаток, можно указать возвращаемый диапазон из нескольких столбцов:
=ПРОСМОТРX(F5;$A$5:$A$12;$B$5:$D$12;"Не найдено")
Если Excel поддерживает динамические массивы, результат «разольется» по соседним ячейкам вправо. Это удобно для карточек товара, быстрых проверок и рабочих отчетов.
Важно: рядом с ячейкой формулы должно быть свободное место. Если соседние ячейки заняты, Excel не сможет вывести весь результат и покажет ошибку разлива.
Режимы сопоставления в ПРОСМОТРX
Пятый аргумент функции ПРОСМОТРX отвечает за режим сопоставления. В большинстве случаев его можно не указывать, потому что по умолчанию используется точное совпадение.
Основные варианты:
— 0 — точное совпадение. Это стандартный и самый безопасный режим.
— -1 — точное совпадение или ближайшее меньшее значение.
— 1 — точное совпадение или ближайшее большее значение.
— 2 — поиск с подстановочными знаками.
Пример точного совпадения:
=ПРОСМОТРX(E5;$A$5:$A$11;$C$5:$C$11;"Не найдено";0)
Пример поиска с подстановочными знаками:
=ПРОСМОТРX("*"&E5&"*";$A$5:$A$11;$C$5:$C$11;"Не найдено";2)
Такой вариант может пригодиться, если нужно найти строку, содержащую часть текста. Но использовать его стоит аккуратно: если совпадений несколько, Excel вернет первое найденное значение.
Режимы поиска: сверху вниз и снизу вверх
Шестой аргумент ПРОСМОТРX задает направление поиска. По умолчанию функция ищет сверху вниз. Но иногда нужно найти последнее совпадение, например последний статус заказа, последнюю дату операции или последнюю цену в истории изменений.
В примере с историей заказов номер заказа находится в F5, список заказов — в B5:B13, а статусы — в C5:C13. Чтобы найти последний статус по заказу, используется режим поиска -1:
=ПРОСМОТРX(F5;$B$5:$B$13;$C$5:$C$13;"Не найдено";0;-1)
Значение -1 в шестом аргументе означает поиск с конца диапазона к началу. Например, для заказа ORD-1007 формула вернет последний статус «Отправлен», а не первый статус «Создан».
Сравнение ВПР и ПРОСМОТРX
ВПР остается полезной функцией, особенно если файл должен открываться в старых версиях Excel. Она знакома многим пользователям, поддерживается давно и подходит для простых справочников, где ключевой столбец находится слева.
ПРОСМОТРX удобнее для новых файлов и более сложных таблиц. Она не требует считать номер столбца, умеет искать влево, возвращает понятное значение при отсутствии результата, поддерживает поиск с конца и может возвращать несколько столбцов.
Краткое сравнение:
— ВПР ищет только в первом столбце выбранного диапазона.
— ПРОСМОТРX ищет в любом указанном диапазоне.
— ВПР возвращает данные по номеру столбца.
— ПРОСМОТРX возвращает данные из явно указанного диапазона.
— ВПР требует внимательно указывать режим точного поиска.
— ПРОСМОТРX по умолчанию использует точное совпадение.
— ВПР лучше подходит для совместимости со старыми файлами.
— ПРОСМОТРX лучше подходит для новых отчетов и более надежных формул.
Как заменить ВПР на ПРОСМОТРX
Допустим, у вас есть формула:
=ВПР(F5;$A$5:$D$12;3;ЛОЖЬ)
Она ищет артикул из F5 в первом столбце диапазона A5:D12 и возвращает данные из третьего столбца этого диапазона, то есть цену из C5:C12.
Аналогичная формула через ПРОСМОТРX будет такой:
=ПРОСМОТРX(F5;$A$5:$A$12;$C$5:$C$12;"Не найдено")
Здесь уже не нужно считать столбцы. Видно, где искать и откуда возвращать результат. Если в таблицу позже добавят новый столбец, формулу ПРОСМОТРX обычно проще проверить и поддерживать.
Пошаговая инструкция: как использовать ВПР
1. Подготовьте справочник. Убедитесь, что столбец с искомыми значениями находится первым в выбранном диапазоне.
2. Перейдите в ячейку, где должен появиться результат.
3. Введите формулу ВПР.
4. В первом аргументе укажите ячейку с искомым значением.
5. Во втором аргументе выделите диапазон справочника и закрепите его с помощью знаков доллара, например $A$5:$D$12.
6. В третьем аргументе укажите номер столбца, из которого нужно вернуть результат.
7. В четвертом аргументе укажите ЛОЖЬ, если нужен точный поиск.
8. Нажмите Enter и протяните формулу вниз.
Пример:
=ВПР(F5;$A$5:$D$12;2;ЛОЖЬ)
Пошаговая инструкция: как использовать ПРОСМОТРX
1. Определите, какое значение нужно найти: артикул, код клиента, номер заказа или другой ключ.
2. Выберите диапазон, в котором Excel должен искать это значение.
3. Выберите диапазон, из которого нужно вернуть результат.
4. Перейдите в ячейку результата.
5. Введите формулу ПРОСМОТРX. Следите, чтобы последняя буква в названии функции была латинской X.
6. При необходимости добавьте текст для случая, когда значение не найдено.
7. Нажмите Enter и проверьте результат.
Пример:
=ПРОСМОТРX(E5;$A$5:$A$11;$B$5:$B$11;"Не найдено")
Такой подход проще поддерживать: в формуле явно видно, что поиск идет по столбцу A, а результат берется из столбца B.
Почему формула не находит значение
Если ВПР или ПРОСМОТРX возвращает «Не найдено» или ошибку #Н/Д, сначала проверьте не саму формулу, а данные. В Excel часто визуально одинаковые значения на самом деле отличаются.
Самые частые причины:
— В одной таблице число хранится как число, а в другой — как текст.
— В ячейках есть лишние пробелы.
— В артикуле используются разные символы, например латинская и русская буква, похожие внешне.
— В названии ПРОСМОТРX случайно введена русская «Х» вместо латинской X.
— В диапазоне поиска не включены все строки.
— Формула ссылается не на тот лист или не на тот столбец.
— ВПР используется без точного совпадения.
Для удаления лишних пробелов можно использовать функцию СЖПРОБЕЛЫ:
=СЖПРОБЕЛЫ(A5)
Если число записано как текст, иногда помогает преобразование формата или проверка данных через инструменты Excel. Перед массовым исправлением лучше проверить несколько строк вручную, чтобы не испортить исходный справочник.
Когда лучше использовать ВПР
ВПР стоит использовать, если файл должен работать в старых версиях Excel, где нет ПРОСМОТРX. Также она подходит для простых таблиц, в которых структура не меняется, а ключевой столбец всегда находится слева.
Еще одна причина оставить ВПР — корпоративная совместимость. В некоторых организациях много старых шаблонов, инструкций и отчетов построено именно на этой функции. Если все пользователи привыкли к ВПР и файл не требует сложного поиска, менять формулы только ради обновления не всегда обязательно.
Когда лучше использовать ПРОСМОТРX
ПРОСМОТРX лучше выбирать для новых отчетов и файлов, которые будут использоваться в современных версиях Excel. Функция особенно полезна, если таблица часто меняется, нужно искать данные слева, требуется возвращать несколько столбцов или важно сразу выводить понятное сообщение вместо ошибки.
Также ПРОСМОТРX удобнее для чтения формул. Когда другой пользователь открывает файл, ему проще понять конструкцию с отдельным диапазоном поиска и отдельным диапазоном результата, чем разбираться, почему в ВПР указан именно третий или седьмой столбец.
Практический пример: подтягиваем цену товара
Представим таблицу заказа. В столбце F указан артикул, в столбце G — количество, в столбце H нужно получить название товара, в столбце I — цену, а в столбце J — сумму по строке. Справочник находится в A5:D12: артикулы в A, названия в B, цены в C, остатки в D.
Формула для названия через ВПР:
=ВПР(F5;$A$5:$D$12;2;ЛОЖЬ)
Формула для цены через ПРОСМОТРX:
=ПРОСМОТРX(F5;$A$5:$A$12;$C$5:$C$12;"Не найдено")
Формула для суммы строки:
=G5*I5
Если цена не найдена, для итоговой таблицы лучше дополнительно обработать ошибку или оставить понятный текст «Не найдено», чтобы пользователь сразу понял, что артикула нет в справочнике.
Советы для надежных формул поиска
— Всегда используйте точное совпадение для артикулов, кодов, номеров заказов и идентификаторов.
— Закрепляйте диапазоны с помощью знаков доллара, если формулу нужно протягивать вниз.
— Следите, чтобы в ПРОСМОТРX была латинская X, а не русская Х.
— Не используйте целые столбцы в тяжелых файлах без необходимости, особенно если данных очень много.
— Проверяйте тип данных: число и текст в Excel могут выглядеть одинаково, но для поиска это разные значения.
— Добавляйте понятное сообщение для ненайденных значений, чтобы отчет было проще читать.
— Для новых файлов по возможности используйте ПРОСМОТРX, если нет требования совместимости со старыми версиями Excel.
ВПР или ПРОСМОТРХ?
ВПР — классическая функция Excel для вертикального поиска. Она проста, хорошо известна и подходит для множества задач, но имеет ограничения: ищет только в первом столбце диапазона, требует вручную указывать номер столбца и может дать неожиданный результат при неправильном режиме поиска.
ПРОСМОТРX — более современный и гибкий инструмент. Она ищет в любом направлении, по умолчанию использует точное совпадение, умеет возвращать данные слева, поддерживает поиск с конца и делает формулы легче для чтения. Главное — помнить, что в названии функции используется латинская X.
Оптимальная стратегия такая: понимать ВПР, потому что она часто встречается в старых отчетах, и постепенно переходить на ПРОСМОТРX в новых таблицах. Это позволит быстрее собирать отчеты, реже ошибаться в формулах и спокойнее работать с большими справочниками.
