Logical & Lookup Formulas
Match First Row Not Containing Specific Text (XLOOKUP & SEARCH)
Retrieve the first order or server record that excludes an internal/test substring using an ISERROR(SEARCH(...)) helper flag with XLOOKUP and count all non-matching records with COUNTIF.
Skipping noisy test or internal prefix rows to find the first real production event!
Telemetry logs and order exports routinely start with synthetic health checks ("INTERNAL-QA-01", "INTERNAL-STG-02") before the first external customer transaction arrives. When you need the first ticket ID whose environment tag does NOT contain "INTERNAL", pairing a transparent per-row boolean flag =ISERROR(SEARCH($F$2, B2)) with =XLOOKUP(TRUE, C2:C6, A2:A6, "All Rows Excluded") works in every spreadsheet without legacy Ctrl+Shift+Enter array syntax!
Why does ISERROR(SEARCH(...)) work so cleanly? SEARCH returns the starting character index when the excluded keyword appears anywhere inside B2, and #VALUE! when the keyword is absent—so ISERROR flips absence into TRUE! Meanwhile, to count how many rows in B2:B6 exclude that substring altogether, wildcard COUNTIF(B2:B6, "<>*" & F2 & "*") returns the exact clean record count in one step!
| Row Event ID (Col A) | Environment Tag (Col B) | ISERROR(SEARCH("INTERNAL", B)) | Lookup Role |
|---|---|---|---|
| EVT-401 | INTERNAL-QA-PING | FALSE (Contains Keyword) | Skipped |
| EVT-402 | INTERNAL-STG-SYNC | FALSE (Contains Keyword) | Skipped |
| EVT-403 | PROD-EU-CHECKOUT | TRUE (Clean Match!) | Returned First by XLOOKUP! |
First Record Whose Tag Does Not Contain Excluded Substring
Scans boolean helper column C2:C6 (=ISERROR(SEARCH($F$2, B2))) for the first TRUE and returns the corresponding Event ID from A2:A6.
=IF(TRIM(F2) = "", "Enter Excluded Text", XLOOKUP(TRUE, C2:C6, A2:A6, "All Rows Excluded"))Count Total Records Not Containing Excluded Substring
Uses negated asterisk wildcards "<>*" & F2 & "*" inside COUNTIF to tally every clean production record.
=IF(TRIM(F2) = "", COUNTA(B2:B6), COUNTIF(B2:B6, "<>*" & F2 & "*"))Try the example
Use the original values or change them to test your understanding.