Поиск позиции ЧИСЛА в MS EXCEL с выводом значения из соседнего столбца

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

Пусть в диапазоне А4:В15 имеется таблица с перечнем товаров и их артикулами (кодами).

Задача

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

Решение

Алгоритм решения задачи следующий:

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

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

Решение

Если несколько значений удовл. критерию

=ВПР($D$4;A4:B15;2;ЛОЖЬ)

берется первое сверху

=ИНДЕКС(B4:B15; ПОИСКПОЗ($D$4;A4:A15;0);1)

берется первое сверху

=СМЕЩ($B$3; ПОИСКПОЗ($D$4;$A$4:$A$15;0);0;1;1)

берется первое сверху

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

берется последнее сверху

=ПРОСМОТР($D$4;$A$4:$A$15;$B$4:$B$15)

если столбец отсортирован по возрастанию, то берется последнее сверху, если нет, то результат непредсказуем

=БИЗВЛЕЧЬ(A3:B15;2;F15:F16)

возвращается ошибка #ЧИСЛО!

Для функции ВПР() требуется, чтобы столбец, по которому производится поиск, был левее столбца, который используется для вывода. Обойти это ограничение позволяет, например, вариант с использованием функций ИНДЕКС() и ПОИСКПОЗ(). Эквивалентная формула приведена в статье о функции ВПР() (см. статью http://excel2.ru/articles/funkciya-vpr-v-ms-excel-vpr).

Задача подразумевает, что искомое значение гарантировано будет найдено. В случае, если требуется найти ближайшее значение, читайте статью http://excel2.ru/articles/poisk-chisla-blizhayshego-k-zadannomu-sortirov...

Примечание. Как показано в статье Как EXCEL хранит дату и время, любая дата в EXCEL – это число. Следовательно, приведенное выше решение будет работать и в случае, если в столбце А будут находиться даты.

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

Примечание. Если диапазон поиска содержит повторяющиеся значения и требуется вернуть не одно, а все значения, удовлетворяющие критерию, то читайте статью Запрос на основе Элементов управления формы.

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

Аналогичный поиск текстовых значений приведен в статье Поиск позиции ТЕКСТового значения с выводом соответствующего значения из соседнего столбца.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.3 (3 оценок)

Комментарии

eima_82

помогите пожалуйста найти решение-
есть таблица вида (данные в разных ячейках)

AA Blue 200 $6.000
AA Red 600 $12.000
AA Red 750 $13.000
AA Red 1000 $17.000
BB Red 280 $10.000
СС Blue 100 $5.000
CC Blue 800 $13.000
и есть значения
AA Red 580,
AA Red 750
AA Blue 100
нужно найти какая цена может быть при каждой из этих комбинаций - для первого - ближайшее к 580 ( т.е. пропорционально цене за 600-й )
для второго найти AA Red 750 точно
в третьем случае пропорционально стоимости за AA Blue 200 для конфигурации 100-й

Creator

Поиск ближайшего рассмотрен в разделе http://excel2.ru/gruppy-statey/blizhayshee-chislo

eima_82

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

Creator

Направил Вам файл на адрес, указанный Вами при регистрации

eima_82

Creator, Спасибо! все работает прекрасно!!