Google Sheets: Perform Filter() on an array

Question

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.

Answer 1

try:

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

Answer 2

You can use QUERY() function in this way.

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