Text FunctionsIntermediate

Route Refund Tickets to Billing with ISNUMBER and SEARCH

SEARCH answers "where is this word?" with a position or an error, never with yes or no. Wrapping it in ISNUMBER turns that answer into the TRUE or FALSE an IF can route on, and SEARCH ignores case, so a shouted REFUND counts as much as a quiet one.

Task:

You run the support inbox for an online kitchenware shop. Any ticket whose subject mentions a refund — anywhere in the line, in any capitalisation — goes to the Billing queue; everything else goes to General. In column C, use IF with ISNUMBER and SEARCH to look for "refund" in each subject in column B and return "Billing" or "General". Fill in rows 2 through 7.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

7 rows × 3 columns6 cells you fill in
ABC
1TicketSubjectQueue
2T-8812Refund for damaged blender
3T-8813Where is my order #55210?
4T-8814Charged twice, need a REFUND
5T-8815Can I change my delivery address?
6T-8816Still waiting on my refund from last week
7T-8817Wrong size sent, please exchange
What this exercise teachesMay contain the answer

SEARCH("refund",B2) on its own returns 1 for T-8812, because the word starts at the first character, and #VALUE! for T-8813, which never mentions it — a position or an error, and neither is something IF can branch on. ISNUMBER is the translator: any position at all, 1 or 23, becomes TRUE, and the error becomes FALSE, so the question changes from "where?" to "is it there?" without caring about the answer to "where". That is also why ISNUMBER rather than a test like SEARCH(...)>0: on a subject without the word, the comparison would pass the #VALUE! straight through to IF, and the cell would show the error instead of "General". The choice of SEARCH over FIND is the other half of the formula. FIND is case-sensitive, so FIND("refund",B2) misses "Refund" at the start of T-8812 and "REFUND" at the end of T-8814, and two customers asking for their money back would wait in the General queue. Only T-8816, written all in lower case, would have been caught either way, which is exactly the kind of test row that makes a case-sensitive formula look correct.