Магия Excel

Классическая задача: нужно найти данные не по полному соответствию содержимого ячейки, а по вхождению текста (например, артикула, среди других, перечисленных через запятую) Спасибо Михаилу Музыкину, который поделился этой задачей с одного из эксельных форумов — и своими решениями. Это хороший повод напомнить про то, что в функции ВПР / VLOOKUP вообще-то работают символы подстановки (* = любой текст от нулевой длины и ? = один любой символ), а в ПРОСМОТРX / XLOOKUP они не работают по умолчанию, но их можно включить (пятый аргумент должен быть равен 2). А еще в ПРОСМОТРX работают регулярные выражения (пятый аргумент = 3), так что если вам нужно вхождение, можно просто применить этот вариант. А если нужно более точно, во избежание ошибок (когда, например, нужно искать именно набор символов и запятую после) - то применить его именно с регуляркой, чтобы искать артикул вместе с запятой как одно слово. Итак, варианты! Ищем просто вхождение артикула и получаем данные из 2 столбцов:

=ПРОСМОТРX(E3;A:.A;B:.C;;3)

ПРОСМОТРX в режиме регулярных выражений

=ПРОСМОТРX("*"&E4&"*";A:.A;B:.C;;2)

в режиме с символами подстановки, так что нужно добавить звездочки, чтобы искать артикул вместе с любым другим текстом до и после

=ВПР("*"&E6&"*";A:.C; {2;3};0) 

ВПР сразу работает с символами подстановки, а массив из двух чисел {2;3}, чтобы одной формулой получить два столбца. В старой версии Excel можно просто двумя отдельными функциями вида:

=ВПР("*"&E5&"*";A:C; 2;0)

или — с конкретным диапазоном и без ВПР, чтобы можно было искать и слева, и справа:

=ИНДЕКС($B$2:$C$10;ПОИСКПОЗ("*"&E8&",*";$A$2:$A$10&",";);)

Ищем артикул именно с запятой после:

=ФИЛЬТР(B:.C;РЕГТЕСТ(A:.A;E6&"(,|$)"))

ФИЛЬТР с массивом условий, формируемым функцией РЕГТЕСТ / REGEXTEST (REGEXMATCH в Google), которая выдает ИСТИНА / ЛОЖЬ при соответствии/несоответствии регулярке. В регулярке к артикулу добавляется либо запятая, либо $ (вариант для артикула в конце, без запятой) Подробнее про ФИЛЬТР читайте в статье

=ПРОСМОТРX(E5&"(,|$)";A:.A;B:.C;;3)

ПРОСМОТРX с регуляркой. Про новые ссылки вида A:.C и функцию УРЕЗДИАПАЗОН / TRIMRANGE смотрите тут. Ссылка на файл с примером