Forum Discussion
Need Some HELP with Formula
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.
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.
- Yes, I thought about using SEARCH in case the color was not the first word. But, then I thought you could run into a potential issue with the color being embedded as part of another word in the item description (like how "red" appears in the word "scared").
But, moving the color to it's own cell would certainly solve all of those potential issues. And, you could dispense with the LEFT(x, FIND()) part. And, from Sergei's post, it looks like the filter function can work, so it could be further simplified (I just don't have the filter function to play with it). IMHO, both formulas check the start of the texts, that was the only reason why I adjusted second one.
