Как определить вхождение слова из именованного диапазаона в ячейку?

[Realtx]

Есть таблица с квартирами,  где полный адрес указан в 1 ячейке- город, улица, д.(в файле - лист база )
Есть именованные диапазоны - названия микрорайонов ( в приложенном файле - лист микрорайоны) , которые содержат названия улиц.
Надо в отдельной ячейке указать название микрорайона,  по названию улицы в ячейке с адресом.
Желательно формулой без макроса,( начальство и я их боимся ), и без разделения по столбцам ( адреса записываются криво, с / без пробелов , по порядку и без) нормально выделить название улицы не получилось.
Всем заранее спасибо за помощь.

[Serge 007]

Если привести в порядок исходные списки, то решение возможно например такой формулой массива:

Код Выделить Развернуть

=ЕСЛИ(СУММ(-ЕЧИСЛО(ПОИСК(микрорайоны!G$1:G$15;D4)));ИНДЕКС(микрорайоны!H$1:H$15;СУММ(ЕСЛИ(НЕ(ЕОШ(ПОИСК(микрорайоны!G$1:G$15;D4)));СТРОКА($1:$15))));"Нет такой улицы в списке")

Впрочем, можно и с текущим расположением данных работать, но формула получится посложнее

[Pelena]

С текущим расположением

Код Выделить Развернуть

=ЕСЛИ(МАКС((микрорайоны!$A$2:$E$6<>"")*ЕЧИСЛО(ПОИСК(микрорайоны!$A$2:$E$6;база!D4)));ИНДЕКС(микрорайоны!$A$1:$E$1;МАКС((микрорайоны!$A$2:$E$6<>"")*ЕЧИСЛО(ПОИСК(микрорайоны!$A$2:$E$6;база!D4))*СТОЛБЕЦ(микрорайоны!$A$1:$E$1)));"")

[Realtx]

Огромное спасибо. 2-й вариант работает без вопросов. 1-й - пока не всегда.
Не совсем разобрался с формулой , поэтому не знаю как быть,  если одна улица указана в 2-х микрорайонах. Иногда выводит одни микрорайон, иногда 2-й. Это если просто ошибка.
А если,  как в приложенном файле, когда 1 улица в нескольких микрорайонах расположена - можно усовершенствовать эту формулу ?
Еще раз огромное спасибо. Пойду учить матчасть

[Serge 007]

...не знаю как быть,  если одна улица указана в 2-х микрорайонах...

Ну а мы-то тогда откуда можем знать? Условия задачи устанавливаете ведь Вы...

...Иногда выводит одни микрорайон, иногда 2-й...

А надо как?

[zs]

предлагаю другой алгоритм