I have a set of rows by which I am trying to display filtered on a different sheet based off of a column that matches a key range.
I can get it to work if there is a single key. I am having trouble when there is a list of keys. (Comparing to each key individually and putting ANDs between them is not a good solution.)
Looking at the image above, when I use xMatch it only returns the first index instead of every match, otherwise it would work.
There’s got to be a way to do it with filter too, but again, I can only get it to work with a single key, not all keys in a range.
How should I create the formula to get the desired result?
EDIT (removed google sheet link and added markdown table):
I am not sure if this is what is being requested, but I copied the cells and put it in a ‘table to markdown’ converter. Here is the result:
| a | abacus | Formula: | `=CHOOSEROWS(A1:B10,arrayformula(XMATCH(B15:B17,A1:A10))) | ||
|---|---|---|---|---|---|
| b | baloney | a | abacus | ||
| c | cat | d | dog | ||
| a | airplane | c | cat | ||
| a | awesome | ||||
| b | balloon | ||||
| c | ate | ||||
| d | dog | Formula: | `=filter(A1:B12,A1:A12=B15:B17) | ||
| d | danger | a | abacus | ||
| b | basketball | c | cat | ||
| e | elephant | ||||
| e | electric | ||||
| Desired output: | Formula= ??? | ||||
| Key | a | abacus | |||
| a | c | cat | |||
| d | a | airplane | |||
| c | a | awesome | |||
| c | ate | ||||
| d | dog | ||||
| d | danger | ||||
7
Use REDUCE(LAMBDA())
=REDUCE({"clm1","clm2"},B15:B17,LAMBDA(_z,_y,vstack(_z,filter(A1:B12,A1:A12=_y))))
1
You may try:
=filter(A1:B12,xmatch(A1:A12,B15:B17))
1
Another alternative using the QUERY function, which simulates a LEFT JOIN in SQL
=QUERY({A2:B},"Select Col1, Col2 Where Col1 Matches '" & TEXTJOIN("|",,D2:D) & "' And Col1 Is Not Null Order By Col1 Asc")
.
1
You may try the following formula:
=FILTER(A1:B12,(A1:A12=B15)+(A1:A12=B16)+(A1:A12=B17))
Where B15, B16 and B17 are your keys.
ShrimpFan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1