Note: The other languages of the website are Google-translated. Back to English
Увійти  \/ 
x
or
x
Реєстрація  \/ 
x

or

Функція VLOOKUP з деякими основними та вдосконаленими прикладами в Excel

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

Зміст:

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

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

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

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

5. Завантажте зразки файлів VLOOKUP


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

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

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

аргументи:

Пошук_значення: Значення, яке потрібно шукати. Він повинен бути в першому стовпці діапазону table_array.

Масив_таблиці: Діапазон даних або таблиця, де знаходяться стовпець значення пошуку та стовпець значення результату.

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

Range_lookup: Логічне значення, яке визначає, чи ця функція VLOOKUP поверне точну відповідність або приблизну відповідність.

  • Приблизний збіг - 1 / ІСТИНА: Якщо точного збігу не знайдено, формула здійснює пошук найближчого збігу - найбільшого значення, яке менше значення підстановки. У цьому випадку слід сортувати стовпець пошуку за зростанням.
    = VLOOKUP (пошук_значення, масив_ таблиці, col_index, TRUE)
    = VLOOKUP (пошук_значення, масив_ таблиці, col_index, 1)
  • Точна відповідність - 0 / НЕВІРНО: Це використовується для пошуку значення, яке точно дорівнює значенню підстановки. Якщо точного збігу не знайдено, буде повернуто значення помилки # N / A.
    = VLOOKUP (пошук_значення, масив_ таблиці, col_index, FALSE)
    = VLOOKUP (пошук_значення, масив_ таблиці, col_index, 0)

Примітки:

  • 1. Функція Vlookup шукає значення лише зліва направо.
  • 2. Якщо на основі значення пошуку є кілька відповідних значень, за допомогою функції Vlookup буде повернуто лише перше зіставлене значення.
  • 3. Поверне значення помилки # N / A, якщо значення пошуку не вдається знайти.

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

1. Виконайте точну відповідність Vlookup та приблизну Vlookup

Виконайте точну відповідність Vlookup в Excel

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

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

1. Застосуйте формулу нижче в порожню клітинку, де ви хочете отримати результат:

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

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

Примітки:

  • 1. У наведеній вище формулі F2 - значення, якому потрібно повернути відповідне значення, A2: D7 - масив таблиці, число 3 - це номер стовпця, з якого повертається відповідне значення, і ПОМИЛКОВИЙ відноситься до точної відповідності.
  • 2. Якщо значення критерію не знайдено в діапазоні даних, відображатиметься значення помилки # N / A.

Зробіть приблизний збіг Vlookup в Excel

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

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

1. Введіть таку формулу в клітинку, куди потрібно поставити результат:

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

2. Потім перетягніть маркер заповнення до комірок, щоб застосувати цю формулу, і ви отримаєте приблизні збіги на основі заданих значень, див. Знімок екрана:

Примітки:

  • 1. У наведеній вище формулі D2 - значення, яке потрібно повернути відносною інформацією, A2: B9 - діапазон даних, число 2 вказує номер стовпця, що повертається відповідне значення, і ІСТИНА відноситься до приблизного збігу.
  • 2. Приблизна відповідність поверне найбільше значення, яке менше, ніж ваше конкретне значення пошуку.
  • 3. Щоб скористатися функцією Vlookup для отримання приблизного значення збігу, потрібно відсортувати крайній лівий стовпець діапазону даних у порядку зростання, інакше це поверне неправильний результат.

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

За замовчуванням функція Vlookup виконує пошук без урахування регістру, що означає, що вона розглядає символи малої та великої літер як однакові. Іноді вам може знадобитися виконати пошук з урахуванням регістру в Excel, функції Index, Match та Exact або функції Lookup and Exact можуть зробити вам послугу.

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

Формула 1: Використання функцій EXACT, INDEX, MATCH

1. Введіть або скопіюйте формулу масиву нижче в порожню комірку, де ви хочете отримати результат:

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

2. Потім натисніть Ctrl + Shift + Enter клавіші одночасно, щоб отримати перший результат, а потім виберіть комірку формули, перетягніть маркер заповнення вниз до комірок, які ви хочете заповнити цією формулою, тоді ви отримаєте потрібні результати. Дивіться знімок екрана:

Примітки:

  • 1. У наведеній вище формулі A2: A10 - стовпець, що містить конкретні значення, які ви хочете шукати, F2 - значення пошуку, C2: C10 - стовпець, з якого буде повернено результат.
  • 2. Якщо знайдено кілька збігів, ця формула завжди поверне перший збіг.

Формула 2: Використання функцій Lookup та Exact

1. Будь ласка, застосуйте формулу нижче в порожню клітинку, де ви хочете отримати результат:

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

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

Примітки:

  • 1. У наведеній вище формулі A2: A10 - стовпець, що містить конкретні значення, які ви хочете шукати, F2 - значення пошуку, C2: C10 - стовпець, з якого буде повернено результат.
  • 2. Якщо знайдено кілька збігів, ця формула завжди поверне останній збіг.

3. Значення перегляду справа наліво в Excel

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

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


4. Перегляньте друге, n-те або останнє значення, що збігається в Excel

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

Vlookup і поверніть друге або n-те відповідне значення

Припустимо, у вас є список імен у стовпці A, навчальний курс, який вони придбали у стовпці B, і тепер ви шукаєте 2-й або n-й навчальний курс, придбаний даним замовником. Дивіться знімок екрана:

1. Щоб отримати друге або n-те відповідне значення на основі заданих критеріїв, застосуйте таку формулу масиву до порожньої комірки:

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

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

Примітка:

  • У цій формулі A2: A14 - діапазон з усіма значеннями для пошуку, B2: B14 - діапазон відповідних значень, з яких потрібно повернутися, E2 - значення пошуку та останнє число 2 вказує друге відповідне значення, яке ви хочете отримати; якщо ви хочете повернути третє відповідне значення, вам просто потрібно змінити його на 3, як вам потрібно.

Перегляд і повернення останнього відповідного значення

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


5. Перегляд відповідних значень між двома заданими значеннями або датами

Іноді вам може знадобитися шукати значення між двома значеннями або датами та повернути відповідні результати, як показано на знімку екрана нижче, у цьому випадку ви можете скористатися функцією ПЕРЕГЛЯД та відсортованою таблицею.

Перегляд відповідності значень між двома заданими значеннями або датами за формулою

1. По-перше, у вихідній таблиці має бути відсортований діапазон даних. А потім скопіюйте або введіть таку формулу в порожню комірку:

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

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

Примітки:

  • 1. У наведеній вище формулі A2: A6 - діапазон менших значень і B2: B6 - діапазон більших чисел у вашому діапазоні даних, E2 - задане значення, яке ви хочете отримати, що відповідає йому, C2: C6 - це дані стовпця, з яких ви хочете витягти.
  • 2. Ця формула також може бути використана для вилучення відповідних значень між двома датами, як показано нижче:

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

Якщо вам боляче з наведеною вище формулою, тут я представив простий інструмент - Kutools для Excel, З його ПЕРЕГЛЯД між двома значеннями Ви можете повернути відповідний елемент на основі конкретного значення або дати між двома значеннями або датами, не запам'ятовуючи жодної формули.   Натисніть, щоб завантажити Kutools для Excel зараз!


6. Використання підстановних знаків для часткових збігів у функції Vlookup

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

Припустимо, у мене є ряд даних, як показано на знімку екрана, тепер я хочу витягнути оцінку на основі власного імені (не повного імені). Як можна вирішити це завдання в Excel?

1. Звичайна функція Vlookup працює некоректно, вам потрібно з’єднати посилання на текст або клітинку підстановкою, будь ласка, скопіюйте або введіть наступну формулу в порожню комірку:

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

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

Примітки:

  • 1. У наведеній вище формулі E2 & ”*” - значення пошуку, значення в E2 і * узагальнюючий знак ("*" позначає будь-який один символ або будь-які символи), A2: C11 - діапазон пошуку, число 3 стовпець, що містить значення, яке потрібно повернути.
  • 2. Vlookup під час використання символів підстановки, ви повинні встановити режим точного збігу з FALSE або 0 для останнього аргументу у функції Vlookup.

Порада:

1. Знайдіть і поверніть відповідні значення, що закінчуються конкретним значенням, застосуйте цю формулу: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. Для пошуку та повернення відповідного значення на основі частини текстового рядка, незалежно від того, чи вказаний текст знаходиться спереду, позаду чи посередині текстового рядка, вам просто потрібно об’єднати два символи * навколо посилання на клітинку або тексту. Будь ласка, виконайте цю формулу: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7. Значення перегляду з іншого робочого аркуша

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

Наприклад, у вас є два аркуші, як показано на знімку екрана, для пошуку та повернення відповідних даних із зазначеного вами аркуша, виконайте такі дії:

1. Введіть або скопіюйте формулу нижче в порожню клітинку, де ви хочете отримати відповідні елементи:

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

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

Примітка: У наведеній вище формулі:

  • A2 представляє значення пошуку;
  • специфікація - це ім'я робочого аркуша, з якого ви хочете шукати дані, (Якщо ім'я аркуша містить пробіли або розділові символи, слід вкласти одинарні лапки навколо імені аркуша, інакше ви можете безпосередньо використовувати ім'я аркуша, наприклад = VLOOKUP (A2, Таблиця даних! $ A $ 2: $ C $ 15,3,0));
  • A2: C15 - діапазон даних у таблиці даних, де ми шукаємо дані;
  • число 3 номер стовпця, який містить відповідні дані, з яких потрібно повернутися.

8. Значення перегляду з іншої книги

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

Наприклад, перша книжка містить списки товарів і витрат, тепер потрібно витягнути відповідну вартість у другій книзі на основі товару, як показано на знімку екрана.

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

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

2. Потім перетягніть та скопіюйте цю формулу в інші потрібні комірки, див. Знімок екрана:

Примітки:

  • 1. У наведеній вище формулі:
    B2 представляє значення пошуку;
    [Список товарів.xlsx] Аркуш1 - це назва книги та аркуша, з якого ви хочете шукати дані, (посилання на книгу укладено у квадратні дужки, а вся книга + аркуш - у одинарні лапки);
    A2: B6 - діапазон даних на аркуші іншої книги, де ми шукаємо дані;
    число 2 номер стовпця, який містить відповідні дані, з яких потрібно повернутися.
  • 2. Якщо книга пошуку буде закрита, повний шлях до файлу книги пошуку буде показаний у формулі, як показано на наступному знімку екрана:

9. Перегляд і повернення порожнього або конкретного тексту замість 0 або # N / A значення помилки

Зазвичай, коли ви застосовуєте функцію vlookup для повернення відповідного значення, якщо ваша відповідна комірка порожня, вона поверне 0, а якщо ваше відповідне значення не знайдено, ви отримаєте значення помилки # N / A, як показано на знімку екрана нижче. Замість того, щоб відображати значення 0 або # N / A із порожньою коміркою або іншим вподобаним значенням, це Vlookup для повернення порожнього або конкретного значення замість 0 або N / A підручник може зробити вам послугу покроково.


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

1. Двосторонній пошук із функцією Vlookup (Vlookup у рядку та стовпці)

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

Формула 1: Використання функцій VLOOKUP та MATCH

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

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

Примітка: У наведеній вище формулі:

  • H1: значення пошуку в стовпці, на основі якого ви хочете отримати відповідне значення;
  • A2: E6: діапазон даних, включаючи заголовки рядків;
  • H2: значення підстановки в рядку, на основі якого потрібно отримати відповідне значення;
  • A1: E1: комірки заголовків стовпців.

Формула 2: Використання функцій INDEX та MATCH

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

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

Примітка: У наведеній вище формулі:

  • В2: Е6: діапазон даних, з якого повертається відповідний елемент;
  • H1: значення пошуку в стовпці, на основі якого ви хочете отримати відповідне значення;
  • A2: A6: заголовки рядків містять товар, який потрібно шукати.
  • H2: значення підстановки в рядку, на основі якого потрібно отримати відповідне значення;
  • В1: Е1: заголовки стовпців містять квартал, який потрібно шукати.

2. Значення відповідності Vlookup на основі двох або більше критеріїв

Вам легко знайти відповідне значення на основі одного критерію, але якщо у вас є два або більше критеріїв, що ви можете зробити? Функції LOOKUP або MATCH та INDEX в Excel можуть допомогти вам швидко та легко вирішити цю роботу.

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

Формула 1: Використання функції LOOKUP

Будь ласка, застосуйте формулу нижче в клітинку, де ви хочете отримати результат, а потім натисніть клавішу Enter, див.

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

Примітки:

  • 1. У наведеній вище формулі:
    A2: A12 = G1: означає пошук критеріїв G1 в діапазоні A2: A12;
    B2: B12 = G2: означає пошук критеріїв G2 в діапазоні B2: B12;
    D2: D12: діапазон, якому потрібно повернути відповідне значення.
  • 2. Якщо у вас більше двох критеріїв, вам просто потрібно об’єднати інші критерії у формулу, наприклад: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

Формула 2: Використання функцій INDEXT AND MATCH

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

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

Потім натисніть клавіші Ctrl + Shift + Enter, щоб отримати відносне значення, яке вам потрібно. Дивіться знімок екрана:

Примітки:

  • 1. У наведеній вище формулі:
    A2: A12 = G1: означає пошук критеріїв G1 в діапазоні A2: A12;
    B2: B12 = G2: означає пошук критеріїв G2 в діапазоні B2: B12;
    D2: D12: діапазон, якому потрібно повернути відповідне значення.
  • 2. Якщо у вас більше двох критеріїв, вам просто потрібно об’єднати нові критерії у формулу, наприклад: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. Vlookup для повернення декількох відповідних значень з однією або кількома умовами

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

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

Переглянути всі відповідні значення на основі однієї умови по горизонталі:

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

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
примітки: m - номер рядка першої комірки в діапазоні повернення мінус 1.
      n - номер стовпця першої комірки формули мінус 1.

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

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. А потім виділіть першу комірку формули та перетягніть маркер заповнення до правої комірки, поки не відобразиться порожня комірка, і всі відповідні елементи будуть витягнуті, див. Знімок екрана:

Порада:

Якщо у повернутому списку є повторювані значення, що відповідають, щоб проігнорувати дублікати, скористайтеся цими формулами, а потім натисніть вводити щоб отримати перший результат: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Продовжуйте вводити цю формулу: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") в клітинку біля першого результату, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати другий результат, а потім перетягніть цю формулу в праві клітинки, щоб отримати всі інші зіставлені значення, поки не відображається порожня комірка, див. скріншот:


Перегляньте всі відповідні значення на основі двох або більше умов горизонтально:

Для Vlookup і повернення всіх відповідних значень на основі більш конкретних значень по горизонталі, загальна формула має вигляд:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
примітки: m - номер рядка першої комірки в діапазоні повернення мінус 1.
      n - номер стовпця першої комірки формули мінус 1.

1. Застосуйте наступну формулу до порожньої комірки, де потрібно вивести результат:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Потім виділіть комірку формули та перетягніть маркер заповнення до правої комірки, поки не з’явиться пуста комірка, і всі відповідні значення на основі конкретних критеріїв будуть повернуті, див. Знімок екрана:

примітки: Щоб отримати більше критеріїв, вам просто потрібно приєднати lookup_value та lookup_range до формули, наприклад: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


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

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

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

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
примітки: m - номер рядка першої комірки в діапазоні повернення мінус 1.
      n - номер рядка першої комірки формули мінус 1.

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

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Потім виділіть першу комірку формули та перетягніть маркер заповнення вниз до інших комірок, поки не з’явиться пуста комірка, і всі відповідні елементи будуть перераховані в стовпці, див. Знімок екрана:

Порада:

Щоб ігнорувати дублікати у повернутих відповідних значеннях, використовуйте наступні формули: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

Потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати перше знайдене значення, а потім перетягніть цю комірку формули вниз до інших комірок, поки не з’явиться порожня комірка, і ви отримаєте результат, як вам потрібно:


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

Для Vlookup і повернення всіх відповідних значень на основі більш конкретних значень по вертикалі, загальна формула має вигляд:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
примітки: m - номер рядка першої комірки в діапазоні повернення мінус 1.
      n - номер рядка першої комірки формули мінус 1.

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

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

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

примітки: Щоб отримати більше критеріїв, вам просто потрібно приєднати lookup_value та lookup_range до формули, наприклад: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


Перегляньте всі відповідні значення на основі двох або більше умов в одну комірку

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

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

Будь ласка, застосуйте наведену нижче просту формулу до порожньої комірки, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати результат:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Порада:

Щоб ігнорувати дублікати у повернутих відповідних значеннях, використовуйте наступні формули: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


Перегляньте всі відповідні значення на основі двох або більше умов в одну комірку:

Для роботи з кількома умовами при поверненні всіх відповідних значень в одну клітинку застосуйте формулу нижче, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати результат:

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Примітки:

1. Функція TEXTJOIN доступна лише в Excel 2019 та Office 365.

2. Якщо ви використовуєте Excel 2016 та попередні версії, будь ласка, використовуйте визначену користувачем функцію наведених нижче статей:


4. Vlookup для повернення цілого або цілого рядка збіжної комірки

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

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

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

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

примітки: У наведеній вище формулі, F2 - значення підстановки, за яким потрібно повернути весь рядок, A1: D12 діапазон даних, який ви хочете використовувати, A1 вказує номер першого стовпця в межах вашого діапазону даних.

Порада:

Якщо на основі відповідного значення знайдено кілька рядків, щоб повернути всі відповідні рядки, застосуйте цю формулу: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),""), а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати перший результат, а потім перетягніть маркер заповнення прямо до комірок, див. знімок екрана:

А потім перетягніть маркер заповнення вниз по комірках, щоб отримати всі відповідні рядки, як показано на знімку екрана:


5. Виконайте кілька функцій Vlookup (вкладені Vlookup) в Excel

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

Загальна формула для вкладеної функції Vlookup:

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

Примітка:

  • lookup_value: значення, яке ви шукаєте;
  • Таблиця1, Таблиця2, Таблиця3, ...: таблиці, в яких існують значення пошуку та повернене значення;
  • з: номер стовпця в таблиці, з якого потрібно повернути відповідне значення.
  • 0: Використовується для точного збігу.

1. Будь ласка, застосуйте наступну формулу до порожньої комірки, куди ви хочете поставити результат:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

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

Примітки:

  • 1. У наведеній вище формулі J3 значення, яке ви шукаєте; A3: B7, D3: E7, G3: H7 - діапазони таблиць, у яких існують значення пошуку та повернене значення; Кількість 2 - номер стовпця в діапазоні, з якого повертається відповідне значення.
  • 2. Якщо значення пошуку не вдається знайти, відображається значення помилки. Щоб замінити помилку читабельним текстом, скористайтеся такою формулою: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. Vlookup, щоб перевірити, чи існує значення на основі даних списку в іншому стовпці

Функція Vlookup також може допомогти вам перевірити, чи існують значення на основі іншого списку, наприклад, якщо ви хочете шукати імена в стовпці С і просто повернути Так чи Ні, якщо ім'я знайдено чи ні в стовпці А, як показано на знімку екрана показано.

1. Будь ласка, застосуйте таку формулу до порожньої комірки:

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

2. Потім перетягніть маркер заповнення до комірок, які ви хочете заповнити цією формулою, і ви отримаєте результат, як вам потрібно, див. Знімок екрана:

примітки: У наведеній вище формулі, C2 - значення пошуку, яке потрібно перевірити; A2: A10 - список діапазону, звідки будуть знайдені значення пошуку; Кількість 1 - номер стовпця, звідки ви хочете отримати значення у своєму діапазоні.


7. Перегляньте та підсумуйте всі відповідні значення в рядках або стовпцях

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

Перегляньте та підсумуйте всі збігаються значення в рядку або декількох рядках

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

Перегляньте та підсумуйте перші співпадаючі значення підряд:

1. Скопіюйте або введіть наступну формулу в порожню комірку, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати перший результат.

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

2. Потім перетягніть маркер заповнення вниз, щоб скопіювати цю формулу в інші потрібні комірки, і всі значення в рядку першого відповідного значення були підсумовані, див. Знімок екрана:

примітки: У наведеній вище формулі: H2 - комірка, що містить значення, яке ви шукаєте; A2: F9 - діапазон даних (без заголовків стовпців), що включає значення пошуку та відповідні значення; Кількість 2,3,4,5,6 {} - номери стовпців, що використовуються для обчислення загальної кількості діапазону.


Перегляньте та підсумуйте всі знайдені значення у декількох рядках:

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

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

примітки: У наведеній вище формулі: H2 - значення пошуку, яке ви шукаєте; A2: A9 - заголовки рядків, що містять значення пошуку; B2: F9 діапазон даних числових значень, які потрібно підсумувати.


Перегляньте та підсумуйте всі знайдені значення у стовпці або декількох стовпцях

Перегляньте та підсумуйте перші знайдені значення у стовпці:

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

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

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

примітки: У наведеній вище формулі: H2 - значення пошуку, яке ви шукаєте; B1: F1 - заголовки стовпців, що містять значення пошуку; B2: F9 діапазон даних числових значень, які потрібно підсумувати.


Перегляньте та підсумуйте всі знайдені значення у кількох стовпцях:

Для перегляду та підсумовування всіх відповідних значень у декількох стовпцях слід скористатися такою формулою:

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

примітки: У наведеній вище формулі: H2 - значення пошуку, яке ви шукаєте; B1: F1 - заголовки стовпців, що містять значення пошуку; B2: F9 діапазон даних числових значень, які потрібно підсумувати.


Перегляньте та підсумуйте перші зіставлені або всі знайдені значення з потужною функцією

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


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

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

Будь ласка, застосуйте наступну формулу до комірки, а потім натисніть клавішу Enter, щоб отримати результат, див. Знімок екрана:

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

примітки: У наведеній вище формулі: B2: F9 - діапазон даних числових значень, які потрібно підсумувати; B1: F1 is заголовки стовпців містять значення підстановки, яке потрібно підсумувати; I2 - значення пошуку в заголовках стовпців, який ви шукаєте; A2: A9 - заголовки рядків містять значення підстановки, яке потрібно підсумувати; H2 - це значення пошуку в заголовках рядків, які ви шукаєте.


8. Vlookup для об’єднання двох таблиць на основі одного або декількох стовпців ключів

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

Vlookup для об’єднання двох таблиць на основі одного стовпця ключів

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

Формула 1: Використання функції VLOOKUP

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

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

примітки: У наведеній вище формулі, A2 значення, яке ви шукаєте, E2: F8 - таблиця для пошуку, номер 2 - номер стовпця в таблиці, з якого потрібно отримати значення.

Формула 2: Використання функцій INDEX та MATCH

Якщо ваші загальні дані в правій частині та повернені дані в лівому стовпці в другій таблиці, для об'єднання стовпця замовлення, функція Vlookup не може виконати роботу. Щоб шукати праворуч ліворуч, ви можете використовувати функції INDEX та MATCH для заміни функції Vlookup.

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

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

примітки: У наведеній вище формулі, A2 - значення пошуку, яке ви шукаєте, E2: E8 - діапазон даних, які потрібно повернути, F2: F8 - діапазон пошуку, який містить значення пошуку.


Vlookup для об’єднання двох таблиць на основі кількох стовпців ключів

Якщо дві таблиці, до яких ви хочете приєднатися, мають кілька ключових стовпців, для об’єднання таблиць на основі цих загальних стовпців вам можуть допомогти функції INDEX та MATCH.

Загальна формула для об’єднання двох таблиць на основі кількох стовпців ключів:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

1. Будь ласка, застосуйте наведену нижче формулу до порожньої комірки, куди ви хочете ввести результат, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати перше знайдене значення, див. знімок екрана:

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

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

2Потім виберіть першу комірку формули та перетягніть маркер заповнення, щоб скопіювати цю формулу в інші комірки, як вам потрібно:

чайові: У програмі Excel 2016 та пізніших версіях ви також можете використовувати Power Query функція для об’єднання двох або більше таблиць в одну на основі ключових стовпців. Клацніть, щоб дізнатися деталі поетапно.

9. Перевірка відповідних значень на кількох робочих аркушах

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


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

1. Vlookup, щоб отримати форматування комірки (колір комірки, колір шрифту) разом із значенням пошуку

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

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

1. На аркуші містяться дані, які потрібно переглянути, клацніть правою кнопкою миші вкладку аркуша та виберіть Переглянути код з контекстного меню. Дивіться знімок екрана:

2. У відкритому Microsoft Visual Basic для додатків вікно, скопіюйте нижче код VBA у вікно коду.

Код VBA 1: Vlookup для отримання форматування комірки разом із значенням пошуку

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

3. Досі в Microsoft Visual Basic для додатків вікна, натисніть Insert > Модулі, а потім скопіюйте наведений нижче код VBA 2 у вікно модуля.

Код VBA 2: Vlookup для отримання форматування комірки разом із значенням пошуку

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. Вставивши вищевказані коди, натисніть інструменти > посилання в Microsoft Visual Basic для додатків вікно. Потім перевірте Виконання сценарію Microsoft у полі Посилання - VBAProject діалогове вікно. Дивіться скріншоти:

5. Потім натисніть кнопку OK щоб закрити діалогове вікно, а потім зберегти та закрити вікно коду, поверніться назад до робочого аркуша та застосуйте таку формулу: =LookupKeepFormat(E2,$A$1:$C$10,3) у порожню комірку, де потрібно вивести результат, а потім натисніть клавішу Enter. Дивіться знімок екрана:

примітки: У наведеній вище формулі, E2 це значення, яке ви будете шукати, A1: C10 - діапазон таблиці та номер 3 - номер стовпця таблиці, для якої потрібно повернути відповідне значення.

6. Потім виділіть першу комірку результату та перетягніть маркер заповнення вниз, щоб отримати всі результати разом із їх форматуванням. Дивіться знімок екрана.


2. Зберігайте формат дати з повернутого значення Vlookup

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

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

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

примітки: У наведеній вище формулі, E2 значення зовнішнього вигляду, A2: C9 - діапазон пошуку, число 3 номер стовпця, для якого потрібно повернути значення, мм / дд / рррр - формат дати, який ви хочете зберегти.


3. Перегляд і повернення відповідного значення з коментарем комірки

Ви коли-небудь пробували Vlookup повертати не тільки відповідні дані комірки, але й коментар комірки, а також в Excel, як показано на наступному знімку екрана? Щоб вирішити це завдання, нижче визначена користувачем функція може зробити вам послугу.

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. Потім збережіть і закрийте вікно коду, введіть цю формулу: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) в порожню комірку, щоб знайти результат, а потім перетягніть маркер заповнення, щоб скопіювати цю формулу в інші комірки, тепер відповідні значення, а також коментарі повертаються відразу, див. знімок екрана:

примітки: У наведеній вище формулі, D2 - значення підстановки, якому потрібно повернути відповідне значення, A2: B9 - це таблиця даних, яку ви хочете використовувати, номер 2 - номер стовпця, який містить відповідне значення, яке потрібно повернути.


4. Розбирайтеся з текстом та дійсними числами у Vlookup

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

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

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

Примітки:

  • 1. У наведеній вище формулі D2 - значення підстановки, якому потрібно повернути відповідне значення, A2: B8 - це таблиця даних, яку ви хочете використовувати, номер 2 - номер стовпця, який містить відповідне значення, яке потрібно повернути.
  • 2. Ця формула також добре працює, якщо ви не впевнені, де у вас цифри, а де текст.

Завантажте зразки файлів VLOOKUP

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці та Ведення даних; Вміст розділених комірок; Поєднуйте повторювані рядки та суму / середнє... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Улюблені та швидко вставлені формули, Діапазони, діаграми та зображення; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Групування зведеної таблиці за номер тижня, день тижня та багато іншого ... Показати розблоковані, заблоковані клітини за різними кольорами; Виділіть клітини, які мають формулу / назву...
вкладка kte 201905
  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Anton · 1 months ago
    Здравствуйте, возможно ли сравнить любое значение из столбцов в таблице. Суть в том, что значения сделаны неправильно и есть слова "CO" "BE" как сделанные на английском так и на русском, чтобы каждый раз не делать замену их неудобно, т.к. файл обновляется постоянно. Необходимо чтобы Впр искал любой вариант из первого и второго столбца. 1 столбец русские символы, 2-ой английские.