Google Sheets：将filter()嵌套arrayformula()实现双键匹配及行清除需求

Hey there! Let's work through your requirements to build a reusable, formula-safe calculator with dynamic dropdowns and protected logic.

1. Replace Per-Row FILTER with a Single ARRAYFORMULA + BYROW #

Since VLOOKUP can't handle two matching keys, we'll use BYROW (available in newer Google Sheets) to pair ARRAYFORMULA with FILTER for bulk, row-wise dual-condition matching.

Formula Setup: #

1. Pick a column in your calculator sheet for the matched results (e.g., Column K).
2. In cell K1, paste this formula (adjust ranges to match your actual column names):

   =ARRAYFORMULA(
     IF(A2:A="", "",  # Skip empty rows
       BYROW(A2:B, LAMBDA(row, 
         IFERROR(
           FILTER(database!C:C,  # Replace C:C with your target column in database
                  database!A:A=INDEX(row,1),  # Match "make" from current row
                  database!B:B=INDEX(row,2)   # Match "model" from current row
           ), 
           "No match"  # Show if no matching pair found
         )
       ))
     )
   )
   

3. This formula will automatically populate results for every row where you enter a make and model—no need to drag it down manually.

2. Eliminate Helper Columns for Dynamic Dropdowns #

You can create dependent dropdowns directly in your input columns (no hidden helper columns required):

Step 1: Make Dropdown (e.g., Column A) #

1. Select Column A (or the range you want for make inputs).
2. Go to Data > Data validation.
3. Under "Criteria", select List from range or formula and enter:

   =UNIQUE(database!A:A)
   

4. Check "Show dropdown list in cell" and save.

Step 2: Model Dropdown (e.g., Column B) #

1. Select Column B (or your model input range).
2. Go to Data > Data validation.
3. Under "Criteria", select List from range or formula and enter:

   =FILTER(database!B:B, database!A:A=INDIRECT("A"&ROW()))
   

4. This will dynamically show only models that match the make in the same row. Save the validation.

3. Protect Formulas & Allow Safe Data Clearing #

To ensure your core formula isn't broken when deleting rows:

1. Hide & Protect the Formula Cell:
   • Right-click cell K1 (where your ARRAYFORMULA lives) and select Protect range.
   • Set permissions to restrict editing to only you (or authorized users).
   • Optionally, hide Column K if you don't want it visible.
2. Allow Row Deletion Without Breaking Logic:
   • Since the ARRAYFORMULA is anchored in row 1, deleting any rows below (rows 2-20) won't affect the formula. The formula will automatically adjust to the remaining rows.
   • When you need to clear data, just select rows 2-20, right-click, and choose Delete rows or Clear contents—the formula in row 1 stays intact.

Why This Works #

• BYROW lets us run the FILTER logic for every row in one go, paired with ARRAYFORMULA to avoid per-row formulas.
• Dynamic data validation uses row-specific logic (via INDIRECT("A"&ROW())) to create dependent dropdowns without helper columns.
• Protecting the single formula cell ensures your core matching logic can't be accidentally deleted, even when clearing or removing rows.

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