The Setup
You’re auditing vendor invoices for a procurement team at Alibaba Cloud’s APAC division. Your raw list lives in Sheet1!A2:D11. It includes vendor names, invoice IDs, amounts, and notes. Some vendors (like "TerraLogix") appear in notes even when they’re not the primary vendor — and you need to flag those where the Vendor Name column does not contain the word "Global" anywhere.| A (Vendor) | B (Invoice ID) | C (Amount) | D (Notes) |
|---|---|---|---|
| Sunrise Tech Ltd | INV-8842 | $12,450 | Payment pending approval |
| GlobalNet Solutions | INV-8843 | $8,920 | GlobalNet account #GNS-771 |
| Acme Corp | INV-8844 | $3,200 | Urgent — ship by 2024-03-15 |
| GlobalEdge Systems | INV-8845 | $15,600 | Includes GlobalEdge SLA addendum |
| TerraLogix Inc | INV-8846 | $7,100 | Global reference: GBL-2024-TLX |
| Vertex Dynamics | INV-8847 | $22,300 | Global support contract attached |
| NexusSoft | INV-8848 | $5,400 | Final reconciliation complete |
| GlobalCore Partners | INV-8849 | $18,750 | GlobalCore PO# GC-9921 |
| Orion Labs | INV-8850 | $4,800 | Global compliance audit required |
| StellarLink Group | INV-8851 | $9,200 | No global dependencies |
The Challenge
You need a column (say, E2:E11) that returns TRUE only when A2 does not contain "Global" — case-insensitive, ignoring partials like "Global" inside "GlobalCore" or "GlobalNet". Sounds simple. But here’s what trips people up: • COUNTIF(A2,"*Global*") returns 0 for blanks — so "does not contain" becomes TRUE for empty cells, which is wrong. • SEARCH("Global",A2) throws #VALUE! on blanks or mismatches — breaking whole formulas. • Using UPPER() + ISERROR(SEARCH()) seems safe… until you realize SEARCH is case-insensitive by default, making UPPER() redundant and slower. And yes — this came up last Thursday during a vendor reconciliation call with finance in Hangzhou. Sarah Chen typed =NOT(ISNUMBER(SEARCH("Global",A2))) into E2… then copied down. She got 3 FALSE positives because "TerraLogix" had "Global" in the Notes column — but her formula was only checking Vendor (A2). Easy to miss.Walking Through It
We’ll build the correct logic step-by-step in column E. Start in E2. Step 1: Test for presenceType =ISNUMBER(SEARCH("Global",A2)) in E2. This returns TRUE if "Global" appears anywhere in A2 — no case sensitivity needed.
| A (Vendor) | E (Contains "Global"?) |
|---|---|
| Sunrise Tech Ltd | FALSE |
| GlobalNet Solutions | TRUE |
| Acme Corp | FALSE |
| GlobalEdge Systems | TRUE |
| TerraLogix Inc | FALSE |
| Vertex Dynamics | FALSE |
| NexusSoft | FALSE |
| GlobalCore Partners | TRUE |
| Orion Labs | FALSE |
| StellarLink Group | FALSE |
=NOT(ISNUMBER(SEARCH("Global",A2))) looks right… until A6 is blank. Then SEARCH fails with #VALUE!. So wrap it: =IF(A2="",FALSE,NOT(ISNUMBER(SEARCH("Global",A2)))) That’s safer — but still flawed. Why? Because FALSE means “doesn’t contain Global”, yet blank cells shouldn’t count as “not containing”. In procurement, blank vendor fields are data errors — they need flagging, not silent exclusion. The fix: Use =AND(A2<>"",NOT(ISNUMBER(SEARCH("Global",A2)))) Now E2 returns TRUE only when A2 is non-blank AND doesn’t contain "Global". Copy that down to E11. You’ll get:
| A (Vendor) | E (Does NOT contain "Global") |
|---|---|
| Sunrise Tech Ltd | TRUE |
| GlobalNet Solutions | FALSE |
| Acme Corp | TRUE |
| GlobalEdge Systems | FALSE |
| TerraLogix Inc | TRUE |
| Vertex Dynamics | TRUE |
| NexusSoft | TRUE |
| GlobalCore Partners | FALSE |
| Orion Labs | TRUE |
| StellarLink Group | TRUE |
The Result
Here’s your final cleaned list — now filtered to show only rows where Vendor does not contain "Global" (i.e., E2:E11 = TRUE):| A (Vendor) | B (Invoice ID) | C (Amount) | D (Notes) |
|---|---|---|---|
| Sunrise Tech Ltd | INV-8842 | $12,450 | Payment pending approval |
| Acme Corp | INV-8844 | $3,200 | Urgent — ship by 2024-03-15 |
| TerraLogix Inc | INV-8846 | $7,100 | Global reference: GBL-2024-TLX |
| Vertex Dynamics | INV-8847 | $22,300 | Global support contract attached |
| NexusSoft | INV-8848 | $5,400 | Final reconciliation complete |
| Orion Labs | INV-8850 | $4,800 | Global compliance audit required |
| StellarLink Group | INV-8851 | $9,200 | No global dependencies |
What Could Go Wrong
Here are three real failures we saw last month — all from copying formulas without adjusting references: Mistake #1: Forgetting $ signs in mixed rangesIf you type =AND(A2<>"",NOT(ISNUMBER(SEARCH("Global",A2)))) in E2 but then drag it while referencing a fixed lookup table like $Z$1:$Z$100 elsewhere, and accidentally make it $A2 instead of A2 — every row checks only the first vendor. You’ll think “Sunrise Tech” appears 10 times. Mistake #2: Using FIND instead of SEARCH
FIND is case-sensitive. So "global" won’t match "Global" — and you’ll get FALSE where you expect TRUE. SEARCH handles it automatically. No need to overcomplicate. Mistake #3: Applying the formula to Notes instead of Vendor
Sarah Chen did this. Her filter showed 0 rows because she ran =AND(D2<>"",NOT(ISNUMBER(SEARCH("Global",D2)))) — checking Notes. That returned FALSE for every row except NexusSoft (which truly had no "Global" in Notes). She spent 47 minutes wondering why the list was empty. Here’s your action checklist — copy-paste ready:
| Task | Cell Range | Formula |
|---|---|---|
| Flag vendors NOT containing "Global" | E2:E11 | =AND(A2<>"",NOT(ISNUMBER(SEARCH("Global",A2)))) |
| Filter to show only those vendors | Select A1:E11 → Data tab → Filter | Click dropdown in E1 → check TRUE only |
| Quickly jump to first formula cell | — | Ctrl + G → type E2 → Enter |
| Auto-fit all columns after paste | — | Alt + H + F + I |
