Страницы: 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.10.2018 23:36:17
По вопросам из тем форума, личку не читаю.
 
Поучу-ка я формулы. А если код товара 99?
Владимир
 
Если не только двузначные (формула массива)
=ПОДСТАВИТЬ(ВПР("*;"&D2&";*";";"&$A$2:$B$6&";";2;);";";)
 
Здравствуйте, коллеги! Во второй формуле в #2, мне кажется, последний IFERROR лишний.
Владимир
 
sokol92,  Владимир, ну скажем так, если  ничего нет, то чтоб ошибка не лезла. Четыре поиска, четыре IFERROR.
По вопросам из тем форума, личку не читаю.
 
Михаил, я думаю, что правильней выдавать #Н/Д при ненахождении (хотя это, конечно, дело вкуса). Кстати, #4 будет работать некорректно, если значение какой-нибудь ячейки из столбца B содержит точку с запятой (она исчезнет в ответе).
Изменено: sokol92 - 27.10.2018 23:57:46
Владимир
 
Цитата
sokol92 написал: что правильней выдавать #Н/Д при ненахождении
Вообще, показывать ошибки - неправильно. На форуме не раз появлялись темы с просьбами помочь обойти ошибки или с недоумением "почему не работает?".
Если уж нужно, то лучше вывести ругательство какое-нибудь.

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

В пользу моих длинных вариантов наверно можно отнести возможность работы с данными, в которых после и/или перед разделителя, может появится пробел Можно указать варианты. Убрать его не получится , так не вернуть его обратно во второй столбец. Но тогда надо переходить на Match
=INDEX(B2:B6;MATCH("*;"&D2&";*";";"&SUBSTITUTE(A2:A6;" ";)&";";))
По вопросам из тем форума, личку не читаю.
 
Немного почувствовал себя криптоаналитиком :)  
В первых двух удалось разобрался полностью, с последними сложнее...
В итоге почему-то ни одна формула не сработала...
Изменено: Esminec - 31.10.2018 14:26:00
 
Esminec, а зачем Вы продублировали в цитате формулу Михаила? Думаете, он её не помнит? Вернитесь в своё сообщение и удалите то, что Вы ошибочно считаете цитатой.
 
Цитата
Юрий М написал:
Думаете, он её не помнит?
Юрий, я как на сессии -  сдал, забыл :-)
Изменено: БМВ - 31.10.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.10.2018 20:55:58
По вопросам из тем форума, личку не читаю.
 
Насколько я могу судить (на темной стороне), в #11 проблемы с тем, что длина текстов в ячейках столбца A больше 255 символов.
Владимир
 
Цитата
sokol92 написал:
Насколько я могу судить (на темной стороне),
не судите, да не судимы будете :-) .
Владимир, добрый вечер. Я на компе файл не открывал а писал с мобилки. Выше подправил.
По вопросам из тем форума, личку не читаю.
 
Здравствуйте, Михаил! Посветлело...
Владимир
Страницы: 1
Наверх