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

Функция ВПР (VLOOKUP) в Excel используется для поиска данных из одной таблицы и сопоставления их с данными из другой таблицы.

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

Видео-урок: Как использовать функцию ВПР в Excel

Функция ВПР в Excel для чайников

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

Синтаксис

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

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

Аргументы функции

• lookup_value(искомое_значение) – это величина для поиска, из крайнего левого столбца таблицы. Это может быть значение, ссылка на ячейку или текстовая строка. В примере со студентами это их фамилии;
• table_array (таблица) – это диапазон данных, в котором будет осуществлен поиск. Это может быть ссылка на диапазон ячеек или именованный диапазон. В примере с таблицей со студентами, это будет вся таблица, которая содержит оценку и фамилии студентов;
• col_index (номер_столбца) – это порядковый номер столбца в диапазоне с данными, из которого будет получена искомая величина;
• [range lookup]([интервальный_просмотр]) – этот аргумент указывает на точность совпадения данных при поиске. Укажите “0” – если точное, “1” – если приблизительное совпадение.

Дополнительная информация

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

Примеры использования ВПР в 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. Двухфакторный поиск данных

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

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

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

Пример 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 с поиском по несколькими критериям.

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

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

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

Теперь, нам необходимо создать уникальный >=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 Excel на русском языке пишется как ВПР. С ее помощью осуществляется поиск указанной величины среди какого-то определенного множества данных величин, приведенных в одной колонке.

Необходимость ВПР

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

Как пользоваться Vlookup в Excel будет показано в этой статье.

Синтаксис функции

Vlookup Excel function= ВПР по-русски. Синтаксис этой функции следующий:

• ВПР (значение_заданное_для_поиска, таблица_в_которой_осуществляется_поиск, номер_столбца, [истина_ложь]).

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

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

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

Как пользоваться Vlookup в Excel, исходя из приведенного выше синтаксиса?

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

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

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

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

Предположим, что в рассматриваемом примере использования Vlookup в Excel осуществляется поиск товаров, объединенных под общим названием «Корма для кошек».

По умолчанию колонка электронной таблицы с прайс-листами «Наименование товара» отсортировывается по возрастанию. Формат данной колонки в двух таблицах должен быть одинаковым.

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

На панели инструментов кликаем на иконку fx и выбираем функцию ВПР, которую можно найти поиском, а также просматривая категорию «Ссылки и массивы».

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

• исходное значение — указываем ячейку, в которую будет подставляться цена искомого товара;
• таблица — указываем диапазон таблицы «Прайс-лист» полностью;
• номер столбца — указываем номер колонки в таблице «Прайс-лист»;
• интервальный просмотр — указываем «ЛОЖЬ» (это немножко непонятно для обычного пользователя, поскольку «ЛОЖЬ» показывает точное значение, а «ИСТИНА» — приближенное).

Подтверждаем введенные значения, в результате получим расчет. Таким образом, на данном примере мы рассмотрели некоторые моменты пользования Vlookup в Excel.

Улучшаем ввод

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

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

Таким образом, мы рассмотрели основные условия, как пользоваться Vlookup в Excel.

Ошибка #Н/Д (#N/A) В ВПР (Vlookup)

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

Чтобы предотвратить появление этой надписи, то есть оставить ячейку пустой в случае ненахождения требуемого значения, в Excel до версии 2007 года вводим =ЕСЛИ(ЕНД(ВПР(;Лист1!;номер столбца;значение «Истина» = 1 или «Ложь» = 0));"";ВПР(копируем то же, что в предыдущей скобке)))

С 2007 версии эту формулу можно упростить =ЕСЛИОШИБКА(ВПР(указываем то же самое, что и в предыдущей формуле);"")

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

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

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

Например, у нас есть поставщики, которые осуществляют поставку разных материалов. Поставщики отображаются в первой колонке, а товары — во второй. Если осуществлять поиск только по поставщикам, мы можем попасть не на тот товар; даже если переставить колонки местами, пользователь попадет на тот товар, но другого поставщика. Поэтому осуществляют объединение этих двух колонок с помощью знака &: =B2&C2. В результате в колонку А будет помещена объединенная колонка (если данную формулу вводили в A2, а затем протягивали), по которой будет осуществляться поиск аналогично предыдущему.

Как работает Vlookup в Excel при вставке значений без привязывания к таблице?

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

Пишем свой Vlookup

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

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

Dim d1 As Double

d1 = Application.WorksheetFunction.Vlookup("имя_работника_из_первой_колонки", Range("диапазон_таблицы"), номер_колонки)

MsgBox Format(d1, "Currency"), , "VBA VLookup Function"

В заключение

В данной статье был рассмотрен вопрос: «Как пользоваться Vlookup в Excel?» Использование этой функции может сильно облегчить жизнь тем пользователям, которые ищут данные по разным таблицам, занимаются сравнением различных сведений. Применение ВПР полезно для тех пользователей, которые добавляют информацию из одной таблицы в другую с поиском по заданному критерию. В некоторых случаях функции может оказаться недостаточно, тогда пользователи, разбирающиеся в языке VBA, могут создавать макросы, которые расширяют функционал Vlookup.

Преамбула

Как известно, знание функции VLOOKUP в MS EXCEL, достаточно чтобы в Москве стать средним аналитиком. Если человек знаком еще и с PIVOT или например знает как убрать дубликаты из списка — все двери в счастливый офисный мир перед ним настежь раскыты.

У сообщества Хабрахабр, конечно, такие знания могут вызвать лишь улыбку умиления. Работать с данными (если вообще до этого снисходить) допустимо только на олдскульном ANSI T-SQL — 92.

Но иногда суровая реальность заставляет сравнивать массивы данных. При этом, как правило, нет времени на перенос данных в СУБД, либо это просто нецелесообразно. Поэтому предлагаю уважаемому сообществу поделиться друг с другом своими «фишками» для удобной и быстрой обработки данных в EXCEL. Не пропадать же добру…

Амбула, собственно

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

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

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

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

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