Google Sheets中Pathfinder技能筛选：排除含其他技能名的条目

Hey Laura! Let's get that exclusion logic working for your Pathfinder skill tool in Google Sheets. The core issue is building a dynamic QUERY condition that excludes rows where column C contains any full value from your exclusion list (column A). Here's a step-by-step solution:

Step 1: Create the Exclusion Condition String #

First, generate the clause that tells QUERY to exclude rows matching your terms. Let's assume your exclusion terms are in a range like Filters!A2:A (adjust this to your actual range—skip headers if you have them).

In a blank cell (e.g., G15), paste this formula:

=IF(COUNTA(Filters!A2:A)=0, "", "NOT (" & JOIN(" OR ", ARRAYFORMULA("LOWER(C) CONTAINS LOWER('" & SUBSTITUTE(FILTER(Filters!A2:A, Filters!A2:A<>""), "'", "\'") & "')")) & ")")

What this does: #

• COUNTA(Filters!A2:A) checks if there are any exclusion terms (returns empty if none).
• FILTER(Filters!A2:A, Filters!A2:A<>"") grabs only non-empty terms from your exclusion list.
• SUBSTITUTE(..., "'", "\'") escapes apostrophes in terms to avoid breaking the QUERY string.
• ARRAYFORMULA(...) builds case-insensitive match clauses (LOWER(C) CONTAINS LOWER('term')) for each term.
• JOIN(" OR ", ...) combines clauses to target any matching term.
• Wrapping in NOT (...) tells QUERY to exclude rows that match any of these terms.

Step 2: Combine with Your Existing Filter Logic #

Replace your main filter cell formula with this merged version:

=LET(
  base_condition, IF(ISNA(G14), "", MID(G14, 12, LEN(G14)-11)),
  exclusion_condition, G15,
  full_where, IF(AND(base_condition="", exclusion_condition=""), "", 
                IF(base_condition="", exclusion_condition, 
                   IF(exclusion_condition="", base_condition, 
                      base_condition & " AND " & exclusion_condition)
                   )
                ),
  IF(full_where="", QUERY(Dons, "select *"), QUERY(Dons, "select * where " & full_where))
)

Breakdown: #

• base_condition extracts your existing filter logic from G14 (removes the "select* where " prefix).
• full_where combines the base filter and exclusion condition:
  • Shows all rows if no conditions are active.
  • Uses only the active condition if one exists.
  • Merges both with "AND" if both are active.
• The final IF runs the QUERY with the combined logic.

Why Your Previous Attempt Failed #

Your formula =IF(F7;=ISNA(=LOOKUP(A;C;1;FALSE));"") had a few critical issues:

1. Extra equals signs inside the IF function (invalid syntax).
2. LOOKUP is designed for sorted range matches, not checking substring presence across a list.
3. Boolean functions like ISNA can't be directly embedded in a QUERY string—you need to build a text-based condition instead.

Optional: Exact Matches Instead of Substrings #

If you want to exclude rows where column C exactly equals any term in column A (not just contains it), modify the exclusion clause to use = instead of CONTAINS:

=IF(COUNTA(Filters!A2:A)=0, "", "NOT (" & JOIN(" OR ", ARRAYFORMULA("C = '" & SUBSTITUTE(FILTER(Filters!A2:A, Filters!A2:A<>""), "'", "\'") & "'")) & ")")

Question content sourced from Stack Exchange, asked by Laura