Пошук позиції ЧИСЛА в MS EXCEL з виведенням значення із сусіднього стовпця

Для знаходження позиції значення в стовпці, з наступним виведенням відповідного значення з сусіднього стовпця EXCEL, існує спеціальна функція ВПР() , але для її вирішення можна використовувати також і інші функції.

Нехай у діапазоніА4:В15 є таблиця з переліком товарів та його артикулами (кодами).

пошук

Потрібно, ввівши в коміркуD4 код товару (число), вивести в іншій комірці назву цього товару. Рішення наведено у файлі прикладу.

Алгоритм розв'язання задачі наступний:

  • знаходимо у списку кодів значення, що збігається з критерієм;
  • визначаємо номер позиції (рядок) знайденого значення;
  • виводимо значення із сусіднього стовпця того ж рядка.

Для вирішення цього завдання в EXCEL існує спеціальна функція ВПР() , але її вирішення можна використовувати й інші функції.

Рішення

Якщо кілька значень удовл. критерію

беретьсяперше зверху

беретьсяперше зверху

беретьсяперше зверху

= ДВССИЛ(АДРЕСА(НАБІЛЬШИЙ( ЯКЩО(($A$4:$A$15=$D$4); РЯДКУ($A$4:$A$15));1);2))

беретьсяостаннє зверху

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

числа

Для функції ВПР() потрібно, щоб стовпець, яким здійснюється пошук, був лівіше стовпця, який використовується для виведення. Обійти це обмеження дозволяє, наприклад, варіант з використанням функцій ІНДЕКС() та ПОШУКПОЗ() . Еквівалентна формула наведена у статті про функцію ВПР() (див. статтю http://excel2.ru/articles/funkciya-vpr-v-ms-excel-vpr).

Примітка. Як показано у статті Як EXCEL зберігає дату і час, будь-яка дата EXCEL – це число.Отже, наведене вище рішення працюватиме і у випадку, якщо в стовпціА будуть дати.

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

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