Google Sheets formula query an array based on matches from another array

1
votes

I would like to filter/query Columns A:B based on 1 or more values in Column E.

Essentially, I want to Return values from ColB for any values within ColA which appear in ColE. I wish I could use this:

=query({$B$1:$C$101},"select Col2 where Col1='" & {$E$2:$E$101} & "'")

But, using an array {$E$2:$E$101} for the values to match doesn't work within a query. Here is a link to my spreadsheet:

https://docs.google.com/spreadsheets/d/1fUixRqqOYh6LleMkvgJdmp7yg2YqOnfzjuHB3glMBKY/edit?usp=sharing

picture of a spreadsheet

1
regexgoogle-sheetsgoogle-sheets-formulagoogle-sheets-querytextjoin

1 Answers

0
votes

formula should be:

=QUERY({B:C}, "select Col2 where Col1 matches '^"&TEXTJOIN("$|^", 1, E:E)&"$'", 0)

0