Google Sheets: Perform Filter() on an array-CodePudding

thanks everyone. Appreciate everyone's input.

Is it possible for the Filter() formula to perform on an array, such as:

Filter(A:A,B:B={D:D})

The reason is, I need to obtain {D:D} from another formula that produces the array.

For example, {D:D} becomes {Red;Green}. Ideally, in this case output would be {1;2;4}

Column A	Column B
1	Red
2	Red
3	Blue
4	Green

I believe there are similar methods using arrayformula(vlookup()). However the output in that case becomes {1;4} as it'd only give the first match for Red.

Thanks again for taking the time to read this. Any suggestion is appreciated.

CodePudding user response：

You can use QUERY() function in this way.

=QUERY(A2:B,"select A, B where B matches '" & TEXTJOIN("|",TRUE,UNIQUE(D:D)) & "'")

CodePudding user response：

try:

=FILTER(A:A, REGEXMATCH(B:B&"", TEXTJOIN("|", 1, D:D))