Background
I have a spreadsheet with a range of source values, a dropdown from that range, and a FILTER that selects items from the source values matching the dropdown value:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Key | Value | Select | Filtered Key | Filtered Value |
| 2 | Item 1 | A | Item 1 | Item 1 | A |
| 3 | Item 5 | B | Item 1 | C | |
| 4 | Item 1 | C | |||
| 5 | Item 12 | D |
Cell D2 has the FILTER function, and cells in the range D3:D and E2:E are blank so that the results of the filter function can expand into them. The formula for the filter in cell D2 is:
=FILTER(A2:B5,A2:A5=C2)
Question
I would like to change the dropdown to a multi-select using the "Allow multiple selections" option. I want the filtered result to include any values that match any of the selection options.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Key | Value | Select | Filtered Key | Filtered Value |
| 2 | Item 1 | A | Item 1, Item 5 | Item 1 | A |
| 3 | Item 5 | B | Item 5 | B | |
| 4 | Item 1 | C | Item 1 | C | |
| 5 | Item 12 | D |
None of the values in my dropdown will contain ", ", so there won't be the ambiguity of the joining sequence existing within a value.
How can I filter based on values in a multiple selection dropdown in my desired manner?
