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.
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 -")@Sergie Baklan
I am facing a problem with the formula. It looks like we have to select at least one item from column L otherwise it won't show the results for column H. "no inventory will be the result.
in other words, let's say I do not want to select any "type" just want to check what color is available in inventory 1. or vise versa I just want to check the "type" or sometimes both.
For instance, in this example, if you uncheck all column "M" the result will be "No inventory" however in inventory 1, we have items that their color is either blue or red which we have already selected. So it can not be true.
Do you have any idea how we can adjust the formula? by this assumption that also it works if there will be more than two columns of checkboxes, not only two columns.
Your thoughts regarding this will be greatly appreciated.Yes, that's a bug. I added couple of more error checks
=IFERROR( FILTER(A1:INDEX(A:A,COUNTA(A:A)), ( IFERROR( --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)), SEQUENCE(ROWS(A1:INDEX(A:A,COUNTA(A:A))),1,0,0)) + IFERROR( --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)), SEQUENCE(ROWS(A1:INDEX(A:A,COUNTA(A:A))),1,0,0)) >0) ), "- no inventory -")Result is in attached file
- SergeiBaklan
Sorry for the late response. It works perfectly. you make it look super simple. Thanks, man.