Numbers: Conditional text output based on cell content

ahh, a seemingly-simple question with a surprisingly complex answer :)

There are a couple of ways of going about this.

The 'easy' way that most people think of first is a series of comparisons using something like:

=IFS(COUNTIF(C2,"*EXPRESS*"),"Loans",


COUNTIF(C2,"*Ever*"),"Utility",


COUNTIF(C2,"*MEDICARE*"),"Health",


TRUE,"Unknown")

where you just add additional COUNTIF() statements, one per string you want to match, and IFS() will return the first one that matches.

(Note this example uses COUNTIF() to do the string comparison, which is a little easier than the SEACH() I used before, especially since we don't care about where in the string the match occurs, just whether it does or not)

However, this has one significant drawback - namely that if you want to add a new category you have to update all the cells with the new value. For this reason you might prefer a lookup.

First create a table that looks like:

With as many lookup/value pairs you want.

Then, on your main table, use the formula:

=XLOOKUP(TRUE,ISNUMBER(SEARCH(Categories::A,C2)),Categories::B,"Unknown")

This does a couple of things. Starting from the internal parentheses, if first runs a SEARCH() comparing the entire column A from the Categories column to the target cell (in this case C2). This returns an array of values indicating which categories match. ISNUMBER() then convert these into a range of boolean values (which gets around the ERROR() that SEARCH() returns when there is no match).

This array of TRUE/FALSE values is then run through XLOOKUP() which looks for the first TRUE() value, and returns the corresponding value from Categories::B.

If there are no matches, XLOOKUP() returns the string "Unknown".

The advantage of this approach is that you can easily add new categories to the Categories table and have them instantly applied across the main sheet.

Note that you may need to be careful with the order of the categories - for example, if you have an entry from 'EverQuick Express Medicare Pharmacy, inc." you probably have an idea of which one of the three categories above it should return. Both approaches will return the first match, so 'Express', which may or may not be correct in this case.

You can't do quite what you describe, specifically:

> and fills another cell with "Utility"

Formulas can ONLY set the value of the cell they are in. They can NOT push a value to a different tell.

In other words, it is possible for a formula in a cell to set ITSELF to "Utility" if another cell contains "Ever", but it can't set the value of another cell.

That said, the SEARCH() function will return the position of a string within another string (or cell value). If the search term is found it returns the position in the string where it starts, and if it isn't found it throws an error, so the easiest path is to just to see if SEARCH() returns a number (you don't care where the substring is, just that it exists at all.

Therefore:

=IF(ISNUMBER(SEARCH("Ever",C2,1)),"Utility","")

will do what you describe - it checks the cell C2 (in this case) to see if it contains the string 'Ever'. If SEARCH() returns any number, the formula returns "Utility", otherwise it returns an empty string.

> t sounds like your formula would do exactly that, but it’s returning FALSE as the answer. Why is there a 1 in the formula?

Without seeing your formula it's hard to explain what it is, or is not doing.

I'm guessing that when you enter the SEARCH() function, my example shows:

...SEARCH("Ever",C2,1)...

For that, you need to look at the definition of SEARCH():

So the third parameter is start-pos which tells Numbers where to start looking in the field. It's listed as optional, and 1 is the default value, so technically isn't needed and you'd get the same result if it wasn't there. I just tend to provide all parameters as a matter of habit.

Good one, Camelot!

Regards,

Ian.