Поиск позиции ТЕКСТа с учетом РЕгиСТра в MS EXCEL и выводом значения из соседнего столбца

Здесь развиваются идеи статьи Поиск позиции ТЕКСТового значения с выводом соответствующего значения из соседнего столбца. Для нахождения позиции значения с учетом РЕгиСТра, с последующим выводом соответствующего значения из соседнего столбца, функция ВПР() применена быть не может, т.к. она не чувствительна к регистру. Задача может быть решена с помощи функций БИЗВЛЕЧЬ(), НАЙТИ() или СОВПАД().

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

Задача

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

Решение

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

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

 Решение задачи основано на использовании функций чувствительных к РЕгиСТру. См. в файле примера лист Текст.

Решение

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

=ИНДЕКС(B:B;МАКС(СОВПАД($D$4;$A$4:$A$15)*СТРОКА($A$4:$A$15)))

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

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

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

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

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

Первое и второе решение используют формулу массива.

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

Вывод числовых значений

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

Решение

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

=СУММПРОИЗВ(СОВПАД($D$4;$A$4:$A$15)*(B4:B15))

соответствующие значения суммируется

=ИНДЕКС(B:B;МАКС(СОВПАД($D$4;$A$4:$A$15)*СТРОКА($A$4:$A$15)))

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

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

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

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

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

Второе и третье решение используют формулу массива.

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

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

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