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

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

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

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

Аргументы:

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

Основные примеры ВПР

В этом разделе мы поговорим о некоторых формулах Vlookup, которые вы часто использовали.

2.1 Точное совпадение и приблизительное совпадение ВПР

 2.1.1 ВПР с точным соответствием

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

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

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

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

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

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

Как только он находит значение, он идет вправо в третьем столбце и извлекает из него значение.

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

 2.1.2 Выполнение приблизительного сопоставления ВПР

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

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

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

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

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

Результат:

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

2.2 Выполните ВПР с учетом регистра в Excel

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

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

Шаг 1. Примените любую формулу и заполните ею другие ячейки.

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

Формула 1: После вставки формулы нажмите Shift + Ctrl + 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

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

 2.4.1 ВПР и вернуть второе или n-е совпадающее значение

Предположим, у вас есть список имен в столбце A, курс обучения, который они приобрели, в столбце B. Теперь вы ищете второй или n-й курс обучения, купленный данным клиентом. Смотрите скриншот:

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

Шаг 1. Примените и заполните формулу в других ячейках.

Например, чтобы получить второе совпадающее значение на основе заданных критериев, примените следующую формулу к пустой ячейке и нажмите Shift + Ctrl + 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 ВПР и возврат последнего совпадающего значения

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

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 for Excel, С его ПОИСК между двумя ценностями функцию, вы можете легко вернуть соответствующий элемент на основе определенного значения или даты между двумя значениями или датами.

1. Нажмите Кутулс > Супер ПОСМОТРЕТЬ > ПОИСК между двумя ценностями для включения этой функции.
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 или #Н/Д, это ВПР для возврата пустого или определенного значения вместо 0 или Н/Д учебник может сделать вам одолжение.

Расширенные примеры ВПР

3.1 Двусторонний поиск (ВПР в строке и столбце)

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

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

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

=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: После вставки формулы нажмите Shift + Ctrl + Enter ключи.

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

Результат:

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

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

1. Нажмите Кутулс > Супер ПОСМОТРЕТЬ > Поиск по нескольким условиям для включения этой функции.
2. Затем укажите операции из диалогового окна на основе ваших данных.

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

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

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

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

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

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

 3.3.3 ВПР всех совпадающих значений на основе одного или нескольких условий в одной ячейке

Если вы хотите выполнить Vlookup и вернуть несколько совпадающих значений в одну ячейку с указанным разделителем, новая функция 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: Примените и заполните следующую формулу

Скопируйте или введите следующую формулу в пустую ячейку, а затем нажмите Shift + Ctrl + 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 VLOOKUP и суммирование первых совпадающих или всех совпадающих значений с мощной функцией

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

1. Нажмите Кутулс > Супер ПОСМОТРЕТЬ > ПРОСМОТР и сумма для включения этой функции.
2. Затем укажите операции в диалоговом окне в зависимости от ваших потребностей.

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

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

Здесь вы можете использовать функцию СУММПРОИЗВ для выполнения этой задачи.

Пожалуйста, примените следующую формулу в ячейку, а затем нажмите 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: Примените следующую формулу

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

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

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

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

3.9 ВПР сопоставление значений на нескольких рабочих листах

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

Соответствующие значения VLOOKUP сохраняют форматирование ячеек

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

4.1 Значение сопоставления ВПР и сохранение цвета ячейки, форматирование шрифта

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

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

Шаг 1: Скопируйте код 1 в модуль Sheet Code.

1. На листе содержатся данные, которые вы хотите ВПР, щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код из контекстного меню. Смотрите скриншот:
   
2. В открытом Microsoft Visual Basic для приложений окна, скопируйте приведенный ниже код VBA в окно кода.
3. Код VBA 1: VLOOKUP для получения форматирования ячейки вместе со значением поиска

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 для приложений окна, нажмите Вставить > Модули, а затем скопируйте приведенный ниже код VBA 2 в окно модуля.
2. Код VBA 2: VLOOKUP для получения форматирования ячейки вместе со значением поиска

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: Выберите вариант для VBAproject

1. После вставки вышеуказанных кодов нажмите Инструменты > Рекомендации в Microsoft Visual Basic для приложений окно. Затем проверьте Среда выполнения сценариев Microsoft флажок в Ссылки - VBAProject диалоговое окно. Смотрите скриншоты:
2. Затем нажмите OK чтобы закрыть диалоговое окно, а затем сохраните и закройте окно кода.

Шаг 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 Возврат комментария к ячейке из ВПР

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

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

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

   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))

Результат:

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