Perform operation on specific column in filtered range, right after filtering

Question

I have this formula in E2: =FILTER(A1:C20,B1:B20=2) and need to perform substitute like:

I'm hoping to do the formula in a single cell, without additional columns.

Thanks for editing the question. I should emphasize that I only want to substitute things in a specific column. I have edited the spreadsheet and your answer don't work anymore. — Allen Sierra, Commented Jan 23, 2019 at 18:05

user0 · Accepted Answer · 2019-01-24 13:18:09Z

0

=ARRAYFORMULA(FILTER({A1:B, SUBSTITUTE(C1:C, "stuff", "blah")}, B1:B=2))

answered Jan 24, 2019 at 13:18

user0

27.1k10 gold badges53 silver badges78 bronze badges

1

Thank you MMA, you've been a great help around here!
– Allen Sierra
Commented Jan 24, 2019 at 20:26

Add a comment |

user0 · Accepted Answer · 2019-01-23 16:39:04Z

0

=ARRAYFORMULA(SUBSTITUTE(FILTER(A1:C20, B1:B20=2), "stuff", "blah"))

answered Jan 23, 2019 at 16:39

user0

27.1k10 gold badges53 silver badges78 bronze badges

Thanks for editing the question. I should emphasize that I only want to substitute things in a specific column. I have edited the spreadsheet and your answer don't work anymore.
– Allen Sierra
Commented Jan 23, 2019 at 18:05
imgur.com/a/ylwZvj4
– Allen Sierra
Commented Jan 24, 2019 at 7:51
@AllenSierra I see, added new answer
– user0
Commented Jan 24, 2019 at 13:16

Add a comment |

2 Answers 2