Функцията VLOOKUP е една от най-популярните функции в Excel. Този урок ще представи как да използвате функцията VLOOKUP в Excel с десетки основни и разширени примери стъпка по стъпка.

Представяне на функцията VLOOKUP – Синтаксис и аргументи

В Excel функцията VLOOKUP е мощна функция за повечето потребители на Excel, тя ви позволява да търсите стойност в най-лявата част на диапазона от данни и да върнете съвпадаща стойност в същия ред от колона, която сте посочили, както е показано на следната екранна снимка .

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

аргументи:

Функционални бележки:

Основни примери за VLOOKUP

В този раздел ще говорим за някои формули на Vlookup, които сте използвали често.

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

 2.1.1 Направете VLOOKUP с точно съвпадение

Обикновено, ако търсите точно съвпадение с функцията VLOOKUP, просто трябва да използвате FALSE като последен аргумент.

Например, за да получите съответните резултати по математика въз основа на конкретните ID номера, моля, направете следното:

Моля, копирайте и поставете формулата по-долу в празна клетка (тук избирам G2) и натиснете Въведете ключ за получаване на резултата:

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

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

Първо, той търси ID - 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 и да върнете последната съответстваща стойност, както е показано на екранната снимка по-долу, това 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. Кликнете 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 за връщане на празна или конкретна стойност вместо 0 или N/A урокът може да ви направи услуга.

Разширени примери за 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. Кликнете Kutools > Супер ТЪРСЕНЕ > Търсене на множество условия за да активирате тази функция.
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. Кликнете Kutools > Супер ТЪРСЕНЕ > ТЪРСЕНЕ и Сумиране за да активирате тази функция.
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 в модула Sheet Code

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 за приложения прозорец, кликнете Поставете > Модулии след това копирайте долния 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 Scripting Runtime в квадратчето Препратки – 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. Кликнете Поставете > Модули, след това копирайте и поставете следния код в прозореца на модула.
   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))

Резултат:

Сега ще получите правилните резултати, както е показано на екранната снимка по-долу: