Поиск в EXCEL третьего (n-го) повтора в списках с ТЕКСТом

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

Пусть исходный список содержит текстовые значения, расположенные в хаотическом порядке в диапазоне A7:A17 .

Задача

Найдем в какой строке находится третий дубликат значения Товар2 (далее будем его называть повтором).

Решение

Разместим в ячейке С7 текстовое значение, повтор которого хотим найти, т.е. Товар2 . В ячейке D7 - номер повтора, т.е. 3 (см. Файл примера ).

Сначала для удобства создадим Динамический диапазон ИсхСписок , который будет содержать наш Исходный список. Теперь при добавлении в него новых значений, они будут автоматически учитываться формулами. Формула для Динамического диапазона выглядит так: =СМЕЩ(Лист1!$A$7;;;СЧЁТЗ(Лист1!$A$7:$A$21))

Примечание . Решение этой задачи аналогично решению приведенному в статье Поиск третьего (n-го) повтора в списках с Числами .

Запишем формулу массива =НАИМЕНЬШИЙ(ЕСЛИ(ИсхСписок=C7;СТРОКА(ИсхСписок);"");D7)

Не забудьте нажать CTRL+SHIFT+ENTER после ввода формулы массива .

В файле примера также настроены правила Условного форматирования для отображения всех повторов (серый фон ячеек) и искомого повтора (зеленый).

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

Источником строк для этого Выпадающего списка определен Динамический диапазон из столбца H , в котором содержится Сортированный список только уникальных текстовых значений Исходного списка.

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

Для того, что у пользователя не было возможности ввести в ячейке D7 номер повтора превышающий общее количество заданного в ячейке С7 значения, в файле примера создан еще один Выпадающий список .