Функция, формула ВПР (VLOOKUP)

Пример задачи на соединение таблиц

Предположим мы имеем две таблици с данными - таблицу кредитов и таблицу обеспечения:

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

Решение.

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

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

Заполните их один за другим:

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

P.S.1

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

  1. Разрешено точный поиск (аргумент Range Lookup = 0) и искомого значения нет в таблице (Table).
  2. Разрешен приблизительный поиск (Range Lookup = 1), но в таблице (T able), в которой мы ищем значение, названия не посортированы по возрастанию .
  3. Формат ячейки, с которой мы берем номер сделки (например, С2 в нашем случае) и формат ячейки первой колонки (G2:G11) таблицы различны (например, цифровой и текстовый). Эта ситуация особенно типична, когда используются цифровые коды вместо текстовых названий (номера счетов, идентификационные коды, даты и т.п.). В этом случае вы можете использовать функции VALUE и TEXT для преобразования форматов данных. Это выглядит так:
    = VLOOKUP (TEXT (C2)$G$2:$H$11;0).
  4. Функция не находит значение тому, что в коде есть пробелы и непечатаемые символы (переносы строки и т.п.). В этом случае вы можете использовать текстовую функцию (TRIM) и (CLEAN) для их удаления:
    =VLOOKUP(TRIM(CLEAN(C2));$G$2:$H$11;0).

P.S.2

Для подавления сообщения об ошибке (#N/A), когда функция не может найти точного совпадения, вы можете использовать функцию IFERROR.

Функция IFERROR проверяет, является ли ошибка (#N/A) результатом работы VLOOKUP, и если да, то выводит пустую строку ("") или ноль, если нет - результат работы VLOOKUP.