Excel Альтернативи VLOOKUP (Зміст)

  • Альтернативи VLOOKUP
  • Приклади функцій INDEX та MATCH з обмеженням VLOOKUP
  • Як використовувати альтернативи VLOOKUP в Excel?

Альтернативи VLOOKUP

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

Однак, якщо ви вважаєте, що знання формули VLOOKUP - це найкраща річ у excel, ви повинні негайно змінити свою думку (я відчув, що VLOOKUP - це найвища річ у першоклассі на початку моєї кар'єри).

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

Однак це ще не кінець, оскільки у нас є мало альтернатив, щоб подолати обмеження VLOOKUP у excel.

Приклади функцій INDEX та MATCH з обмеженням VLOOKUP

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

Формула INDEX:

  • Масив: Яке значення масиву ви хочете отримати?
  • Номер рядка : з якого номера рядка ви намагаєтеся отримати дані.
  • Число стовпців: з якого номера стовпця ви намагаєтеся отримати дані.

Функція індексу - приклад №1

Розглянемо деякі приклади альтернатив Excel для VLOOKUP.

Ви можете завантажити цю альтернативу шаблону Excel Шаблон VLOOKUP тут - Альтернативи шаблону Excel VLOOKUP

У мене проста таблиця продажів, орієнтована на зони.

Я можу отримати дані за допомогою VLOOKUP, але тут я буду використовувати формулу INDEX. Відкрийте формулу в комірці E2.

Тепер виберіть необхідні значення стовпців результатів. Тут мої необхідні значення знаходяться в межах від B2 до B5. Я виберу діапазон як B2: B5 і зафіксую його.

Тепер мені потрібно згадати номер рядка. Для регіону Південь номер ряду - 2.

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

INDEX отримає мені дані для Південної області.

Ого!!! INDEX зробив для мене трюк.

Однак проблема почнеться зараз. Коли я копіюю і вставляю формулу в комірки нижче, мені потрібно змінити номер рядка, коли клітина змінюється. У поточному номері рядка комірки дорівнює 1, а коли я перейшов до наступної комірки, він повинен бути 2. INDEX не приймає додаткове число рядка автоматично.

Ми можемо автоматизувати це завдання за допомогою функції MATCH.

Функція МАТЧ - Приклад №2

Він поверне номер рядка вибраного значення у наданому діапазоні.

  • Значення пошуку: яке значення пошуку ви намагаєтеся знайти номер рядка?
  • Масив пошуку: з якого діапазону ви намагаєтеся знайти номер рядка.
  • Тип відповідності: який результат ви хочете шукати. Будь то приблизна відповідність нижче значення пошуку (1) або Точна відповідність (0) або приблизна відповідність вище значення пошуку (-1).

Зараз я буду використовувати ті самі дані з наведеного вище прикладу. Я спробую переглянути номер рядка для кожного регіону.

Відкрийте формулу в комірці E2 і виберіть значення пошуку як значення комірки D2.

Тепер масив пошуку - це з якої таблиці ви намагаєтесь знати номер рядка. Наша таблиця масиву - від A2 до A5.

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

Я отримав номери рядків для кожного регіону.

Перетягніть формулу до залишків комірок.

Обмеження VLOOKUP - приклад №3

VLOOKUP завжди працює зліва направо. Тепер подивіться нижче наведені дані.

Мені потрібно витягнути дані про зарплату з діапазону від А1 до Д21. Я можу це зробити, застосувавши функцію VLOOKUP.

Результат буде наступним:

Перетягніть формулу до залишків комірок.

На даний момент VLOOKUP робить роботу для мене. Але тепер подивіться на нижню структуру даних.

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

Тож у нас є чудова альтернатива VLOOKUP під назвою функція INDEX & MATCH.

Як використовувати альтернативу VLOOKUP в Excel?

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

Функція INDEX + MATCH як альтернатива VLOOKUP - приклад №1

Тепер візьміть ту саму таблицю з наведеного вище прикладу.

Відкрийте формулу INDEX у комірці H2.

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

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

LOOKUP означає, на якій основі я намагаюся знайти номер рядка. Тут для ідентифікатора EMP я намагаюся знайти номер рядка, тому значенням пошуку є ідентифікатор EMP.

Зараз шукати масив - це не що інше, але це стовпець значення основного пошуку таблиці.

Тепер нарешті тип відповідності повинен бути точним відповідним, тому вкажіть 0 як значення.

Результат буде наступним:

Yessssss !!!!!!! INDEX + MATCH працює точно так само, як VLOOKUP, але може отримувати дані з будь-якого місця та в будь-якому місці.

Перетягніть формулу до залишків комірок.

Функція LOOKUP як альтернатива VLOOKUP - приклад №2

LOOKUP сам по собі є вбудованою функцією в excel.

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

Візьміть ті самі дані з попереднього прикладу.

Відкрийте формулу LOOKUP у комірці H2 та оберіть значення пошуку як G2.

Тепер нам потрібно вибрати головний вектор пошуку як від D2 до D21 в головній таблиці.

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

Після введення формули натисніть клавішу введення, і ми отримаємо результат.

Перетягніть формулу до залишків комірок.

Що слід пам’ятати про альтернативи Excel для VLOOKUP

  • VLOOKUP може працювати лише зліва направо.
  • Функція MATCH поверне номер рядка.
  • Функції INDEX + MATCH і LOOKUP не вимагають номера стовпців, на відміну від VLOOKUP, для отримання даних потрібен номер стовпця, навіть якщо потрібний стовпець вже вибраний.
  • Структура даних не має значення для функцій INDEX + MATCH та LOOKUP. Але для VLOOKUP структура даних має значення.

Рекомендовані статті

Це посібник щодо альтернатив VLOOKUP в Excel. Тут ми обговорюємо приклади альтернатив Excel для VLOOKUP, таких як INDEX, MATCH та LOOKUP, а також практичні приклади та завантажений шаблон excel. Ви також можете ознайомитися з іншими запропонованими нами статтями -

  1. Функція VLOOKUP в Excel
  2. Підручники для функції MATCH в Excel
  3. Як користуватися LOOKUP в Excel?
  4. IFERROR з VLOOKUP в Excel
  5. Приклади макросів Excel

Категорія:

Поради Excel