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 spreadsheet versions that support XLOOKUP without legacy Ctrl+Shift+Enter array syntax. SEARCH and COUNTIF interpret wildcard characters in the excluded text; also decide explicitly whether blank tags should count as valid records.
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.