ВПР с несколькими условиями

Примеры использования

Первый простой пример – имеются 2 таблицы. В одной указываются Товары и их идентификаторы (ID). Во второй, с помощью фильтра по ID, мы хотим получить наименование товара.

После знака равно вводим ВПР, затем Enter и Fx для ввода аргументов.

Аргументы также можно вводить в соответствующей строке, перечисляя их через точку с запятой.

Искомыми будут значения, по которым будем искать совпадения. В данном случае это ячейка E1.

Для второго аргумента выделяем диапазон таблицы.

Обязательно необходимо зафиксировать (указать знаки доллара или клавиша F4 для всего диапазона) и сделать абсолютные ссылки для того чтобы диапазон не «сползал», так как в данном случае не указывается имя таблица.

Номер столбца – то, что нужно вернуть. В этом примере требуется вернуть 2 столбец (Товар). Для точного поиска 4 аргумент – .

Введя все значения, жмём кнопку ОК.

Теперь при изменении в фильтре номера ID будет изменяться наименование товара.

Теперь посмотрим другой пример.

Теперь нужно получить партию для каждого наименования товара по критерию Количество.

Например, для мелкой партии количество должно быть от 100 до 200, средней – 200-300 и т.д.

Искомым значением в данном случае будет количество, Таблицу выбираем диапазон Критерий – Партия (фиксируем F4). Номер столбца 2, интервальный просмотр в этом случае должен быть 1 (позволит получить ближайшее меньшее значение к искомому значению).

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

Итак, мы с вами рассмотрим четыре варианта создания подстановочной функции с двумя условиями:

1. Использование функции СУММПРОИЗВ

Ну а начнем мы с вами с самого простого.

Использование дополнительной колонки

В большинстве случаев сложные проблемы становятся проще и более управляемыми, если их разбить на маленькие кусочки. Тоже самое касается при построении формул в Excel.

Рассмотрим классический пример. У нас имеется таблица с продажами по месяцам и городам. И нам необходимо определить значение продаж, соответствующее двум условиям: месяц – Февраль и город – Самара.

Использование функции ВПР в классическом виде нам не поможет, так как она сможет вернуть значение, соответствующее только одному условию. Из положения нам поможет выйти дополнительный столбец, в котором мы объединим значения столбцов Месяц и Город. Для этого в ячейке А2 прописываем формулу =B2&C2 и протягиваем данную формулу до ячейки А13. Теперь мы сможем использовать значения столбца А, чтобы вернуть необходимое значение. Прописываем в ячейке G3 формулу:

ВПР(G1&G2;A2:D13;4;0)

Данная формула объединяет два условия ячеек G1 и G2 в одну строку и просматривает его в столбце А. После того, как нужное условие было обнаружено, формула возвращает значение с четвертого столбца таблицы A1:D13, т.е. столбца Продажи.

Использование функции ВЫБОР для создания новой таблицы просмотра

В случае если по каким-либо причинам использование дополнительного столбца для нас является не вариантом, мы можем использовать .

Использование функции ВЫБОР подразумевает создание новой таблицы для просмотра, в котором значения столбцов Месяц и Город уже объединены. Наша формула будет выглядеть следующим образом:

ВПР(G1&G2;ВЫБОР({1;2};B2:B13&C2:C13;D2:D13);2;0)

Основной момент данной формулы заключается в части ВЫБОР({1;2};B2:B13&C2:C13;D2:D13), который делает две вещи:

1. Объединяет значения столбцов Месяц и Город в один массив: ЯнвМосква, ФевМосква …
2. Объединяет два массива в таблицу, состоящую из двух столбцов.

Результатом работы данной функции будет таблица, которая выглядит следующим образом:

Теперь формула стала более понятной.

ВАЖНО: Так как мы использовали формулу массива, по окончании ввода формулы нажмите Ctrl+Shift+Enter, чтобы дать знать программе о наших намерениях. После нажатия данной комбинации клавиш, программа автоматически установит фигурные скобки в начале и в конце формулы.

Использование функций ИНДЕКС и ПОИСКПОЗ

Третий способ, который мы с вами рассмотрим, также предполагает использование формулы массива и задействует функции ИНДЕКС и ПОИСКПОЗ.

Формула будет выглядеть следующим образом.

ИНДЕКС(D2:D13;ПОИСКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0))

Давайте разберем, что делает каждая часть данной формулы.

Сначала рассмотрим функцию ПОИСКПОЗ(1;(B2:B13=G1)*(C2:C13=G2);0). В данном случае последовательно сравнивается значение ячейки G1 с каждым значением ячеек диапазона B2:B13 и возвращается ИСТИНА, если значения совпадают и ЛОЖЬ, если нет. Такое же сравнение производится со значением ячейки G2 и диапазоном C2:C13. Далее мы сравниваем оба эти массива, состоящих из ИСТИНА и ЛОЖЬ. Комбинация ИСТИНА * ИСТИНА дает нам результат 1 (ИСТИНА). Давайте посмотрим на картинку ниже, которая поможет объяснить принцип работы более наглядно.

Теперь мы можем сказать, где находится строка, удовлетворяющая обоим условиям. Функция ПОИСКПОЗ отыскивает положение 1 в результирующем массиве и возвращает 6, так как единица встречается в шестой строке. Далее функция ИНДЕКС возвращает значение шестой строки диапазона D2:D13.

Использование СУММПРОИЗВ

Одна из самых мощных формул Excel. У меня даже есть отдельная статья, посвященная данной формуле. Наш четвертый способ использовании нескольких условий заключается в написании формулы с функцией СУММПРОИЗВ. И выглядеть она будет следующим образом:

СУММПРОИЗВ((B2:B13=G1)*(C2:C13=G2);D2:D13)

Принцип работы данной формулы схож с принципом работы предыдущего подхода. Создается виртуальная таблица, в которой сравниваются значения ячеек G1 и G2 с диапазонами B2:B13 и С2:С13 соответственно. Далее оба этих массива сопоставляются и получается массив из единиц и нулей, где единица присваивается той строке, в которой оба условия совпали. Далее данный виртуальный массив перемножается на диапазон D2:D13. Так как в нашем виртуальном массиве будет только одна единица в шестой строке, формула вернёт результат 189.

Данная функция не будет работать, если в диапазоне D2:D13 имеются текстовые значения.

Чтобы понять, как работает данная формула, рекомендую прочитать статью о функции СУММПРОИЗ.

ИТОГ

Итак, какой же способ использовать? Хотя все они работают стабильно, я предпочитаю первый способ. В своей ежедневной работе, я предпочитаю работать с файлами, которые просты для понимания и поддаются изменениям. Оба эти требования отвечают условиям первого подхода.

Для более лучшего понимания формул, вы можете , разобранными в сегодняшней статье.

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

Название функции ВПР расшифровывается, как «функция вертикального просмотра». По-английски её наименование звучит – VLOOKUP. Эта функция ищет данные в левом столбце изучаемого диапазона, а затем возвращает полученное значение в указанную ячейку. Попросту говоря, ВПР позволяет переставлять значения из ячейки одной таблицы, в другую таблицу. Выясним, как пользоваться функцией VLOOKUP в Excel.

Пример использования ВПР

Взглянем, как работает функция ВПР на конкретном примере.

У нас имеется две таблицы. Первая из них представляет собой таблицу закупок, в которой размещены наименования продуктов питания. В следующей колонке после наименования расположено значение количества товара, который требуется закупить. Далее следует цена. И в последней колонке – общая стоимость закупки конкретного наименования товара, которая рассчитывается по вбитой уже в ячейку формуле умножения количества на цену. А вот цену нам как раз и придется подтянуть с помощью функции ВПР из соседней таблицы, которая представляет собой прайс-лист.

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

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

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

Функция ВПР (Вертикальный ПРосмотр) ищет по таблице с данными и на основе критериев запроса поиска, возвращает соответствующее значение с определенного столбца. Очень часто необходимо в запросе поиска использовать сразу несколько условий. Но по умолчанию данная функция не может обработать более одного условия. Поэтому следует использовать весьма простую формулу, которая позволит расширить возможности функции ВПР по нескольким столбцам одновременно.

Работа функции ВПР по нескольким критериям

Для наглядности разберем формулу ВПР с примером нескольких условий. Для примера будем использовать схематический отчет по выручке торговых представителей за квартал:

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

1. – Дата сдачи выручки в кассу.
2. – Фамилия торгового представителя.

Для решения данной задачи будем использовать функцию ВПР по нескольким условиям и составим следующую формулу:

1. В ячейке С1 введите первое значение для первого критерия поискового запроса. Например, дата: 22.03.2017.
2. В ячейку C2 введите фамилию торгового представителя (например, Новиков). Это значение будет использоваться в качестве второго аргумента поискового запроса.
3. В ячейке C3 мы будем получать результат поиска, для этого там следует ввести формулу:
4. После ввода формулы для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве.

Результат поиска в таблице по двум условиям:

Найдена сумма выручки конкретного торгового представителя на конкретную дату.



Разбор принципа действия формулы для функции ВПР с несколькими условиями:

Первым аргументом функции =ВПР() является первым условием для поиска значения по таблице отчета выручки торговых представителей. Во втором аргументе находится виртуальная таблица создана в результате массивного вычисления логической функцией =ЕСЛИ(). Каждая фамилия в диапазоне ячеек B6:B12 сравнивается со значением в ячейке C2. Таким образом в памяти создается условный массив данных с элементами значений ИСТИНА и ЛОЖЬ.

Потом благодаря формуле, в памяти программы каждый истинный элемент заменяется на 3-х элементный набор данных:

1. элемент – Дата.
2. элемент – Фамилия.
3. элемент – Выручка.

А каждый ложный элемент в памяти заменяется на 3-х элементный набор пустых текстовых значений («»). В результате создается в памяти программы новая таблица, с которой уже будет работать функция ВПР. Она игнорирует все пустые наборы данных элементов. А непустые элементы сопоставляются со значением ячейки C1, использованного в качестве первого критерия поискового запроса (Дата). Одним словом, таблица в памяти проверена функцией ВПР с одним условием поиска. При положительном результате сопоставления функция возвращает значение элемента из третьего столбца (выручка) условной таблицы. Это происходит потому, что в третьем аргументе указывается номер столбца 3 из которого берутся значения. Стоит отметить что для просмотра в аргументах функции указывается целая таблица (во втором аргументе), но сам поиск всегда идет по первому столбцу в указанной таблицы.

А из какого столбца брать возвращаемое значение указывается уже в третьем аргументе.

Число 0 в последнем аргументе функции указывает на то, то совпадение должно быть абсолютно точным.

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

Использование функции СТОЛБЕЦ для указания колонки извлечения

Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов. При этом все ВПР-формулы будут одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого параметра координата столбца абсолютная.

Создание составного ключа через &»|»&

Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем «Код»), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.

Это моя первая статья для Лайфхакера. Если вам понравилось, то приглашаю вас посетить мой сайт , а также с удовольствием прочту в комментариях о ваших секретах использования функции ВПР и ей подобных. Спасибо. 🙂

Необходимое предисловие

Если вы раньше не работали с функцией ВПР (VLOOKUP) , то много потеряли очень рекомендую сначала почитать .

Проблема

Как многим известно, функция ВПР (VLOOKUP) может выдавать в качестве результата значения, которые находятся строго правее того столбца, где производится поиск. Ах, как было бы красиво, если бы третий аргумент этой функции (номер столбца, откуда выдаются значения) можно было бы задавать отрицательным, но — нет.

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

Стоимость по коду заказа найти легко — обычный ВПР тут поможет на раз-два. А вот как найти название товара по коду? На тренингах этот вопрос я чаще всего слышу в формулировке «а как сделать левый ВПР»?

Давайте разберем несколько способов.

Способ 1. Лобовая атака

Если следовать принципу Оккама и не усложнять без надобности, то можно просто скопировать нужный столбец правее (или сделать его ссылками) и использовать обычный ВПР :

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

Способ 2. Виртуальная перестановка столбцов функцией ВЫБОР

Если переставить местами столбцы на листе нельзя, то это можно сделать виртуально, т.е. «на лету» прямо в самой формуле. Для этого нам потребуется функция ВЫБОР (CHOOSE) . Основное ее предназначение – выбирать нужный элемент из списка по заданному номеру. Ее, например, можно использовать для замены номера дня недели на его текстовый аналог:

Ничего сверхъестественного, на первый взгляд, но тут есть пара хитрых моментов.

Во-первых , вместо текстовых названий выбираемых элементов списка («пн», «вт» и т.д.) можно использовать адреса диапазонов. И тогда функция вернет ссылку на выбранный диапазон. Так, например, формула:

ВЫБОР(2; A1:A10 ; D1:D10 ; B1:B10 )

Во-вторых , вместо простого одиночного номера извлекаемого элемента в первом аргументе функции ВЫБОР можно задать массив констант в фигурных скобках, например, так:

ВЫБОР({1;2} ; A1:A10 ; D1:D10 ; B1:B10 )

Тогда на выходе мы получим два первых диапазона (A1:A10 и D1:D10), склеенных в единое целое.

И вот теперь все это можно вложить внутрь нашей ВПР , чтобы реализовать «левый поиск»:

От «классического ВПР» отличается, как видно, только тем, что диапазон задается склейкой двух столбцов Код заказа и Товар с помощью функции ВЫБОР . В остальном все привычно.

Минусы такого способа — это скорость (примерно в 5-7 раз медленнее обычного ВПР) и некоторая непривычность для коллег (а может это даже плюс!)

Способ 3. Связка функций ИНДЕКС и ПОИСКПОЗ

Если не держаться имеено за функцию ВПР, то можно использовать ее более мощный аналог — связку двух очень полезных функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) :

Функция ПОИСКПОЗ ищет заданное значение (С2, т.е. код нужного нам заказа) в одномерном диапазоне (столбце кодов в таблице C10:C25) и выдает в качестве результата порядковый номер ячейки, где нашла искомое — в нашем случае это будет число 4, т.к. код нужного нам заказа четвертый в таблице.

А затем в дело вступает функция ИНДЕКС , которая умеет извлекать данные из вертикального массива-столбца (названия товаров в B10:B25) по порядковому номеру (который предварительно нашла ПОИСКПОЗ ). Таким образом, ИНДЕКС выдаст нам содержимое четвертой ячейки из столбца Товар , что и требовалось.

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

Похожий пример (с видео) я разбирал ранее . А уж про функцию ИНДЕКС можно говорить 🙂

Способ 4. Функция СУММЕСЛИ(МН)

Если нужно извлечь из таблицы именно число (допустим, объем в литрах), то иногда проще использовать для реализации «левого ВПР» функцию выборочного суммирования СУММЕСЛИ (SUMIF) или ее старшую сестру — функцию СУММЕСЛИМН (SUMIFS) :

Минусы такого подхода очевидны — он работает только для чисел и, при условии, что в столбце нет повторяющихся значений. Если есть дубликаты (несколько заказов с одинаковым кодом), то эта функция сложит все объемы, а не выдаст первый, как это сделала бы ВПР. Ну, и скорость у такого способа тоже не очень — примерно в 3-4 раза медленнее, чем обычный ВПР.

Большая статья про функции выборочного подсчета по одному или нескольким условиям .

Способ 5. Готовая макрофункция из PLEX

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

• искать по нескольким столбцам сразу (до 3)
• выдавать результаты из любого столбца (левее или правее — не важно)
• выдавать не только первое встретившееся значение, а нужное по порядку
• можно задать, что вывести, если ничего не найдено вместо ошибки #Н/Д

У такого способа два минуса: нужно сохранять файл с поддержкой макросов (XLSM) и скорость у любой макрофункции не очень высокая — на больших таблицах может ощутимо подтормаживать.

Но как один из вариантов — пойдет:)

Ссылки по теме

Отличный способ. Спасибо.
Научите: Как можно с помощью индекс и поискпозиции выбирать значения из первого столбца, когда ищешь во втором столбце, но при этом нужно во втором столбце найти значения с максимальной датой в четвертом?
На вскидку приходит функция наибольший, но куда ее вставить….)))вот такая таблица

маршрут	ид	префикс	платеж
запад2	251458	БР7652И	16.01.2017
Кашира1	252412	БР7652И	17.01.2017
С5-1	253016	БР7272И	18.01.2017
С3-1	254392	БР7652И	19.01.2017
Кашира1	255031	БР7249И	20.01.2017
Кашира1	257321	БР7759И	21.01.2017
СВ1-1	257569	БР7761И	22.01.2017
Пушкино1	259373	БР7647И	23.01.2017
Коломна1	259591	БР7315И	24.01.2017
Кашира1	260300	БР7544И	25.01.2017

вот так я ищу

префикс	ид
БР7652И	ИНДЕКС(A2:D11;ПОИСКПОЗ(D20;C2:C11;0);ПОИСКПОЗ(E19;A1:D1;0))

но мне нужен результат с максимальной датой платежа а не первый по порядку

Синтаксис и описание функции ВПР в Excel

Итак, так как второе название этой статьи «Функция ВПР в Excel для чайников», начнем с того что узнаем, что же такое функция ВПР и что она делает? Функция ВПР на английском VLOOKUP, ищет указанное значение и возвращает соответствующее значение из другого столбца.

Как работает функция ВПР? Функция ВПР в Excel выполняет поиск по вашим спискам данных на основе уникального идентификатора и предоставляет вам часть информации, связанную с этим уникальным идентификатором.

Буква «В» в ВПР означает «вертикальный». Она используется для дифференциации функции ВПР и ГПР, которая ищет значение в верхней строке массива («Г» обозначает «горизонтальный»).

Функция ВПР доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

Синтаксис функции ВПР выглядит следующим образом:

ВПР(искомое_значение;таблица;номер_столбца;)

Как видите, функция ВПР имеет 4 параметра или аргумента. Первые три параметра обязательные, последний — необязательный.

1. искомое_значение – это значение для поиска.

Это может быть либо значение (число, дата или текст), либо ссылка на ячейку (ссылка на ячейку, содержащую значение поиска), или значение, возвращаемое некоторой другой функцией Excel. Например:

• Поиск числа: =ВПР(40; A2:B15; 2) — формула будет искать число 40.
• Поиск текста: =ВПР(«яблоки»; A2:B15; 2) — формула будет искать текст «яблоки». Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
• Поиск значения из другой ячейки: =ВПР(C2; A2:B15; 2) — формула будет искать значение в ячейке C2.

1. таблица — это два или более столбца данных.

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

Итак, наша формула =ВПР(40; A2:B15; 2) будет искать «40» в ячейках от A2 до A15, потому что A — это первый столбец таблицы A2: B15.

1. номер_столбца — номер столбца в таблице, из которой должно быть возвращено значение в соответствующей строке.

Самый левый столбец в указанной таблице равен 1, второй столбец — 2, третий — 3 и т. д.

Итак, теперь вы можете прочитать всю формулу =ВПР(40; A2:B15; 2). Формула ищет «40» в ячейках от A2 до A15 и возвращает соответствующее значение из столбца B (потому что B является вторым столбцом в указанной таблице A2:B15).

4. интервальный_просмотр определяет, ищете ли вы точное соответствие (ЛОЖЬ) или приблизительное соответствие (ИСТИНА или опущено). Этот последний параметр является необязательным, но очень важным.

Функция ВПР в Excel примеры

Теперь давайте рассмотрим несколько примеров использования функции ВПР для реальных данных.

Функция ВПР на разных листах

На практике формулы ВПР редко используются для поиска данных на одном листе. Чаще всего вам придется искать и вытаскивать соответствующие данные с другого листа.

Чтобы использовать функцию ВПР с другого листа Excel, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(40;Лист2!A2:B15;2). Формула указывает, что диапазон поиска A2:B15 находится в Лист2.

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

Формула, которую вы видите на изображении ниже, ищет текст в ячейке А2 («Продукт 3») в столбце A (1-й столбец диапазона поиска A2:B9) на листе «Цены»:

=ВПР(A2;Цены!$A$2:$B$8;2;ЛОЖЬ)

Функция ВПР в Excel – Функция ВПР на разных листах

Обратите внимание, что рекомендуется использовать на ячейки (со знаком $) в аргументе таблица. Это предотвратит изменение диапазона поиска при копировании формулы ВПР в другие ячейки.

Как использовать именованный диапазон или таблицу в формулах ВПР

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

Чтобы создать именованный диапазон, просто выберите ячейки и введите любое имя в поле «Имя», слева от панели «Формула».

Функция ВПР в Excel – Присвоение имени диапазону

Теперь вы можете написать следующую формулу ВПР, чтобы получить цену Продукта 1:

=ВПР(«Продукт 1»;Продукты;2)

Функция ВПР в Excel – Пример функции ВПР с именем диапазона

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

Если вы преобразовали диапазон ячеек в полнофункциональную таблицу Excel (вкладка «Вставка» —> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, а Microsoft Excel автоматически добавит имена колонок или имя таблицы в формулу:

Функция ВПР в Excel – Пример функции ВПР с именем таблицы

Полная формула может выглядеть примерно так:

=ВПР(«Продукт 1»;Таблица6:];2)

или даже =ВПР(«Продукт 1»;Таблица6;2).

Как и именованные диапазоны, имена столбцов являются постоянными, а ссылки на ячейки не изменятся независимо от того, где копируется формула ВПР.

Функция ВПР с несколькими условиями

Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:

Функция ВПР в Excel – Таблица исходных данных

Пусть нам необходимо использовать функцию ВПР с несколькими условиями. Например, для поиска цены товара по двумя критериями: названию продукта и его типу.

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

Итак на листе «Цены» вставляем столбец и в ячейке А2 вводим следующую формулу:

=B2&C2

При помощи этой формулы мы сцепляем значение столбца «Продукт» и «Тип». Заполняем все ячейки.

Теперь таблица для поиска выглядит следующим образом:

Функция ВПР в Excel – Добавление вспомогательного столбца

1. Теперь в ячейке С2 на листе «Продажи» напишем следующую формулу ВПР:

=ВПР(A2&B2;Цены!$A$1:$D$8;4;ЛОЖЬ)

Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:

Функция ВПР в Excel – Пример ВПР с несколькими условиями

Теперь разберем ошибки функции ВПР.

Почему не работает функция ВПР

В этой части статьи мы рассмотрим почему не работает функция ВПР и возможные ошибки функции ВПР.

Тип ошибки	Причина	Решение
#Н/Д	Неверное расположение столбца, по которому происходит поиск	Столбец таблицы, по которому происходит поиск ОБЯЗАТЕЛЬНО должен быть крайним левым. Перенесите столбец, по которому происходит поиск в крайнее левое положение таблицы. Или создайте вспомогательный дублирующий столбец, слева в таблице.
#Н/Д	Не закреплен диапазон таблицы	Если первое значение было выведено правильно, а после протягивания формулы ВПР в некоторых ячейках встречается ошибка #Н/Д, то диапазон таблицы не закреплен. Используйте ($) для закрепления диапазона таблицы, чтобы при заполнении формула использовала один и тот же диапазон. Или используйте именованный диапазон
#Н/Д	Не удалось найти точное совпадение (если в интервальном просмотре выбран поиск точного значения (0)	–
#Н/Д	В интервальном просмотре выполняется поиск ближайшего значения (1), а таблица, по которой происходит поиск не отсортирована.	Отсортируйте первый столбец таблицы по возрастанию наименований.
#Н/Д	Данные содержат лишние пробелы, недопустимые кавычки или непечатаемые символы.	Используйте функции ПЕЧСИМВ или СЖПРОБЕЛЫ.
#ССЫЛКА!	Значение номер столбца превышает число столбцов в таблице	Проверьте номер столбца, содержащий возвращаемое значение.
#ИМЯ?	В формуле пропущены кавычки	Если вы используете в качестве искомого значения не ссылку на ячейку, а текст, то его необходимо заключить в кавычки. Например: =ВПР(«Продукт 1»; Цены!$A$2:$B$8;2;0)

Надеюсь, что теперь даже для чайников функция ВПР в Excel будет понятна.

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

1. Таблицы должны располагаться в одной книге Excel.
2. Искать можно только среди статических данных (не формул).
3. Условие поиска должно располагаться в первом столбце используемых данных.

Блок: 1/5 | Кол-во символов: 404

Как пользоваться функцией

Для того чтобы понять, как работает этот инструмент, необходимо выполнить следующие шаги.

1. Создайте таблицу, по которой можно будет сделать какой-нибудь поиск информации.

1. Добавим несколько полей, которые будем использовать для демонстрации формул.

1. В поле «Искомая фамилия» введем какую-нибудь на выбор из тех, что есть в таблице.
2. Затем переходим на следующую ячейку и вызываем окно «Вставка функции».
3. Выбираем категорию «Полный алфавитный перечень».
4. Находим нужную нам функцию «ВПР». Для продолжения нажимаем на кнопку «OK».

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

1. В результате этого мы получили имя «Томара». То есть, всё правильно.

Теперь нужно воспользоваться этой же формулой и для остальных полей. Простое копирование ячейки при помощи Ctrl+C и Ctrl+V не подойдёт, поскольку у нас используются относительные ссылки и каждый раз будет меняться номер столбца.

Для того чтобы всё сработало правильно, нужно сделать следующее:

1. Кликните на ячейку с первой функцией.
2. Перейдите в строку ввода формул.
3. Скопируйте текст при помощи Ctrl+C.

1. Сделайте активной следующее поле.
2. Снова перейдите в строку ввода формул.
3. Нажмите на горячие клавиши Ctrl+V.

Только таким способом редактор не изменит ссылки в аргументах функции.

1. Затем меняем номер столбца на нужный. В нашем случае это 3. Нажимаем на клавишу Enter.

1. Благодаря этому мы видим, что данные из столбца «Год рождения» определились правильно.

1. После этого повторяем те же самые действия для последнего поля, но с корректировкой номера нужного столбца.

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

То есть нумерация начинается не с начала листа, а с начала указанной области ячеек.

Сравнение данных двух таблиц Excel

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

На двух листах мы имеем одинаковые таблицы с разными данными.

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

В ячейку B2 введем функцию ВПР. В качестве первого аргумента укажем ячейку с месяцем на текущем листе, а диапазон выберем с листа «Цех1». Чтобы при копировании диапазон не смещался, нажмите F4 после выбора диапазона. Это сделает ссылку абсолютной.

Растяните формулу на весь столбец.

Аналогично введите формулу в следующий столбец, только диапазон выделяйте на листе «Цех2».

После копирования Вы получите сводный отчет с двух листов.

>Синтаксис

=VLOOKUP(lookup_value, table_array, col_index_num, ) – английская версия

=ВПР(искомое_значение, таблица, номер_столбца, ) – русская версия

Примеры использования ВПР в Excel

Пример 1. Ищем результат экзамена для студента

На примере ниже, в левой колонке таблицы указан список студентов. В правой части таблицы указаны итоговые баллы по экзаменам этих студентов.

Перед нами стоит задача узнать, какой балл получил студент Петров по Физике.

Узнать это мы можем с помощью формулы ниже:

=VLOOKUP(“Петров”,$A$3:$E$10,3,0) – английская версия

=ВПР(“Петров”;$A$3:$E$10;3;0) – русская версия

Формула выше состоит из четырех аргументов:

• “Петров” – фамилия, данные по которой, ищем;
• $A$3:$E$10 – диапазон данных с результатами экзаменов;
• “3” – порядковый номер столбца диапазона данных со значением оценки по Физике;
• “0” – точное совпадение искомого значения.

Ниже, наглядно изображено как работает по примеру наша функция.

Сначала, она ищет в крайней левой колонке диапазона данных фамилию “Петров”. Поиск происходит сверху вниз.

Как только Excel нашел точное совпадение “Петров” в диапазоне данных, система “шагает” в право, на третью колонку для отображения искомых данных.

Используя этот подход вы можете получить любые данные по каждому студенту и учебному предмету.

Например, чтобы найти результаты экзамена для студента Пескова по Химии, нам нужна формула:

=VLOOKUP(“Песков”,$A$20:$E$28,4,0) – английская версия

=ВПР(“Песков”;$A$20:$E$28;4;0) – русская версия

В приведенном выше примере фамилия студента указана в двойных кавычках. Также, можно использовать ссылку на ячейку с искомым значением. Ссылка на ячейку делает формулу динамической.

Например, если у вас есть ячейка с фамилией, и вы ищете оценку по математике, результат будет автоматически обновляться при изменении имени учащегося (как показано ниже):

Если в качестве аргумента поиска будет введена фамилия студента, которой нет в диапазоне данных, функция выдаст ошибку.

Пример 2. Двухфакторный поиск данных

На примере выше мы указывали вручную в качестве атрибута “номер_столбца” порядковый номер колонки с данными.

Но что, если мы хотим сделать поиск динамическим? Что если, мы хотим вводить имя студента и название предмета и функция автоматически выведет нужные данные? Для этого нам потребуется использовать двух факторный поиск.

Ниже пример двух факторного поиска:

Чтобы сделать двух факторную формулу поиска, важно сделать выбор данных из колонок динамическим. Так, когда пользователь меняет название предмета: Физика, Математика и т.д., в формуле изменяется соответствующий атрибут столбца с данными.

Чтобы сделать это, нам потребуется функция MATCH (ПОИСКПОЗ) в качестве аргумента отвечающего за порядковый номер колонки.

Так будет выглядеть формула для двухфакторного поиска:

=VLOOKUP($G$5,$A$4:$E$12,MATCH($H$4,$A$3:$E$3,0),0) – английская версия

=ВПР($G$5;$A$4:$E$12;ПОИСКПОЗ($H$4;$A$3:$E$3;0);0) – русская версия

В формуле выше используется функция MATCH (ПОИСКПОЗ), которая отвечает за порядковый номер колонки с данными. MATCH (ПОИСКПОЗ) принимает название учебного предмета как значение поиска (в ячейке “H4”) и возвращает его позицию в диапазоне ячеек “A2:E2”.

Если указать “Математика”, Excel вернет “2”, поскольку “Математика” находится в ячейке “B2” (вторая по счету в этом массиве).

Пример 3. Используем выпадающий список при двух факторном поиске

Используя способ из примера №2, нам требуется делать много ручной работы. Высок риск допустить ошибку и потратить много времени, особенно, если вы работаете с большим объемом данных.

Здесь лучше использовать выпадающие списки как значения для поиска (в нашем примере это фамилии студентов и учебные предметы).

Основываясь на тех данных, что вы выберете из выпадающего списка, функция автоматически выведет необходимые данные, как это указано на примере ниже:

Такой подход позволяет создать дашборд, с помощью которого вы легко сможете обрабатывать большие списки с данными.

Ниже мы рассмотрим, как сделать такую таблицу.

Для этого нам потребуется формула из Примера №2:

=VLOOKUP($G$5,$A$4:$E$12,MATCH($H$4,$A$3:$E$3,0),0) – английская версия

=ВПР($G$5;$A$4:$E$12;ПОИСКПОЗ($H$4;$A$3:$E$3;0);0) – русская версия

Данные поиска были преобразованы в выпадающие списки.

Чтобы сделать выпадающий список, нужно:

• Выбрать ячейку, в которой вы хотите создать выпадающий список. В нашем примере в ячейке “G4” мы указали фамилии студентов.
• Перейти к вкладке Данные -> Проверка данных.
• В диалоговом окне «Проверка данных» на вкладке «Настройки» выбрать «Список» и в раскрывающемся списке выберите «Разрешить».
• В источнике выбрать $A$3:$A$10
• Нажать «ОК».

Теперь у вас появится раскрывающийся список в ячейке “G4”. Аналогично, вы можете создать его в “H3” для предметов.

Пример 4. Трех факторный поиск данных ВПР

Что такое трех факторный поиск?

В Примере 2 мы использовали одну таблицу с оценками для студентов по разным предметам. Это пример двухфакторного поиска, поскольку мы используем две переменные для получения оценки (фамилия студента и предмет).

Теперь предположим, что к концу года студент прошел три уровня экзаменов: «Вступительный», «Полугодовой» и «Финальный экзамен».

Трехсторонний поиск – это возможность получить отметки студента по предмету с определенным уровнем экзамена.

Вот пример трехстороннего поиска:

В приведенном выше примере, функция ищет данные в трех разных таблицах с данными («Вступительный», «Полугодовой» и «Финальный экзамен») и возвращает значения оценок студента по определенному предмету по конкретному уровню экзамена.

Для таких расчетов нам поможет формула:

=VLOOKUP(G5,CHOOSE(IF(H3=”Вступительный”,1,IF(H3=”Полугодовой”,2,3)),$A$4:$E$12,$A$16:$E$24,$A$28:$E$36),MATCH(H4,$A$3:$E$3,0),0) – английская версия

=ВПР(G5;ВЫБОР(ЕСЛИ(H3=”Вступительный”;1;ЕСЛИ(H3=”Полугодовой”;2;3));$A$4:$E$12;$A$16:$E$24;$A$28:$E$36);ПОИСКПОЗ(H4;$A$3:$E$3;0);0) – русская версия

Эта формула использует функцию CHOOSE (ВЫБОР), чтобы убедиться, что данные выбраны из правильной таблицы. Давайте проанализируем часть формулы CHOOSE (ВЫБОР):

CHOOSE(IF(H3=”Вступительный”,1,IF(H3=”Полугодовой”,2,3)),$A$4:$E$12,$A$16:$E$24,$A$28:$E$36),MATCH(H4,$A$3:$E$3,0),0) – английская версия

ВЫБОР(ЕСЛИ(H3=”Вступительный”;1;ЕСЛИ(H3=”Полугодовой”;2;3));$A$4:$E$12;$A$16:$E$24;$A$28:$E$36);ПОИСКПОЗ(H4;$A$3:$E$3;0);0) – русская версия

Первый аргумент формулы (IF(H3=”Вступительный”,1,IF(H3=”Полугодовой”,2,3) или (ЕСЛИ(H3=”Вступительный”;1;ЕСЛИ(H3=”Полугодовой”;2;3) проверяет ячейку “H3” и определяет, на какой уровень экзамена ссылаются. Если это “Вступительный”, функция возвращает данные из диапазона $A$4:$E$12, который содержит оценки для вступительного экзамена.

Если выбран уровень “Полугодовой”, то система возвращает данные из диапазона $A$16:$E$24, в другом случае она возвращает данные из диапазона $A$28:$E$36.

Такой подход делает массив таблиц динамическим и трех факторным.

Пример 5. Получаем последнее значение колонки с помощью функции VLOOKUP (ВПР)

С помощью ВПР вы можете вычислить последнее число из списка.

Наибольшее положительное число, которое вы можете использовать в Excel, равно 9.99999999999999E + 307. Это также означает, что самый большой номер поиска в номере VLOOKUP также 9.99999999999999E + 307.

Я не думаю, что вам когда-нибудь понадобится какой-то расчет, в котором участвует такое большое число. Но с его помощью мы можем получить последнее число в списке.

Предположим, у вас есть набор данных (в диапазоне ячеек A1:A14), как показано ниже, и вы хотите получить число из последней ячейки в списке.

Для этого нам подойдет следующая формула:

=VLOOKUP(9.99999999999999E+307,$A$1:$A$14,TRUE) – английская версия

=ВПР(9.99999999999999E+307;$A$1:$A$14;ИСТИНА)

Обратите внимание, что в формуле выше используется приблизительная точность совпадения данных (несмотря на это нет необходимости упорядочивать список).

Вот как работает функция с приблизительной точностью совпадения данных. Она просматривает левую колонку сверху вниз.

• Если он находит точное совпадение, она возвращает это значение;
• Если она находит число, превышающее значение поиска => возвращает число в ячейке над ним;
• Если значение поиска больше всех чисел в списке, функция возвращает последнее число из списка.

В нашем примере работает третий сценарий.

Число 9.99999999999999E + 307 это самое большое число, которое можно использовать в Excel, когда оно используется как критерий поиска, тогда функция VLOOKUP (ВПР) возвращает последнее число из списка.

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

=VLOOKUP(“яяя”,$A$1:$A$14,1,TRUE) – английская версия

=ВПР(“яяя”;$A$1:$A$14;1;ИСТИНА) – русская версия

Здесь действует та же логика. Система просматривает все названия из списка. Так как “яяя” самое большое текстовое значение => результатом вычисления будет самое крайнее значение из списка данных.

Пример 6. Частичный поиск с использованием символов подстановочных знаков и ВПР

Символы подстановки в Excel могут быть полезными во многих ситуациях.

Частичный поиск необходим, когда вам нужно искать значение в списке, в котором нет точного соответствия искомым данным.

Например, у вас есть набор данных, как показано ниже, и вы хотите найти компанию “ABC” в списке, но в списке есть только “ABC Ltd” вместо “ABC”.

Вы не можете использовать “ABC” в качестве значения поиска, так как в столбце “A” нет точного соответствия. Приблизительное совпадение часто приводит к ошибочным результатам и требует сортировки списка в порядке возрастания.

Однако, вы можете использовать подстановочный знак в VLOOKUP (ВПР) для более точного соответствия данных.

Введите следующую формулу в ячейку “D2” и перетащите ее в другие ячейки:

=VLOOKUP(“*”&C2&”*”,$A$2:$A$8,1,FALSE) – английская версия

=ВПР(“*”&C2&”*”;$A$2:$A$8;1;ЛОЖЬ) – русская версия

Как это работает?

В формуле, указанной выше мы добавили к значению поиска “звездочки” с двух сторон. Указывая такие “звездочки” вы даете Excel понять, что готовы осуществить поиск, при котором в искомых данных как в начале так и в конце значения могут быть любые другие слова, буквы или цифры.

Таким образом, осуществляя поиск, Excel будет понимать, что необходимо искать значение, которое содержит слово из ячейки “С2” (в нашем примере).

Например, в ячейке “C2” указано название компании “ABC”, поэтому функция просматривает значения в диапазоне данных “A2:A8” и ищет “ABC”. Она находит соответствие в ячейке “A2”, так как та содержит слово “ABC” в “ABC Ltd”. Не имеет значения, есть ли какие-либо символы слева или справа от “ABC”.

Примечание: ВПР всегда возвращает первое совпадающее значение и перестает искать дальше. Поэтому, если у вас есть название компании “ABC Ltd”. и “ABC Corporation” в списке, она вернет первый по порядку и проигнорирует остальные.

Пример 7. Функция ВПР возвращает ошибку, несмотря на совпадение в значении поиска

Это может свести вас с ума, когда вы увидите, что имеется соответствующее значение поиска, а функция возвращает ошибку.

Например, у вас в таблице есть очевидное соответствие (Иван), но система по-прежнему возвращает ошибку.

Рассматривая значения в таблице глубже, вы можете заметить, что в диапазоне данных значение “Иван “ написано с пробелом. Если в диапазоне данных по которым вы осуществляете поиск есть пробелы до значений, после или между словами, то функция их не сопоставит.

Решение есть, и зовут его – функция TRIM. Она удаляет все лишние пробелы в значениях.

Ниже формула, которая поможет вам сопоставить данные несмотря на лишние пробелы в диапазоне данных.

=VLOOKUP(“Иван”,TRIM($A$2:$A$15),1,0) – английская версия

=ВПР(“Иван”,СЖПРОБЕЛЫ($A$2:$A$15);1;0) – русская версия

Для того, чтобы функция TRIM (СЖПРОБЕЛЫ) заработала при использовании VLOOKUP (ВПР), следует вводить формулу после набора не с помощью клавиши Enter на клавиатуре, а с помощью сочетания клавиш Ctrl + Shift + Enter.

Пример 8. Функция ВПР в Excel с несколькими условиями

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

Но часто нам необходимо использовать VLOOKUP с поиском по несколькими критериям.

Представим, что у вас есть таблица с результатами экзаменов студентов по трем уровням экзамена: «Вступительный», «Полугодовой» и «Финальный экзамен»:

Сопоставить данные по конкретному студенту, предмету и уровню экзамена с помощью функции ВПР – нелегкая задача, так как сопоставляя данные по студенту и предмету, она будет выдавать результат первого совпадения, но далеко не факт, что этот результат будет корректным и соответствовать необходимому уровню экзамена.

Осуществить поиск по студенту + названию предмета + уровню экзамена можно с помощью создания вспомогательного столбца, как показано на примере ниже:

Теперь, нам необходимо создать уникальный ID для каждого студента и уровня экзамена с помощью формулы во вспомогательном столбце: =A2&”|”&B2.

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

Благодаря вспомогательному столбцу мы избежали того, что при сопоставлении данных, функция будет выдавать данные по не нужному уровню экзамена.

Теперь вы можете использовать значения вспомогательного столбца в качестве значений поиска.

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

=VLOOKUP($F3&”|”&G$2,$C$2:$D$19,2,0) – английская версия

=ВПР($F3&”|”&G$2;$C$2:$D$19;2;0) – русская версия

С помощью этой формулы мы объединили имя студента и уровень экзамена, чтобы получить значение поиска которое мы проверяем его во вспомогательном столбце и получить данные.

Пример 9. Обработка ошибок с помощью функции ВПР

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

В Excel есть возможность заменить сообщения об ошибке на заданное вами текстовое или числовое значение типа “Нет”, “Не доступно”, “Нет данных” и.т.д.

На примере ниже, мы пытаемся сопоставить результаты экзамена студента Ивана из таблицы. Но так как данных по Ивану в таблице нет, функция выдает ошибку.

Для того, чтобы заменить сообщение ошибки на какое-то более формальное значение нам поможет функция ЕСЛИОШИБКА(IFERROR):

=IFERROR(VLOOKUP(D2,$A$2:$B$7,2,0),”Нет данных”) – английская версия

=ЕСЛИОШИБКА(ВПР(D2;$A$2:$B$7;2;0);”Нет данных”) – русская версия

Функция ЕСЛИОШИБКА(IFERROR) проверяет, если в результате вычислений по формуле выдается ошибка, то она выдает, то значение, которое вы указали в функции ЕСЛИОШИБКА(IFERROR), например “Нет данных”.

Рекомендации

Действие	Результат
Используйте абсолютные ссылки в аргументе интервальный_просмотр	Использование абсолютных ссылок позволяет заполнить формулу так, чтобы она всегда отображала один и тот же диапазон точных подстановок. Узнайте, как использовать абсолютные ссылки на ячейки.
Не сохраняйте числовые значения или значения дат как текст.	При поиске числовых значений или значений дат убедитесь, что данные в первом столбце аргумента таблица не являются текстовыми значениями. Иначе функция ВПР может вернуть неправильное или непредвиденное значение.
Сортируйте первый столбец	Если для аргумента интервальный_просмотр указано значение ИСТИНА, прежде чем использовать функцию ВПР, отсортируйте первый столбец таблицы.
Используйте подстановочные знаки	Если значение аргумента интервальный_просмотр — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому одиночному символу, а звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, поставьте перед ними знак тильды (~). Например, с помощью функции =VLOOKUP(«Fontan?»,B2:E7,2,FALSE) можно выполнить поиск всех случаев употребления фамилии Иванов в различных падежных формах.
Убедитесь, что данные не содержат ошибочных символов.	При поиске текстовых значений в первом столбце убедитесь, что данные в нем не содержат начальных или конечных пробелов, недопустимых прямых (‘ или «) и изогнутых (‘ или “) кавычек либо непечатаемых символов. В этих случаях функция ВПР может возвращать непредвиденное значение. Для получения точных результатов попробуйте воспользоваться функциями ПЕЧСИМВ или СЖПРОБЕЛЫ.