Функція VLOOKUP є однією з найпопулярніших функцій у Excel. У цьому посібнику крок за кроком описано, як використовувати функцію VLOOKUP в Excel, із десятками базових і розширених прикладів.

Впровадження функції VLOOKUP - Синтаксис та аргументи

У Excel функція VLOOKUP є потужною функцією для більшості користувачів Excel, вона дозволяє вам шукати значення в крайньому лівому куті діапазону даних і повертати відповідне значення в тому самому рядку зі стовпця, який ви вказали, як показано на наступному знімку екрана .

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

аргументи:

Примітки до функцій:

Основні приклади VLOOKUP

У цьому розділі ми поговоримо про деякі формули Vlookup, які ви часто використовуєте.

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

 2.1.1 Виконайте VLOOKUP для точного збігу

Зазвичай, якщо ви шукаєте точну відповідність із функцією VLOOKUP, вам просто потрібно використовувати FALSE як останній аргумент.

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

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

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

Як працює формула VLOOKUP?

Спочатку він шукає ідентифікатор - C1005 у крайньому лівому стовпці таблиці. Він рухається зверху вниз і знаходить значення в клітинці A6.

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

Отже, ви отримаєте результат, як показано на скріншоті нижче:

 2.1.2 Виконайте приблизну відповідність VLOOKUP

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

Наприклад, якщо у вас є такий діапазон даних, а вказані замовлення відсутні в стовпці «Замовлення», як отримати найближчу знижку в стовпці B?

Крок 1. Застосуйте формулу VLOOKUP і заповніть нею інші клітинки

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

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

Результат:

Тепер ви отримаєте приблизні збіги на основі заданих значень, див. знімок екрана:

2.2 Виконайте VLOOKUP з урахуванням регістру в Excel

За замовчуванням функція VLOOKUP виконує пошук без урахування регістру, що означає, що вона розглядає малі та великі символи як ідентичні. Іноді вам може знадобитися виконати пошук у Excel з урахуванням регістру, звичайна функція VLOOKUP може не вирішити цю проблему. У цьому випадку ви можете використовувати альтернативні функції, такі як INDEX і MATCH з функцією EXACT, або функціями LOOKUP і EXACT.

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

Крок 1. Застосуйте будь-яку одну формулу та заповніть нею інші клітинки

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

Формула 1: Після вставлення формули натисніть Ctrl + Shift + Enter ключі.

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

Формула 2: Після вставлення формули натисніть Що натомість? Створіть віртуальну версію себе у ключ

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

Результат:

Тоді ви отримаєте правильні результати, які вам потрібні. Перегляньте скріншот:

2.3 Значення VLOOKUP справа наліво в Excel

Функція VLOOKUP завжди шукає значення в крайньому лівому стовпці діапазону даних і повертає відповідне значення зі стовпця справа. Якщо ви хочете виконати зворотний VLOOKUP, що означає пошук певного значення в правому стовпці та повернення його відповідного значення в лівому стовпці, як показано на знімку екрана нижче:

Клацніть, щоб поетапно дізнатись про це завдання ...

2.4 VLOOKUP друге, n-те або останнє відповідне значення в Excel

Зазвичай, якщо під час використання функції Vlookup знайдено кілька відповідних значень, повертатиметься лише перший відповідний запис. У цьому розділі я розповім про те, як отримати друге, n-те або останнє відповідне значення в діапазоні даних.

 2.4.1 VLOOKUP і повертає друге або n-те відповідне значення

Припустімо, у стовпчику A є список імен, у стовпчику B – навчальний курс, який вони придбали. Тепер ви шукаєте 2-й або 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 і повертає останнє відповідне значення підручник може допомогти вам детально отримати останнє відповідне значення.

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. Натисніть Кутулс > Супер ПОГЛЯД > ПЕРЕГЛЯД між двома значеннями щоб увімкнути цю функцію.
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 для повернення пустого або конкретного значення замість 0 або Н/Д підручник може зробити вам послугу.

Розширені приклади VLOOKUP

3.1 Двосторонній пошук (VLOOKUP у рядку та стовпці)

Іноді вам може знадобитися виконати двовимірний пошук, що означає пошук значення в рядку та стовпці одночасно. Наприклад, якщо у вас є такий діапазон даних, і вам може знадобитися отримати значення для певного продукту за вказаний квартал. У цьому розділі представлено формулу для виконання цієї роботи в Excel.

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

Будь ласка, застосуйте наступну формулу до порожньої комірки, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат.

=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: Після вставлення формули натисніть Що натомість? Створіть віртуальну версію себе у ключ

=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 пропонує Пошук кількох умов функція, яка дозволяє лише кількома клацаннями миші повернути відповідний результат на основі однієї чи кількох умов.

1. Натисніть Кутулс > Супер ПОГЛЯД > Пошук кількох умов щоб увімкнути цю функцію.
2. Потім укажіть операції в діалоговому вікні на основі ваших даних.

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

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

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

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

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

Якщо вам потрібно виконати Vlookup і повернути всі відповідні значення вертикально на основі конкретних критеріїв, як показано на знімку екрана нижче, будь ласка, натисніть тут, щоб отримати детальне рішення.

 3.3.3 VLOOKUP усі відповідні значення на основі однієї чи кількох умов у одну клітинку

Якщо ви хочете виконати Vlookup і повернути кілька відповідних значень в одну клітинку з указаним роздільником, нова функція TEXTJOIN може допомогти вам швидко та легко вирішити цю задачу.

3.4 VLOOKUP для повернення всього рядка відповідної клітинки

У цьому розділі я розповім про те, як отримати весь рядок відповідного значення за допомогою функції VLOOKUP.

Крок 1: Застосуйте та заповніть наступну формулу

Будь ласка, скопіюйте або введіть наведену нижче формулу в порожню клітинку, де ви хочете вивести результат, і натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати перше значення. Потім перетягніть клітинку формули вправо, доки не відобразяться дані всього рядка.

=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 і підсумовування перших або всіх відповідних значень за допомогою потужної функції

Можливо, наведені вище формули вам важко запам’ятати, у цьому випадку я порекомендую потужну функцію – Пошук і сума of Kutools для Excel, за допомогою цієї функції ви можете виконувати Vlookup і підсумовувати перші або всі відповідні значення в рядках або стовпцях якомога легше.

1. Натисніть Кутулс > Супер ПОГЛЯД > ПОГЛЯД і сума щоб увімкнути цю функцію.
2. Потім у діалоговому вікні вкажіть операції відповідно до ваших потреб.

 3.7.4 VLOOKUP і підсумуйте всі відповідні значення в рядках і стовпцях

Якщо ви хочете підсумувати значення, коли вам потрібно зрівняти як стовпець, так і рядок, наприклад, щоб отримати загальне значення товару Светр у березні місяці, як показано на знімку екрана нижче.

Тут ви можете скористатися функцією SUMPRODCT для виконання цього завдання.

Будь ласка, застосуйте наступну формулу до клітинки, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат, див. знімок екрана:

=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 на кількох аркушах виконати це завдання.

Відповідні значення VLOOKUP зберігають форматування комірок

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

4.1 VLOOKUP відповідне значення та збереження кольору комірки, форматування шрифту

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

Будь ласка, виконайте наступні кроки для пошуку та повернення відповідного значення разом із форматуванням комірок:

Крок 1: Скопіюйте код 1 у модуль коду аркуша

1. На робочому аркуші містяться дані, які потрібно відобразити у VLOOKUP, клацніть правою кнопкою миші вкладку аркуша та виберіть Переглянути код з контекстного меню. Дивіться знімок екрана:
   
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 для додатків вікна, натисніть Insert > Модулі, а потім скопіюйте наведений нижче код 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. Вставивши наведені вище коди, натисніть Tools > посилання в Microsoft Visual Basic для додатків вікно. Потім перевірте Виконання сценаріїв Microsoft у полі Посилання - VBAProject діалогове вікно. Дивіться скріншоти:
2. Потім натисніть кнопку OK щоб закрити діалогове вікно, а потім збережіть і закрийте вікно коду.

Крок 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 для додатків вікна.
2. Натисніть Insert > Модулі, потім скопіюйте та вставте наступний код у вікно модуля.
   Код 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, збережені як текст

Наприклад, у мене є діапазон даних, де ідентифікаційний номер у вихідній таблиці має числовий формат, а ідентифікаційний номер у комірках пошуку зберігається як текст. Ви можете зіткнутися з помилкою #N/A під час використання звичайної функції VLOOKUP. У цьому випадку, щоб отримати правильну інформацію, ви можете обернути функції TEXT і VALUE у функцію VLOOKUP. Нижче наведено формулу для досягнення цього:

Крок 1: Застосуйте та заповніть наступну формулу

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

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

Результат:

Тепер ви отримаєте правильні результати, як показано на знімку екрана нижче: