20+ Примеры функции ВПР в Excel для начинающих и опытных пользователей

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

Введение в функцию ВПР — синтаксис и аргументы

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

Синтаксис функции ВПР:

Аргументы:

Примечания по функции:

Базовые примеры ВПР

В этом разделе представлены наиболее часто используемые формулы ВПР.

2,1 Точный и приближённый поиск с помощью ВПР

2,1.1 Выполнение точного поиска с помощью ВПР

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

Например, чтобы получить соответствующие баллы по математике на основе конкретных номеров ID, выполните следующие действия:

Скопируйте и вставьте приведённую ниже формулу в пустую ячейку (в данном случае выбрана G2) и нажмите клавишу «Enter», чтобы получить результат:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

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

Сначала она ищет ID — C1005 в самом левом столбце таблицы, выполняя поиск сверху вниз, и находит значение в ячейке A6.

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

Таким образом, вы получите результат, как показано на приведённом ниже снимке экрана:

2,1.2 Выполните приблизительный поиск с помощью функции ВПР

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

Например, если у вас есть следующий диапазон данных, но указанные заказы отсутствуют в столбце «Заказы», как найти ближайшую скидку из столбца B?

Шаг 1: примените формулу ВПР и заполните ею остальные ячейки

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

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Результат:

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

2,2 Выполните С учетом регистра поиск с помощью функции ВПР в Excel

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

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

Шаг 1: примените любую из приведённых формул и заполните ею остальные ячейки

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

Формула 1: после вставки формулы нажмите сочетание клавиш «Ctrl» + «Shift» + «Enter».

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Формула 2: после вставки формулы нажмите клавишу Enter.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Результат:

Теперь вы получите точные результаты, которые вам нужны. См. снимок экрана:

2,3 Поиск значений с помощью ВПР справа налево в Excel

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

Нажмите, чтобы пошагово ознакомиться с подробностями выполнения этой задачи…

2,4 Поиск второго, n-го или последнего совпадающего значения с помощью ВПР в Excel

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

2,4.1 Поиск с помощью ВПР и возврат второго или n-го совпадающего значения

Допустим, в столбце A у вас указан список имён, а в столбце B — курсы, которые они приобрели. Теперь вы хотите найти второй или n-й курс, купленный конкретным клиентом. См. снимок экрана:

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

Шаг 1: примените формулу и заполните ею остальные ячейки

Например, чтобы получить второе совпадающее значение по заданному критерию, вставьте следующую формулу в пустую ячейку и нажмите одновременно «Ctrl» + «Shift» + «Enter», чтобы получить первый результат. Затем выделите ячейку с формулой и перетащите маркер заполнения вниз до тех ячеек, куда нужно скопировать эту формулу.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Результат:

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

2,4.2 Поиск с помощью ВПР и возврат последнего совпадающего значения

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

2,5 Поиск совпадающих значений с помощью ВПР между двумя заданными значениями или датами

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

2,5.1 Поиск совпадающих значений между двумя заданными значениями или датами с помощью формулы

Шаг 1: упорядочьте данные и примените следующую формулу

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

=LOOKUP(2,1/($A$2:$A$6<,=E2)/($B$2:$B$6>,=E2),$C$2:$C$6)

Результат:

Теперь вы получите все совпадающие записи на основе указанного значения — см. снимок экрана:

2,5.2 Поиск совпадающих значений между двумя заданными значениями или датами с помощью удобной функции

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

1. Чтобы активировать эту функцию, выберите «Kutools» → «Супер ПОИСК» → «Поиск данных между двумя значениями».
2. Затем укажите операции в диалоговом окне в соответствии со своими данными.

2,6 Использование подстановочных знаков для частичного поиска в функции ВПР

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

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

Шаг 1: примените формулу и заполните ею остальные ячейки

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

=VLOOKUP(E2&,"*", $A$2:$C$11, 3, FALSE)

Результат:

Таким образом, все совпадающие баллы были возвращены, как показано на снимке экрана ниже:

2,7 Поиск значений с помощью ВПР из другого листа

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

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

Шаг 1: примените формулу и заполните ею остальные ячейки

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

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Результат:

Вы получите нужные релевантные результаты — см. снимок экрана:

2,8 Поиск значений с помощью ВПР из другой книги

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

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

Шаг 1: примените формулу

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

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Результат:

2,9 Возврат пустой ячейки или определённого текста вместо 0 или ошибки #Н/Д

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

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

3,1 Двунаправленный поиск (ВПР по строке и столбцу)

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

В Excel для двунаправленного поиска можно эффективно комбинировать функции ВПР и ПОИСКПОЗ.

Введите приведённую ниже формулу в любую пустую ячейку и нажмите «Enter», чтобы instantly получить результат.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3,2 Поиск значения с помощью ВПР по двум или более критериям

Легко найти соответствие по одному критерию, но что делать, если критериев два или больше?

3,2.1 Поиск значения с помощью ВПР по двум или более критериям с использованием формул

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

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

Шаг 1: Примените любую из приведённых ниже формул

Формула 1: Введите приведённую ниже формулу и нажмите «Enter».

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Формула 2: Введите приведённую ниже формулу и нажмите «Ctrl» + «Shift» + «Enter».

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Результат:

3,2.2 Поиск значения с помощью ВПР по двум или более критериям с помощью Kutools для Excel

Запомнить вышеуказанные сложные формулы, которые необходимо применять многократно, бывает затруднительно — это снижает эффективность вашей работы. Однако «Kutools для Excel» предлагает функцию «Поиск - Многокритериальный поиск», которая позволяет получить соответствующий результат по одному или нескольким условиям всего за несколько кликов.

1. Чтобы активировать эту функцию, выберите «Kutools» → «Супер ПОИСК» → «Поиск — Многокритериальный поиск».
2. Затем укажите операции в диалоговом окне в соответствии со своими данными.

3,3 ВПР для возврата нескольких значений по одному или нескольким критериям

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

3,3.1 ВПР всех совпадающих значений по одному или нескольким условиям горизонтально

Допустим, у вас есть таблица данных с указанием страны, города и имён в диапазоне A1:C14, и вы хотите вывести все имена по горизонтали для «США», как показано на скриншоте ниже. Чтобы решить эту задачу,щёлкните здесь, чтобы пошагово получить результат.

3,3.2 ВПР всех совпадающих значений по одному или нескольким условиям вертикально

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

3,3.3 ВПР всех совпадающих значений по одному или нескольким условиям в одну ячейку

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

3,4 ВПР для возврата Вся строка найденной ячейки

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

Шаг 1: Примените следующую формулу

Скопируйте или введите приведённую ниже формулу в пустую ячейку, куда вы хотите вывести результат, и нажмите клавишу «Enter», чтобы получить первое значение. Затем перетащите маркер заполнения вправо, пока не отобразится вся строка.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Результат:

Теперь вы видите, что возвращена вся строка с данными. См. скриншот:

3,5 Вложенный ВПР в Excel

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

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

Общий вид формулы для вложенного ВПР:

Шаг 1: Примените и заполните следующую формулу

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

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Результат:

Теперь вы получите результат, как показано на скриншоте ниже:

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

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

Шаг 1: Примените следующую формулу

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

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Результат:

Вы получите нужный результат. См. скриншот:

3,7 ВПР и суммирование всех совпадающих значений в строках или столбцах

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

3,7.1 ВПР и суммирование всех совпадающих значений в строке или нескольких строках

Допустим, у вас есть список товаров с объёмами продаж за несколько месяцев, как показано на скриншоте ниже. Теперь нужно просуммировать все заказы по указанным товарам за все месяцы.

Шаг 1: Примените следующую формулу

Скопируйте или введите приведённую ниже формулу в пустую ячейку и нажмите одновременно клавиши Ctrl + Shift + Enter, чтобы получить первый результат. Затем перетащите маркер заполнения вниз, чтобы применить эту формулу к остальным нужным ячейкам.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Результат:

Все значения в строке первого совпадения были просуммированы. См. скриншот:

3,7.2 ВПР и суммирование всех совпадающих значений в столбце или нескольких столбцах

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

Шаг 1: Примените следующую формулу

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

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Результат:

Теперь первые совпадающие значения за указанный месяц в столбце были просуммированы. См. скриншот:

3,7.3 ВПР и суммирование первого или всех совпадающих значений с помощью Kutools для Excel

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

1. Чтобы активировать эту функцию, выберите «Kutools» → «Супер ПОИСК» → «Поиск и суммирование».
2. Затем укажите нужные операции в диалоговом окне.

3,7.4 ВПР и суммирование всех совпадающих значений одновременно по строкам и столбцам

Если вам нужно просуммировать значения с учётом совпадений и по столбцу, и по строке — например, получить общую сумму продаж товара «Свитер» за март, как показано на скриншоте ниже.

Для решения этой задачи отлично подойдёт функция СУММПРОИЗВ (SUMPRODUCT).

Введите приведённую ниже формулу в ячейку и нажмите клавишу «Enter», чтобы получить результат. См. скриншот:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3,8 ВПР для объединения двух таблиц на основе Ключевой столбец

В повседневной работе при анализе данных часто требуется собрать всю необходимую информацию в одну таблицу на основе одного или нескольких ключевых столбцов. Для этого вместо функции ВПР рекомендуется использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ.

3,8.1 ВПР для объединения двух таблиц на основе одного Ключевой столбец

Например, у вас есть две таблицы: первая содержит данные о товарах и именах, а вторая — о товарах и заказах. Теперь вы хотите объединить их в одну, сопоставив по общему столбцу «Товары».

Шаг 1: Примените следующую формулу

Введите приведённую ниже формулу в пустую ячейку, а затем протяните маркер заполнения вниз до последней ячейки, к которой нужно применить эту формулу.

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Результат:

Теперь вы получите объединённую таблицу, в которой столбец заказов добавлен к первой таблице на основе данных из ключевого столбца.

3,8.2 ВПР для объединения двух таблиц на основе нескольких Ключевой столбец

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

Общая формула:

Шаг 1: примените следующую формулу

Введите приведённую ниже формулу в пустую ячейку, куда вы хотите поместить результат, и нажмите одновременно клавиши «Ctrl» + «Shift» + «Enter», чтобы получить первое совпадающее значение (см. снимок экрана):

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Шаг 2: заполните формулу в другие ячейки

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

3,9 Сопоставление значений функцией ВПР по нескольким листам

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

Сопоставленные значения ВПР сохраняют форматирование ячеек

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

4,1 Поиск значения функцией ВПР с сохранением цвета ячейки и форматирования шрифта

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

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

Шаг 1: скопируйте код 1 в модуль листа

1. На листе с данными для функции ВПР щёлкните правой кнопкой мыши по ярлыку листа и выберите в контекстном меню «Просмотреть код». См. снимок экрана:
   
2. В открывшемся окне «Microsoft Visual Basic for Applications» скопируйте приведённый ниже код VBA в окно редактора кода.
3. Код VBA 1: ВПР для получения форматирования ячейки вместе с искомым значением

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

Шаг 2: скопируйте код 2 в окно модуля

1. В том же окне «Microsoft Visual Basic for Applications» выберите «Вставка» > «Модуль», а затем скопируйте приведённый ниже код VBA 2 в открывшееся окно модуля.
2. Код VBA 2: ВПР для получения форматирования ячейки вместе с искомым значением

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

Шаг 3: выберите параметр для проекта VBA

1. После вставки приведённых выше кодов выберите «Сервис» > «Ссылки» в окне Microsoft Visual Basic for Applications, а затем установите флажок напротив «Microsoft Scripting Runtime» в диалоговом окне «Ссылки – VBAProject». См. снимки экрана:
2. Затем нажмите «ОК», чтобы закрыть диалоговое окно, а после — сохранить и закрыть окно кода.

Шаг 4: введите формулу для получения результата

1. Теперь вернитесь на лист и введите следующую формулу. Затем перетащите маркер заполнения вниз, чтобы получить все результаты вместе с их форматированием. См. снимок экрана:
   

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4,2 Сохранение Формат даты при использовании ВПР Возвращаемое значение

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

Шаг 1: примените следующую формулу

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

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Результат:

Все совпадающие даты были возвращены, как показано на снимке экрана ниже:

4,3 Возврат Комментарий с помощью ВПР

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

Шаг 1: скопируйте код в модуль

1. Нажмите и удерживайте клавиши «ALT» + «F11», чтобы открыть окно «Microsoft Visual Basic for Applications».
2. Нажмите «Вставка» > «Модуль», затем скопируйте и вставьте следующий код в окно «Модуль».
   Код VBA: ВПР с возвратом совпадающего значения и Комментарий:

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. Затем сохраните и закройте окно кода.

Шаг 2: введите формулу для получения результата

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

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4,4 ВПР для чисел, сохранённых как текст

Например, у вас есть диапазон данных, в котором идентификатор в исходной таблице представлен в числовом формате, а идентификатор в ячейках поиска сохранён как текст. В такой ситуации обычная функция ВПР может вернуть ошибку #Н/Д. Чтобы получить корректный результат, оберните функции ТЕКСТ и ЗНАЧ в функцию ВПР. Ниже приведена формула, позволяющая достичь нужного эффекта:

Шаг 1: примените и заполните следующую формулу

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

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Результат:

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