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.
- 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.
- 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.
I was thinking what if we have multiple columns with checkboxes to filter? can we use "AND/OR" in "FILTER"?
An example is been attached:
in this example, let's say how we can filter inventory 1 to search for items selected in both columns H and J?
A formula that shows everything in Inventory 1 that includes (blue, black, red) and/or (Shirt, jacket or sweater)???
For OR condition you may + another MMULT, for AND multiply them as here (for OR)
formula is
=IFERROR(FILTER(A1:INDEX(A:A,COUNTA(A:A)), --(MMULT(--ISNUMBER( SEARCH(TRANSPOSE(FILTER($H$2:$H$8,$J$2:$J$8)), A1:INDEX(A:A,COUNTA(A:A)))), --FILTER($J$2:$J$8,$J$2:$J$8))+ MMULT(--ISNUMBER( SEARCH(TRANSPOSE(FILTER($L$2:$L$8,$N$2:$N$8)), A1:INDEX(A:A,COUNTA(A:A)))), --FILTER($N$2:$N$8,$N$2:$N$8)) >0)),"- no inventory -")
