谷歌表格：筛选后将Yes/No替换为对应状态的公式求助

Hey there! You're already halfway there with your REGEXREPLACE + FILTER setup. To handle both "Yes" → "Signed" and "No" → "Awaiting", you have a few clean, straightforward options in Google Sheets:

Option 1: ARRAYFORMULA + IF (Simple & Direct) #

Since FILTER returns an array of values, wrap an IF statement in ARRAYFORMULA to apply the conversion to every filtered result:

=ARRAYFORMULA(IF(FILTER(Sheet1!C:C, Sheet1!B:B="Triangle")="Yes", "Signed", "Awaiting"))

This checks each value from your filter: if it's "Yes", it outputs "Signed"; otherwise (which is "No" in your dataset), it outputs "Awaiting".

Option 2: Nested REGEXREPLACE (Build on Your Existing Formula) #

You can chain two REGEXREPLACE functions to handle both replacements sequentially, building on the formula you already started:

=REGEXREPLACE(REGEXREPLACE(FILTER(Sheet1!C:C, Sheet1!B:B="Triangle"), "Yes", "Signed"), "No", "Awaiting")

First, it swaps all "Yes" entries for "Signed", then takes that modified set and replaces any remaining "No" entries with "Awaiting".

Option 3: SWITCH (More Readable for Future Extensions) #

If you might need to add more status mappings later, SWITCH makes the logic easier to read and extend:

=ARRAYFORMULA(SWITCH(FILTER(Sheet1!C:C, Sheet1!B:B="Triangle"), "Yes", "Signed", "No", "Awaiting"))

SWITCH maps each exact match to its corresponding output—perfect if you ever need to handle additional values beyond "Yes" and "No".

Full Sheet2 Setup #

To get the complete Sheet2 with both columns working automatically:

1. In Sheet2 cell A2 (Name column), use:

   =FILTER(Sheet1!A:A, Sheet1!B:B="Triangle")
   

2. In Sheet2 cell B2 (Contract Status column), use any of the conversion formulas above.

All these formulas will update automatically if you add or modify rows in Sheet1!

内容的提问来源于stack exchange，提问作者user9773567