Поиск наименований по ключевому слову в Excel⁠⁠

Задача: формировать список значений, найденных по ключевому слову. Сразу продемонстрирую результат, который получится в итоге:

Начало работы

Что имеем: 1) список данных (В4:В30), предварительно очищенный от дубликатов, 2) ячейка для ввода ключевого слова (Е1) и 3) диапазон под вывод результатов (предварительно пронумерованный по всей длине списка):

1 шаг. Формирование нумерованного списка по поиску

В дополнительный столбец (например, А) вносим формулу:

=ЕСЛИ (ПОИСК($E$1;B4)>0; СЧЁТЕСЛИ($B$4:B4;"*"&$E$1&"*");0)

где

ПОИСК($E$1;B4) — выполняет проверку совпадений. Если совпадение есть, выводит порядковый номер первого совпадающего символа. На самом деле неважно, какое это будет числовое значение, главное, что оно числовое. Если совпадения нет, выходит #ЗНАЧ. Поэтому

ЕСЛИ (ПОИСК($E$1;B4)>0 следует понимать как «если совпадение найдено».

Часть СЧЁТЕСЛИ($B$4:B4;"*"&$E$1&"*") означает - при нахождении неточного совпадения («звездочки» (*) указывают, что совпадение неточное) выводи значение повтора - это будет 1,2,3...

В целом, это выглядит так:

На заметку! Функция ПОИСК выполняет поиск без учета регистра. Если нужен поиск с учетом регистра, вместо ПОИСК надо использовать функцию НАЙТИ.

2 шаг. Выводим список результатов

С помощью ВПР в столбце для результатов (Е) находим результаты сравнения номеров из столбца D с номерами из А. Через ЕСЛИОШИБКА скрываем Н/Д (''''):

3* шаг. Наводим красоту

Шаг, возможно, не обязательной, так как основная работа сделала, поэтому помечен *. Но лично я люблю, когда все красиво и понятно, поэтому про «красоту» тоже расскажу.

Во-первых, сейчас уже можно скрыть вспомогательный столбец А, он для показа не нужен.

Во-вторых, лучше скрыть ненужные порядковые номера из столбца D. Пусть здесь работает такой принцип: сколько результатов найдено, столько номеров и видно.

Исходно номеров проставлено столько, сколько всего позиций в списке (ну а вдруг?). Но чаще всего они в таком количестве не нужны, поэтому схитрим и скроем их через Условное форматирование:

1. Выделяем ячейки D4:D30, выбираем Главная — Условное форматирование — Создать правило, Использовать формулу…

Устанавливаем формулу: =$Е4='''', что значит: если ячейка столбца Е пустая.

Нажимаем кнопку Формат, выбираем вкладку Шрифт, цвет Белый.

Жмем ОК во всех окнах, наслаждаемся результатом :)

Если бы можно было приложить файл, тоже бы добавила. Если кто знает способ, подскажите.