Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Найти код товара (коды перечислены через запятую) и проставить значения из соседней правой ячейки
 
Доброго времени товарищи. Прошу помочь!
Перекопал много информации но решения так и не удалось найти.

Задача казалось бы проста, необходимо найти код товара и проставить значения из соседней правой ячейки.
Проблема в том что коды товара перечислены через точку с запятой а ВПР как и ИНДЕКС+ПОИСКПОЗ отказываются искать нужное значение в ячейках в которых указано более одного значения.
Пример на яблоках прилагаю.
 
Если коды всегда двузначные, то
=VLOOKUP("*"&D2&"*";A:B;2;0) а вот если нет, то с учетом разделителя наверно так
=IFERROR(VLOOKUP(D2;A:B;2;0);IFERROR(VLOOKUP(D2&";*";A:B;2;0);IFERROR(VLOOKUP("*;"&D2&";*";A:B;2;0);IFERROR(VLOOKUP("*;"&D2;A:B;2;0);""))))
или массивно так
=INDEX(B:B;MIN(IFERROR(MATCH(CHOOSE({1;2;3;4};D2;D2&";*";"*;"&D2&";*";"*;"&D2);A:A;);1=0)))
=IFERROR(INDEX(B:B;IFERROR(1/(1/MIN(IFERROR(MATCH(CHOOSE({1;2;3;4};D7;D7&";*";"*;"&D7&";*";"*;"&D7);A:A;);1=0)));NA()));"")
или без трех кнопок
=INDEX(B:B;MIN(IFERROR(INDEX(MATCH(CHOOSE({1;2;3;4};D2;D2&";*";"*;"&D2&";*";"*;"&D2);A:A;););1=0)))
=IFERROR(INDEX(B:B;AGGREGATE(15;6;1/(1/MATCH(CHOOSE({1;2;3;4};D2;D2&";*";"*;"&D2&";*";"*;"&D2);A:A;));1));"")
Изменено: БМВ - 27 Окт 2018 23:36:17
 
Поучу-ка я формулы. А если код товара 99?
Владимир
 
Если не только двузначные (формула массива)
=ПОДСТАВИТЬ(ВПР("*;"&D2&";*";";"&$A$2:$B$6&";";2;);";";)
 
Здравствуйте, коллеги! Во второй формуле в #2, мне кажется, последний IFERROR лишний.
Владимир
 
sokol92,  Владимир, ну скажем так, если  ничего нет, то чтоб ошибка не лезла. Четыре поиска, четыре IFERROR.
 
Михаил, я думаю, что правильней выдавать #Н/Д при ненахождении (хотя это, конечно, дело вкуса). Кстати, #4 будет работать некорректно, если значение какой-нибудь ячейки из столбца B содержит точку с запятой (она исчезнет в ответе).
Изменено: sokol92 - 27 Окт 2018 23:57:46
Владимир
 
Цитата
sokol92 написал: что правильней выдавать #Н/Д при ненахождении
Вообще, показывать ошибки - неправильно. На форуме не раз появлялись темы с просьбами помочь обойти ошибки или с недоумением "почему не работает?".
Если уж нужно, то лучше вывести ругательство какое-нибудь.

Цитата
#4 будет работать некорректно, если значение какой-нибудь ячейки из столбца B содержит точку с запятой
О точке с запятой - да, пропадет. Можно вместо ПОДСТАВИТЬ применить ПСТР  с обрезанием крайних символов.
Слепим два разделителя:
=ПОДСТАВИТЬ(ВПР("*;"&D2&";*";";;"&$A$2:$B$6&";;";2;);";;";)
 
Спасибо за обучение! :) Успехов!
Изменено: sokol92 - 28 Окт 2018 00:12:53
Владимир
 
Цитата
vikttur написал:
Если уж нужно, то лучше вывести ругательство какое-нибудь.
именно так, и в данном случае все готово, только вставить и все.
Цитата
vikttur написал:
О точке с запятой - да, пропадет.
на самом дел , подумав об этом я сразу отказался от варианта наподобии №4, правда еще от него меня отталкивает необходимость указать диапазон ограниченный, но это дело вкуса. Кто-то делает с запасом, кто-то предпочитает менять в формуле.

В пользу моих длинных вариантов наверно можно отнести возможность работы с данными, в которых после и/или перед разделителя, может появится пробел Можно указать варианты. Убрать его не получится , так не вернуть его обратно во второй столбец. Но тогда надо переходить на Match
=INDEX(B2:B6;MATCH("*;"&D2&";*";";"&SUBSTITUTE(A2:A6;" ";)&";";))
 
Немного почувствовал себя криптоаналитиком :)  
В первых двух удалось разобрался полностью, с последними сложнее...
В итоге почему-то ни одна формула не сработала...
Изменено: Esminec - 31 Окт 2018 14:26:00
 
Esminec, а зачем Вы продублировали в цитате формулу Михаила? Думаете, он её не помнит? Вернитесь в своё сообщение и удалите то, что Вы ошибочно считаете цитатой.
 
Цитата
Юрий М написал:
Думаете, он её не помнит?
Юрий, я как на сессии -  сдал, забыл :-)
Изменено: БМВ - 31 Окт 2018 11:05:36
 
Цитата
Юрий М написал:
Вернитесь в своё сообщение и удалите то, что Вы ошибочно считаете цитатой.
Удалил.
Но так и не понял как можно цитату ошибочно считать цитатой?
 
Цитата
Esminec написал: Но так и не понял как можно цитату ошибочно считать цитатой?
, если вы всё произведение Л.Н. Толстого "Война и мир" вставите в сообщение это будет считаться цитатой, если да то у вас неверное представление об этом понятии.
Лень двигатель прогресса, доказано!!!
 
Esminec, исходя из ЛС, у Вам не удается использовать формулы в рабочем файле. Причин может быть море. Если не справиться, то помощь может быть оказана, но нужен файл.
UPD не увидел файла
Наверно самое универсальное
=INDEX($B:$B;MIN(IF(ISNUMBER(FIND(";"&D2&";";";"&TRIM($A$1:INDEX($A:$A;COUNTA($A:$A)))&";"));ROW($A$1:INDEX($A:$A;COUNTA($A:$A))))))

варианты в файле, указанный - последний, но он массивный. тоже можно через Aggregate(15;6 сделать.
Изменено: БМВ - 31 Окт 2018 20:55:58
 
Насколько я могу судить (на темной стороне), в #11 проблемы с тем, что длина текстов в ячейках столбца A больше 255 символов.
Владимир
 
Цитата
sokol92 написал:
Насколько я могу судить (на темной стороне),
не судите, да не судимы будете :-) .
Владимир, добрый вечер. Я на компе файл не открывал а писал с мобилки. Выше подправил.
 
Здравствуйте, Михаил! Посветлело...
Владимир
Страницы: 1
Читают тему (гостей: 1)
Наверх