20+ примеров использования VLOOKUP для начинающих и продвинутых пользователей Excel

Функция VLOOKUP — одна из самых востребованных в Excel. В этом руководстве вы узнаете, как применять VLOOKUP в Excel на практике: пошагово рассмотрим десятки базовых и продвинутых примеров.

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

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

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

Аргументы:

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

Базовые примеры VLOOKUP

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

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

 2.1.1 Точное совпадение в VLOOKUP

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

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

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

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

Как работает формула VLOOKUP?

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

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

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

 2.1.2 Приблизительное совпадение в VLOOKUP

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

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

Шаг1: Примените формулу VLOOKUP и протяните её в другие ячейки

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

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

Результат:

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

2.2 Как сделать VLOOKUP с учетом регистра в Excel

По умолчанию функция VLOOKUP не различает регистр, то есть воспринимает строчные и прописные буквы одинаково. Иногда требуется выполнить поиск с учетом регистра, но стандартная VLOOKUP не справляется с этой задачей. В таком случае используйте альтернативные функции, например, INDEX и MATCH вместе с EXACT, либо LOOKUP и EXACT.

Например, у меня есть диапазон данных, где столбец 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 VLOOKUP значений справа налево в Excel

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

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

2.4 VLOOKUP второго, n-го или последнего совпадения в Excel

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

 2.4.1 VLOOKUP и возврат второго или n-го совпадения

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

Здесь функция VLOOKUP не решит задачу напрямую. Но вы можете использовать функцию INDEX как альтернативу.

Шаг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 VLOOKUP и возврат последнего совпадения

Если вы хотите выполнить VLOOKUP и вернуть последнее совпадение, как показано на скриншоте ниже, этот урок VLOOKUP And Return The Last Matching Value поможет вам подробно получить последнее совпадение.

2.5 VLOOKUP совпадающих значений между двумя заданными значениями или датами

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

 2.5.1 VLOOKUP совпадающих значений между двумя заданными значениями или датами с помощью формулы

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

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

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

Результат:

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

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

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

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

2.6 Использование подстановочных знаков для частичных совпадений в функции VLOOKUP

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

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

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

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

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

Результат:

Все совпавшие оценки возвращены, как показано на скриншоте ниже:

2.7 VLOOKUP значений с другого листа

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

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

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

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

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

Результат:

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

2.8 VLOOKUP значений из другой книги

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

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

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

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

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

Результат:

2.9 Возврат пустой ячейки или заданного текста вместо0 или ошибки #N/A

Обычно при использовании функции VLOOKUP для возврата соответствующего значения, если совпавшая ячейка пуста, возвращается0. А если совпадение не найдено, вы получите ошибку #N/A, как показано на скриншоте ниже. Если вы хотите отображать пустую ячейку или определённое значение вместо0 или #N/A, этот урок VLOOKUP To Return Blank Or Specific Value Instead Of0 Or N/A может вам помочь.

Продвинутые примеры VLOOKUP

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

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

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

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

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

3.2 VLOOKUP по двум и более критериям

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

 3.2.1 VLOOKUP по двум и более критериям с помощью формул

В этом случае функции LOOKUP или MATCH и INDEX в 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 VLOOKUP по двум и более критериям с помощью Kutools для Excel

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

1. Нажмите "Kutools" > "Супер ПОИСК" > "Многокритериальный поиск", чтобы включить эту функцию.
2. Затем укажите параметры в диалоговом окне в зависимости от ваших данных.

3.3 VLOOKUP для возврата нескольких значений по одному или нескольким условиям

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

 3.3.1 VLOOKUP всех совпавших значений по одному или нескольким условиям по горизонтали

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

 3.3.2 VLOOKUP всех совпавших значений по одному или нескольким условиям по вертикали

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

 3.3.3 VLOOKUP всех совпавших значений по одному или нескольким условиям в одну ячейку

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

3.4 VLOOKUP для возврата всей строки совпавшей ячейки

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

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

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

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

Результат:

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

3.5 Вложенный VLOOKUP в Excel

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

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

Общая формула для вложенного VLOOKUP:

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

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

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

Результат:

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

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

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

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

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

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

Результат:

Вы получите результат, как требуется, смотрите скриншот:

3.7 VLOOKUP и суммирование всех совпавших значений в строках или столбцах

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

 3.7.1 VLOOKUP и суммирование всех совпавших значений в строке или нескольких строках

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

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

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

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

Результат:

Все значения в строке первого совпавшего значения просуммированы, смотрите скриншот:

 3.7.2 VLOOKUP и суммирование всех совпавших значений в столбце или нескольких столбцах

Если нужно просуммировать итоговое значение за определённые месяцы, как показано на скриншоте ниже. Обычная функция VLOOKUP не поможет, здесь нужно использовать функции SUM, INDEX и MATCH вместе для создания формулы.

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

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

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

Результат:

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

 3.7.3 VLOOKUP и суммирование первого или всех совпавших значений с помощью Kutools для Excel

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

1. Нажмите "Kutools" > "Супер ПОИСК" > "Поиск и суммирование", чтобы включить эту функцию.
2. Затем укажите параметры в диалоговом окне в зависимости от ваших потребностей.

 3.7.4 VLOOKUP и суммирование всех совпавших значений одновременно по строкам и столбцам

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

Здесь можно использовать функцию SUMPRODUCT для выполнения этой задачи.

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

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

3.8 VLOOKUP для объединения двух таблиц по ключевым столбцам

В повседневной работе при анализе данных часто требуется собрать всю необходимую информацию в одну таблицу по одному или нескольким ключевым столбцам. Для этого используйте функции INDEX и MATCH вместо VLOOKUP.

 3.8.1 VLOOKUP для объединения двух таблиц по одному ключевому столбцу

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

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

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

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

Результат:

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

 3.8.2 VLOOKUP для объединения двух таблиц по нескольким ключевым столбцам

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

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

Шаг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 VLOOKUP совпадающих значений на нескольких листах

Вам когда-нибудь приходилось выполнять VLOOKUP на нескольких листах Excel? Например, если у вас есть три листа с диапазонами данных и вы хотите получить определённые значения по критериям с этих листов, следуйте пошаговому руководству VLOOKUP Values Across Multiple Worksheets для выполнения этой задачи.

VLOOKUP с сохранением форматирования ячеек

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

4.1 VLOOKUP совпавшего значения с сохранением цвета ячейки и форматирования шрифта

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

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

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

1. На листе с данными, которые нужно искать через VLOOKUP, щёлкните правой кнопкой мыши по вкладке листа и выберите "Просмотреть код" в контекстном меню. Смотрите скриншот:
   
2. В открывшемся окне "Microsoft Visual Basic for Applications" скопируйте приведённый ниже 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 for Applications" нажмите "Вставка" > "Модуль" и скопируйте приведённый ниже 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 for Applications". Затем установите флажок "Microsoft Scripting Runtime" в диалоговом окне "Ссылки — VBAProject". Смотрите скриншоты:
2. Затем нажмите "ОК" для закрытия диалогового окна, сохраните и закройте окно кода.

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

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

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

   
   

4.2 Сохранение формата даты для значения, возвращённого VLOOKUP

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

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

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

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

Результат:

Все совпавшие даты возвращены, как показано на скриншоте ниже:

4.3 Возврат комментария ячейки через VLOOKUP

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

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

1. Удерживайте клавиши "ALT" + "F11", чтобы открыть окно "Microsoft Visual Basic for Applications".
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 VLOOKUP для чисел, сохранённых как текст

Например, у меня есть диапазон данных, где номер ID в исходной таблице — числовой, а в ячейках поиска — текстовый. При использовании обычной функции VLOOKUP может возникнуть ошибка #N/A. В этом случае для получения правильной информации оберните функции TEXT и VALUE внутри VLOOKUP. Вот формула для этого:

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

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

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

Результат:

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