How can I switch using an array or a range of values in google sheets? In other words, how can I use the range of values in B2:B to work as the "Switch" Parameters in the formula below?
The 1st formula used in the table below generates the desired result in column C
1. arrayformula(if(A2:A<>"",SWITCH(trim(A2:A),"A","B","C","D"),""))
But the 2nd formula I use evaluates as #N/A
2. arrayformula(if(A2:A<>"",SWITCH(trim(A2:A),JOIN(",",FILTER(B2:B,B2:B<>""))),""))
| A | B | C | |
|---|---|---|---|
| 1 | Values | Replacement | Desired (using formula 1) |
| 2 | A | "A","B" | B |
| 3 | C | "C","D" | D |
What I tried - replacing using switch
Both textjoin(",",TRUE,B2:B) and JOIN(",",FILTER(B2:B,B2:B<>"")) result in "A","B","C","D" so I figured that replacing those values in formula 1 above as in the 2nd formula would work buy it didn't instead evaluating to #N/A
Can I use SWITCH with a range that is generated or is there some other way to achieve using the joining the values in column B to be used to replace values in column C?
CodePudding user response：
try:
=INDEX(TRIM(REGEXREPLACE(B2:B3, "\b"&A2:A3&"\b", )))
or:
=INDEX(IFNA(IFNA(VLOOKUP(A2:A6, SPLIT(B2, "|"), 2, ),
VLOOKUP(A2:A6, SPLIT(B3, "|"), 2, )), A2:A6))
CodePudding user response：
No. Currently, It's not possible to unpack/unzip/spread a array/range to arguments of a function.
Workaround in this specific case would be to use regex:
=ARRAYFORMULA(REGEXREPLACE(B2:INDEX(B2:B,COUNTA(B2:B)),"["&A2:INDEX(A2:A,COUNTA(A2:A))&",""]",))
This creates a regex like [A",], where
[]is a character classA",is literalA,"and,
If it matches, it gets replaced with nothing.