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.
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 -")
SergeiBaklan
Sorry for the late response. It works perfectly. you make it look super simple. Thanks, man.
Sorry for the late response. It works perfectly. you make it look super simple. Thanks, man.