Note: The other languages of the website are Google-translated. Back to English

Як vlookup знайти перше, друге або n-те значення відповідності в Excel?

Припустимо, у вас є дві колонки з продуктами та кількістю, як показано на знімку екрана нижче. Що б ви зробили для швидкого з’ясування кількості першого чи другого банана?

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

Vlookup знаходить перше, друге або n-те значення відповідності в Excel за формулою

Легко шукати перше значення відповідності в Excel за допомогою Kutools для Excel


Vlookup знаходить перше, друге або n-те значення відповідності в Excel

Будь ласка, виконайте наступні дії, щоб знайти перше, друге або n-те значення відповідності в Excel.

1. У комірку D1 введіть критерії, які ви хочете переглянути, тут я вводжу Банан.

2. Тут ми знайдемо значення першого збігу банана. Виділіть порожню комірку, наприклад E2, скопіюйте та вставте формулу =INDEX($B$2:$B$6,MATCH(TRUE,EXACT($D$1,$A$2:$A$6),0)) в панель формул, а потім натисніть Ctrl + Shift + вводити клавіші одночасно.

примітки: У цій формулі $ B $ 2: $ B $ 6 - діапазон значень, що збігаються; $ A $ 2: $ A $ 6 - це діапазон з усіма критеріями для пошуку; $ D $ 1 - це комірка, що містить зазначені критерії пошуку.

Тоді ви отримаєте перше співпадаюче значення банана в комірці E2. За допомогою цієї формули ви можете отримати лише перше відповідне значення на основі ваших критеріїв.

Щоб отримати будь-які n-ті відносні значення, ви можете застосувати таку формулу: =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1)) + Ctrl + Shift + вводити ключі разом, ця формула поверне перше знайдене значення.

примітки:

1. Щоб знайти друге значення відповідності, змініть наведену вище формулу на =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),2)), а потім натисніть Ctrl + Shift + вводити клавіші одночасно. Дивіться знімок екрана:

2. Останнє число у наведеній вище формулі означає n-те значення збігу критеріїв vlookup. Якщо ви зміните його на 3, він отримає третє значення збігу, і зміниться на n, буде виявлено n-те значення збігу.


Vlookup знаходить перше значення відповідності в Excel за допомогою Kutools для Excel

YВи можете легко знайти перше значення відповідності в Excel, не запам'ятовуючи формул за допомогою Шукайте значення у списку формула формула Kutools для Excel.

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

1. Виберіть комірку для пошуку першого відповідного значення (каже комірка E2), а потім клацніть Кутулс > Помічник формули > Помічник формули. Дивіться знімок екрана:

3 В Помічник формули діалогове вікно, будь ласка, налаштуйте наступним чином:

  • 3.1 У Виберіть формулу знайдіть і виберіть Шукайте значення у списку;
    чайові: Ви можете перевірити фільтр поле, введіть певне слово в текстове поле, щоб швидко відфільтрувати формулу.
  • 3.2 У Масив_таблиці , виберіть таблиця, яка містить перші значення відповідних значень.;
  • 3.2 У Пошук_значення виберіть клітинку, яка містить Критерії ви повернете перше значення на основі;
  • 3.3 У Колонка вкажіть стовпець, з якого ви повернете відповідне значення. Або ви можете ввести номер стовпця в текстове поле безпосередньо, як вам потрібно.
  • 3.4 Натисніть кнопку OK кнопку. Дивіться знімок екрана:

Тепер відповідне значення комірки буде автоматично заповнене в комірку С10 на основі вибору зі спадного списку.

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


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

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

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2021 і 365. Підтримує всі мови. Легке розгортання на вашому підприємстві чи в організації. 30-денна безкоштовна пробна версія повних функцій. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (43)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Привіт. Чи можете ви надати відео для вищезгаданої формули, щоб отримати 2-е, 3-е значення даних
Цей коментар був мінімізований модератором на сайті
Дуже дякую!!!
Цей коментар був мінімізований модератором на сайті
Що, якщо банан може бути жовтим або зеленим, як ми можемо використовувати цю формулу, щоб показати потрібну кількість на основі двох значень (замість одного тільки зараз)? Спасибі за вашу допомогу!
Цей коментар був мінімізований модератором на сайті
Якщо результат значення #NUM! не могли б ви показати мені формулу для додавання, щоб вона повернулася до НУЛЬОВОГО результату. Дякую
Цей коментар був мінімізований модератором на сайті
Просто додайте IFERROR(ваша формула, результат, який ви хочете повернути), наприклад, формула =сума(A1:A6), тоді вона перетвориться на =IFERROR(сума(A1:A6),""), вона буде повертайте пусте, якщо результатом є помилка, наприклад #NUM!.
Цей коментар був мінімізований модератором на сайті
Допоможіть мені знайти максимальне значення Bananaa за допомогою формули. Тобто відображати 300
Цей коментар був мінімізований модератором на сайті
Як знайти максимальне значення Bananaa
Цей коментар був мінімізований модератором на сайті
=Sumproduct(((A2:A6)=D1)*((B2:B6)=Max(B2:B6))*(B2:B6))
Цей коментар був мінімізований модератором на сайті
[quote]=Sumproduct(((A2:A6)=D1)*((B2:B6)=Max(B2:B6))*(B2:B6))Від Ферді[/quote] Я ціную вашу допомогу FERDHY. Я спробував формулу, але оскільки max(B2:B6) дорівнює 500 (помаранчевий), отримане значення дорівнює 0.
Цей коментар був мінімізований модератором на сайті
Привіт, просто використовуйте це =SUMPRODUCT(MAX(((A2:A8)=D1)*(B2:B8))) як тільки ви зміните в D1 і помістите Banana, ви повинні отримати 300 , якщо ви поставите Orange, ви отримаєте 500 Фердхі
Цей коментар був мінімізований модератором на сайті
Ви також можете використовувати:
=макс(якщо(A2:A6=D1, B2:B6)) + Ctrl + Shift + Enter
Цей коментар був мінімізований модератором на сайті
Як я можу автоматично збільшити останнє число під час перетягування формули вниз: =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6) - РЯДК($A$2)+1),2)),
Цей коментар був мінімізований модератором на сайті
Шановний Warthogb,

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



=IFERROR(ІНДЕКС($B$2:$B$7,МАЛИЙ(ЯКЩО($D$1=$A$2:$A$7,РЯДОК($A$2:$A$7)-РОВ($A$2)+1), 1+(ROW(A1)-1))), "") + Ctrl + Shift + Enter
Цей коментар був мінімізований модератором на сайті
Crystal, велике спасибі, я мав можливість побачити вашу допомогу лише сьогодні 27/8/48, зроблю формулу пізніше сьогодні :)
Цей коментар був мінімізований модератором на сайті
привіт,
Я застосував цю формулу, але в моєму випадку замість назви продукту є цифри. Коли я перетягую формулу вниз, щоб знайти наступне число у списку, я отримую помилку.

обсяг подій непарні події тільки обсяг
1 0.3 1 0.3
1 2.5 1 2.5
2 1.1 3 #ЧИСЛО
2 0.5 3 #ЧИСЛО
3 0
3 0.2
3 1
Цей коментар був мінімізований модератором на сайті
Дорога Еббі,
Функція перетягування вниз може працювати лише для однакових значень vlookup. Але у вашому випадку значення vlookup відрізняються (1 і 3).
Будь ласка, використовуйте цю формулу масиву: =IFERROR(INDEX($B$2:$B$8,SMALL(IF($C$4=$A$2:$A$8,ROW($A$2:$A$8)-ROW($A) $2)+1),1+(ROW(A1)-1))), "") + Ctrl + Shift + Enter і перетягніть формулу вниз, щоб отримати всі відповідні значення в тому самому значенні vlookup, як показано на знімку екрана нижче.
Цей коментар був мінімізований модератором на сайті
Чудовий підручник! Працює як шарм, навіть на кількох аркушах в одному файлі! Велике дякую!!
Цей коментар був мінімізований модератором на сайті
Моя поточна формула: {=IFERROR(INDEX(Sheet3!$C$2:$C$596,SMALL(IF(Sheet3!$A$2:$A$596=Sheet2!A19,ROW(Sheet3!$A$2:$A$596)) -ROW(INDEX(Sheet3!$A$2:$A$596,1,1))+1),P19)),0)} Але як я можу застосувати це до кількох критеріїв, скажімо, двох збігів?
Цей коментар був мінімізований модератором на сайті
Моя проблема схожа
зателефонуйте мені, якщо знайдете рішення
Цей коментар був мінімізований модератором на сайті
створіть допоміжний стовпець, який об’єднує ваші критерії, а потім використовуйте конкатенацію як критерії!

Сподіваюся, це працює!
Цей коментар був мінімізований модератором на сайті
У мене є невелика проблема з цією формулою, вона не працює в моєму випадку:
=ІНДЕКС($B$2:$B$6,МАЛИЙ(ЯКЩО($D$1=$A$2:$A$6,РЯДОК($A$2:$A$6)-РОВ($A$2)+1),1) ) + Ctrl + Shift + Enter

що якщо критерії, які я шукаю, щоразу не збігаються (Банан), а скоріше стають частиною фрази (бананова республіка) тощо; що тоді? Змінюючи число "n" в кінці цієї формули, я отримую "#NUM!" відповідь. У мене є стовпець словникового запасу, значення якого я хочу шукати в другому стовпці, і, ввівши одне слово, мені потрібно отримати всі входження цього слова в будь-яку фразу, яку потрібно перерахувати. Будь-яка допомога з цього приводу?
Спасибі,
RG
Цей коментар був мінімізований модератором на сайті
Чи можна використовувати цю формулу, щоб знайти, чи знаходиться число між двома числами. Нижче наведена моя формула. Спроба перевірити, чи розміщено список із особами та сумою між іншими клітинками набору (приклад: 50,000 74,999 доларів США та XNUMX XNUMX доларів США)


=ArrayFormula(INDEX('4 - Список донорів'!$B$2:$B$1000,SMALL(IF('4 - Список донорів'!$F$2:$F$1000>=D$2,ROW('4 - Список донорів' '!$F$2:$F$1000)-ROW('4 - список донорів'!$F$2)+1),$A6)))
Цей коментар був мінімізований модератором на сайті
Шановна пані/пан,

У мене є проблема:
Я знав кількість товару, я хочу дізнатися назву продукту з першим або другим значенням відповідності 200, що б ви зробили?
Велике спасибі!

Сім Ван Наріт
Цей коментар був мінімізований модератором на сайті
Добрий день,
Припустимо, що значення 200 знаходиться в клітинці F2, спробуйте цю формулу: =VLOOKUP(F2,IF({1,0},$B$2:$B$7,$A$2:$A$7),2,0).
Сподіваюся, це може допомогти. Дякую за коментар.
Цей коментар був мінімізований модератором на сайті
якщо члена було викликано 1 жовтня (набір даних за жовтень), і він не був відновлений, CCE знову зателефонував йому 15 листопада (набір даних листопада). Член відновлено 16 листопада. Під час перевірки пожвавлення за допомогою VLookup він робить ТАК для записів за жовтень і листопад. Як уникнути подібного, має показувати «ТАК» для запису за листопад фактично, коли він був відновлений, а також залишати жовтневий запис як «НІ».
Цей коментар був мінімізований модератором на сайті
Чи можна знайти середнє значення неунікальних даних. Або можна було б мати спадний список у клітинці з різними значеннями?
Цей коментар був мінімізований модератором на сайті
Хороший день,
На жаль, поки що не можу вам допомогти. Дякуємо за коментар.
Цей коментар був мінімізований модератором на сайті
І якщо ви хочете, щоб останній, другий останній, n-й останній, просто додайте лічильник (підрахуйте кількість подій, які вже відбулися) до кінця і відніміть його відповідно на 0,1,n.

Дуже дякую! Я довго шукав це
Цей коментар був мінімізований модератором на сайті
Хороший день,
На жаль, поки що не можу вам допомогти. Дякуємо за коментар.
Цей коментар був мінімізований модератором на сайті
Якщо перший або будь-який інший запис для стовпця B «банан» був порожнім, для якого я не потребую цього номера, які зміни потрібно внести до цієї формули, щоб пропустити пусту клітинку в стовпці B.
Цей коментар був мінімізований модератором на сайті
Вибачте, що я використовую цю формулу
=INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1))
Цей коментар був мінімізований модератором на сайті
ВИРІШЕНО:
=SMALL(IF(A2:A7=D1,IF(B2:B7<>"",B2:B7)),1)

Якщо потрібен 2-й або 3-й номер, замініть ),1) на 2 або 3

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

Оновлена ​​формула
=INDEX($B$2:$B$7,SMALL(IF($A2:$A6=$D$1,IF($B$2:$B$7<>"",ROW($A2:$A6)-ROW($A2)+1)),1))

Це пропускає порожню клітинку та розміщує значення непустої клітинки. Замініть +1 на +2 або +3 для 2-го або 3-го значення
Цей коментар був мінімізований модератором на сайті
Ідеальне пояснення, дякую.
Цей коментар був мінімізований модератором на сайті
У мене є сценарій... Як отримати останню ціну чого-небудь для довідки... Приклад: перша ціна банана була 200... Під час другої покупки; Мені потрібно відобразити 200 у моїй клітинці очікуваної ціни, а потім, якщо я куплю це в день за 220, я встановлю це значення вручну як 220... Щоразу, коли наступного разу я куплю банан; Мені потрібно відобразити 220 від ціни останньої покупки
Цей коментар був мінімізований модератором на сайті
Try =INDEX($B$2:$B$6,XMATCH(TRUE,EXACT($D$1,$A$2:$A$6),0,-1))

По суті, це змінює порядок пошуку та повертає перший збіг за допомогою функції XMATCH.

Краще пізно, ніж ніколи, сподіваюся, комусь допоможе :)
There are no comments posted here yet
Load More
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL