Forum Discussion
Solved
Need Some HELP with Formula
Hi I will appreciate if somebody can help me to find the right formula or function. not sure if it is possible only through Formula or needs VBA. I tried UNIQUE and SORT but it did not Work. I at...
Assuming that the color will always be the first word of the item description, you can try this in L2:
=IFERROR(INDEX(A$2:A$8,SMALL(IF(MMULT(--(LEFT(A$2:A$8,FIND(" ",A$2:A$8&" ")-1)=TRANSPOSE($H$2:$H$8)),--($J$2:$J$8)),ROW(INDIRECT("1:"&ROWS(A$2:A$8))),""),ROWS(L$2:L2))),"")I don't have the filter function to test it, but this might work also (Ctrl+Shift+Enter after keying or copying it to the formula bar):
=FILTER(A$2:A$8,MMULT(--(LEFT(A$2:A$8,FIND(" ",A$2:A$8&" ")-1)=TRANSPOSE($H$2:$H$8)),--($J$2:$J$8)))Also, not there is a trailing space for "Pink " in Column H that was removed. I attached the file as well.
JMB17 You're Rock man. The first Formula is the one I need. First is much more understandable and simple and sharp as a tack. The problem with second formula is that it finds the ones start with the color. Let's say somebody else who does not have any idea how the formula works updates the inventory every day and by accident put "HAT YELLOW" Instead of "yellow hat", However, the solution will be creating a new column next to products and call it Color. That way you make it more specific. For sure, The first formula shows everything that contains the color. In my case, works perfectly. Thanks, A lot for taking the time to solve the problem.
IMHO, both formulas check the start of the texts, that was the only reason why I adjusted second one.