Функция vlookup в excel

ВПР (функция ВПР)

ФУНКЦИЯ ВПР используется, если нужно найти элементы в таблице или диапазоне по строкам. Например, можно найти цену на автомобильную часть по номеру детали или получить имя сотрудника на основе его кода сотрудника.

Совет: Чтобы получить дополнительную справку о функции ВПР, просмотрите эти видео с YouTube от экспертов сообщества Excel!

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

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

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

Используйте функцию ВПР для поиска значения в таблице.

ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])

= ВПР (A2; A10: C20; 2; ИСТИНА)

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

Например, если Таблица-массив охватывает ячейки B2: D7, то искомое_значение должен находиться в столбце B.

Искомое_значение может являться значением или ссылкой на ячейку.

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

Первый столбец в диапазоне ячеек должен содержать Искомое_значение. Диапазон ячеек также должен содержать возвращаемое значение, которое нужно найти.

Номер столбца (начиная с 1 для самого левого столбца массива « инфо_таблица»), содержащего возвращаемое значение.

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

Приближенное соответствие: 1/истина предполагает, что первый столбец в таблице отсортирован либо в числовом формате, либо в алфавитном порядке, а затем будет искать ближайшее значение. Это способ по умолчанию, если не указан другой. Например, = ВПР (90; a1: B100; 2; истина).

Точное совпадение: 0/ложь осуществляет поиск точного значения в первом столбце. Например, = ВПР («Смит»; a1: B100; 2; ложь).

Начало работы

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

Значение, которое вам нужно найти, то есть искомое значение.

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

Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона задано значение B2: D11, число B должно быть первым столбцом, а в качестве второго — «C» и т. д.

При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

Теперь объедините все перечисленное выше аргументы следующим образом:

= ВПР (искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением, приближенное соответствие (истина) или точное совпадение (ложь)).

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

Использование функции ВПР в программе Excel

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

Описание функции ВПР

ВПР – это аббревиатура, которая расшифровывается как “функция вертикального просмотра”. Английское названи функции – VLOOKUP.

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

Применение функции ВПР на практике

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

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

Порядок действий в данном случае следующий:

Щелкаем по самой верхней ячейке столбца, значения которого мы хотим заполнить (в нашем случае – это C2). После этого нажимаем на кнопку “Вставить функцию” (fx) слева от строки формул.
В окне вставки функции нам нужна категория “Ссылки и массивы”, в которой выбираем оператор “ВПР” и щелкаем OK.
Теперь предстоит правильно заполнить аргументы функции:
- в поле “Искомое_значение” указываем адрес ячейки в основной таблице, по значению которой будет производиться поиск соответствия во второй таблице с ценами. Координаты можно прописать вручную, либо, находясь курсивом в поле для ввода информации просто кликнуть в самой таблице по нужной ячейке.
- переходим к аргументу “Таблица”. Здесь мы указываем координаты таблицы (или ее отдельной части), в котором будет выполняться поиск искомого значения. При этом важно, чтобы первый столбец указанного диапазона содержал именно те данные, по которым будет осуществляться поиск и сопоставление значений (в нашем случае – это наименования позиций). И, конечно же, в указанные координаты должны попадать ячейки с информацией, которая будет “подтягиваться” в основную таблицу (в нашем случае – это цены).
  Примечание: Таблица может располагаться как на том же листе, что и основная, так и на других листах книги.
- Чтобы координаты, указанные в аргументе “Таблица” не сместились при возможных дальнейших корректировках данных, делаем их абсолютными, так как по умолчанию они являются относительными. Для этого выполняем выделение всей ссылки в поле и нажимаем кнопку F4. В результате перед всеми обозначениями строк и столбцов будут добавлены символы “$”.
- в поле аргумента “Номер_столбца” указываем порядковый номер столбца, значения которого нужно вставить в основную таблицу при совпадении искомого значения. В нашем случае это столбец с ценами, который занимает вторую позицию в указанной выше области (аргумент “Таблица”).
- в значении аргумента “Интервальный_просмотр” можно указать два значения:
  - ЛОЖЬ (0) – результат будет выводиться только в случае точного совпадения;
  - ИСТИНА (1) – будут выводиться результаты по приближенным совпадениям.
  - мы выбираем первый вариант, так как нам важна предельная точность.
- Когда все готово, нажимаем OK.
В выбранной ячейку, куда мы вставили функцию, автоматически вставилась требуемая цена.Причем, если мы изменим значение во второй таблице с ценами, так как данные взаимосвязаны посредством функции, то и в основной таблице произойдут соответствующие изменения.
Чтобы автоматически заполнить аналогичными данными другие ячейки столбца, воспользуемся Маркером заполнения. Для этого наводим курсор мыши на нижний правый угол ячейки с результатом, когда появится черный плюсик, зажав левую кнопку мыши тянем его вниз до конца таблицы или до то ячейки, которую нужно заполнить.
В итоге нам удалось получить в основной таблице все данные по ценам, а также посчитать итоговые суммы по продажам, что и требовалось сделать.

Заключение

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

Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую

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

Читать еще: Как задать формулу в таблице excel

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

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

Даже если вы годами используете функцию ВПР, то с высокой долей вероятности эта статья будет вам полезна и не оставит равнодушным. Я, например, будучи IT-специалистом, а потом и руководителем в IT, пользовался VLOOKUP 15 лет, но разобраться со всеми нюансами довелось только сейчас, когда я на профессиональной основе стал обучать людей Excel.

ВПР — это аббревиатура от вертикального просмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP). ГПР, в противоположность ВПР, производит горизонтальный поиск, однако концепция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни.

Функция ВПР имеет четыре параметра:

=ВПР( ; ; [; ] ), тут:

— искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев);

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

— номер столбца в диапазоне, из которого будет возвращено значение;

— это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона . В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.

Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0) либо же о диапазонном просмотре (ИСТИНА или 1).

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

Как же конкретно работает формула ВПР

Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром , в противном случае возвращается код ошибки #Н/Д (#N/A).

Схемы работы формул

ВПР тип II

Следствия для формул вида I

Формулы можно использовать для распределения значений по диапазонам.
Если первый столбец содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
Данный вид вернёт ошибку #Н/Д, только если не найдёт значения меньше или равное искомому.
Понять, что формула возвращает неправильные значения, в случае если ваш массив не отсортирован, довольно затруднительно.

Следствия для формул вида II

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

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

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

мне нужен более мощный компьютер;
мне нужна более быстрая формула, например, многие знают про ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая якобы быстрее на жалкие 5–10%.

И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Не на 57%, а на 5 700%. Данный факт я проверил вполне надёжно.

Секрет такой быстрой работы кроется в том, что на отсортированном массиве можно применять чрезвычайно эффективный алгоритм поиска, который носит название бинарного поиска (метод деления пополам, метод дихотомии). Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще. То же самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР (LOOKUP), которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus 1-2-3.

Недостатки формулы

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

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

Диапазонный поиск

Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа.

Поиск текстовых строк

Безусловно, ВПР ищет не только числа, но и текст. При этом надо принимать во внимание, что регистр символов формула не различает. Если использовать символы подстановки, то можно организовать нечёткий поиск. Есть два символа подстановки: «?» — заменяет один любой символ в текстовой строке, «*» — заменяет любое количество любых символов.

Борьба с пробелами

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

Разный формат данных

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

Читать еще: Формула прогноза в excel

=ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если D7 содержит текст, а таблица — числа;

=ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — и наоборот.

Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:

Двойное отрицание —D7.
Умножение на единицу D7*1.
Сложение с нулём D7+0.
Возведение в первую степень D7^1.

Перевод числа в текст производится через сцепку с пустой строкой, которая заставляет Excel преобразовать тип данных.

Как подавить выдачу #Н/Д

Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).

Например: =ЕСЛИОШИБКА( ВПР(D7; Продукты!$A$2:$C$5; 3; 0); «»).

Если ВПР вернёт код ошибки #Н/Д, то ЕСЛИОШИБКА его перехватит и подставит параметр 2 (в данном случае пустая строка), а если ошибки не произошло, то эта функция сделает вид, что её вообще нет, а есть только ВПР, вернувший нормальный результат.

Часто забывают ссылку массива сделать абсолютной, и при протягивании массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5.

Хорошей идеей является размещение справочного массива на отдельном листе рабочей книги. Не путается под ногами, да и сохраннее будет.

Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона.

Многие пользователи при указании массива используют конструкцию вида A:C, указывая столбцы целиком. Этот подход имеет право на существование, так как вы избавлены от необходимости отслеживать тот факт, что ваш массив включает все необходимые строки. Если вы добавите строки на лист с первоначальным массивом, то диапазон, указанный как A:C, не придётся корректировать. Безусловно, эта синтаксическая конструкция заставляет Excel проводить несколько большую работу, чем при точном указании диапазона, но данными накладными расходами можно пренебречь. Речь идёт о сотых долях секунды.

Ну и на грани гениальности — оформить массив в виде умной таблицы.

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

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

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

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

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

Функция ВПР в Excel и её секреты. Поиск VLOOKUP в базах данных

Функция ВПР в Exce l позволяет работать с большими таблицами и базами данных. В статье рассмотрим примеры использования функции ВПР в Excel. Дополнительно с функцией Excel VLOOKUP разберем динамический индекс Col index num, IF и поиск в нескольких столбцах или базе ДВССЫЛ INDIRECT.

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

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

Критерий поиска (Lookup_value)
Матрица (Table_array)
Индекс (Col_index_num)
Порядок сортировки (Range_lookup).

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

Структура формулы ВПР в Excel

Первая строка – значение, которое вы хотите найти в базе данных Excel. В примере необходимая информация находится в ячейке B3. Если вы введете ее адрес в первое поле, при помощи выражения можно найти нужные сведения в списке со сведениями о персонале.

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

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

Существует два варианта значений для параметра «Порядок сортировки». Введете 0 – тогда результатом вставки будет ошибка #Н/Д, если не сможет найти в базе Экселя результаты по запросу.

Результат отображается только в том случае, если между заданным критерием отбора (Lookup_value) и результатами в крайней первой колонке массива (Table_array) будет обнаружено абсолютное совпадение.

Введете 1 – и формула тоже отобразит результат, если найдет полное совпадение между Lookup_value и содержимым какой-либо строки в первом столбце матрицы (Table_array).

Но если нет соответствия, то будет выбрано значение, приближенное к этому числу. Другими словами, если вы в примере вместо B3 напишете 12, функция вернет ответ «Gwendy», потому что 10 является числом, максимально приближенным к 12. Первая колонка в таблице заранее должна быть отсортирована по возрастанию, если вы вставляете 1 в Range_lookup.

Поиск приблизительного значения ВПР в Excel

В примере (снимок № 2) ВПР используется для вычисления прибавки на основе текущего оклада. Критерий отбора – это заработная плата, написана в F3. Матрица – это диапазон $I$3:$J$10 (весь массив).

Адрес является абсолютным (это показывают знаки $), чтобы осталась возможность копировать всю получившуюся конструкцию без изменения диапазона. Col_index_num равен 2 (процент повышения находится в соседнем массиве).

Параметр порядка сортировки Range_lookup – 1, потому что нужно, чтобы формула выдавала результат, даже если нет идеального соответствия. Если Lookup_value составляет 35 850, ВПР не может найти идеальное совпадение в массиве, но ближайшее минимальное число – 35 000, поэтому ответом на выражение будет 4%. На этом ликбез « ВПР в Excel для чайников » заканчивается.

Сравнение двух списков с помощью ВПР в Excel

Вы можете использовать ВПР в Excel для сравнения двух баз данных . Критерий отбора в этом примере (скриншот № 3) – идентификатор персонала (он должен быть уникальным значением) в первой колонке.

Матрица – это диапазон идентификаторов персонала во втором списке или массиве. Сделайте ссылку на массив абсолютной, используя знаки $ в адресах ячеек ($I$3:$I$9), чтобы иметь возможность копировать выражение без изменения ссылок на ячейки диапазона.

В аргументе «Порядок сортировки» введите 0 – это обязательно потому, что вам нужно только точное пересечение. Скопируйте получившееся, и если в ответ отображается #Н/Д, это значит, что во втором списке или в базе искомая запись отсутствует.

Динамический индекс (Col_index_num) с использованием нумерованных столбцов

Если вам нужно найти какую-либо информацию в нескольких колонках, в поле «Индекс» можете использовать относительные ссылки на ячейки.

Это позволяет сэкономить очень много времени и не вводить номер столбца каждый раз. На снимке № 4 нумерация колонок выполняется в строке 4. В поле Col_index_num нужно ввести C4, и получится первый динамический индекс.

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

Динамический индекс со вложенными IF (ЕСЛИ)

Вы можете использовать формулы IF (ЕСЛИ) в Экселе для создания динамического индекса. В примере (скриншот № 5) премия рассчитывается в зависимости от отдела и категории.

Читать еще: Как расшифровывается функция впр в excel

Если сотрудник, работая в отделе продаж, входит в группу 1 бонусной категории, формула VLOOKUP должна вернуть 3%, если в группу 5 бонусной категории – 4%, а если в группу 10 – 5%.

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

Критерий поиска – это отдел, E3. Матрица – диапазон $J$3:$M$7. В поле «Индекс» указывается конструкция по типу IF(G3=«Группа 1»,2,IF(G3=«Группа 5»,3,4)).

С помощью первой функции IF вы сможете узнать, соответствует ли G3 запросу «Группа 1». Если это так, формула VLOOKUP выберет бонус из второго столбца. Если это неправда, второе выражение IF проверяет, соответствует ли G3 запросу «Группа 5».

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

Поиск в нескольких столбцах или базе Excel с INDIRECT (ДВССЫЛ)

В примере (снимок № 6) имеется 3 таблицы. Диапазоны данных в каждой из них имеют названия «Финансы», «Производство» и «Продажи».

В этом примере вам нужно найти сотрудника из отдела продаж с идентификатором 3. Название диапазона вводится в C19, идентификатор – в E19. Ячейка B22 связана с C19, и B22 (идентификатор) – это Lookup_value. Выражение с ДВССЫЛ находится в таблице – INDIRECT воспринимает содержимое C19 как имя диапазона.

Далее указываете номер колонки в матрице, откуда функция должна извлекать сведения и отображать их в ячейке при помощи VLOOKUP. В Range_lookup введите 0, потому что здесь требуется только точное пересечение.

ВПР – одно из самых полезных и важных средств поиска в Microsoft Excel . Он обычно используется для выполнения запросов к огромным листам с кучей информации, когда ручная работа может занять слишком много времени. Буква «V» в VLOOKUP означает «вертикальный», поэтому ее также иногда называют формулой вертикального отбора.

Особенности функции вертикального поиска в Excel

Регистр в запросах не учитывается.
В строке «Порядок сортировки» значение по умолчанию равно 1 (TRUE-ПРАВДА). Поэтому лучше не пропускать этот аргумент, если вам нужны только точные совпадения.
В первом поле (там, где вы пишете запрос) допускается использование подстановочных знаков.

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

Использование функции ВПР (VLOOKUP) для подстановки значений

Кому лень или нет времени читать — смотрим видео. Подробности и нюансы — в тексте ниже.

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

Итак, имеем две таблицы — таблицу заказов и прайс-лист:

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

В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP) . Эта функция ищет заданное значение (в нашем примере это слово «Яблоки») в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:

Для простоты дальнейшего использования функции сразу сделайте одну вещь — дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме «шапки» (G3:H19), выберите в меню Вставка — Имя — Присвоить (Insert — Name — Define) или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы — Вставка функции (Formulas — Insert Function) . В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:

Заполняем их по очереди:

Искомое значение (Lookup Value) — то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае — слово «Яблоки» из ячейки B3.
Таблица (Table Array) — таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя «Прайс» данное ранее. Если вы не давали имя, то можно просто выделить таблицу, но не забудьте нажать потом клавишу F4 , чтобы закрепить ссылку знаками доллара , т.к. в противном случае она будет соскальзывать при копировании нашей формулы вниз, на остальные ячейки столбца D3:D30.
Номер_столбца (Column index number) — порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.
Интервальный_просмотр (Range Lookup) — в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:

Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.

Ошибки #Н/Д и их подавление

Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если:

Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.
Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.
Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
=ВПР(ТЕКСТ(B3);прайс;0)
Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:
=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)
=VLOOKUP(TRIM(CLEAN(B3));прайс;0)

Для подавления сообщения об ошибке #Н/Д (#N/A) в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR) . Так, например, вот такая конструкция перехватывает любые ошибки создаваемые ВПР и заменяет их нулями:

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