Thread: Searching for appropriate function

Hello All,

I need some help looking for a function which could help me.

I have the following scenarion:

I need something similar to "IF" function. That I can use for if cell value contains part of a text value return 1 if not return 2

Example:

IF AX contains "*.*pro*.*" then BX = 1
IF AX not contain "*.*pro*.*" then BX = 2

Hope I made myself clear enough

You could probably use either FIND() or SEARCH() (they're exactly the same, but FIND() is case sensitive but SEARCH() is not)

If you did SEARCH("Pro",A1), it would return the position of A1.

If the string does not contain the string "Pro", it will bring back an error, but use ISERROR() to trap it.

You can't use wildcard characters though, but by playing around with the SEARCH function, you should be able to get the desired result.

So something like...

IF(ISERROR(SEARCH("Pro",A1)),2,1)

Once you got that sussed, have a play with this...

There is a third argument to the SEACH() function, that is, start_number

use this third argument to locate the . areas both before & after the 'Pro' part.

thank you both for the reply will play a little bit now

Last question, how can I combine in:

IF(ISERROR(SEARCH("prov",M:M)),2,1)

more than one find text value? I want also to find blank sell + 1 additional search creteria.

Originally Posted by vladmir

Last question, how can I combine in:

IF(ISERROR(SEARCH("prov",M:M)),2,1)

more than one find text value? I want also to find blank sell + 1 additional search creteria.

Can you use 'AND'?

such as..

IF(ISERROR(AND(SEARCH("VAL1",M:M),SEARCH("VAL2",M:M))),2,1)

this will return 1 if both entries are present & correct

Originally Posted by NBVC

Have you tested this?

Nope, but yes, there is clearly something not quite right with that!

Which made me realise that surely a much simpler way would be..

IF(ISERROR((SEARCH("VAL1",M:M)+SEARCH("VAL2",M:M)),2,1)

Originally Posted by NBVC

Are you certain that works?...

Yep

First, of all, it must be entered as an array formula, using CTRL+SHIFT+ENTER...

I didn't need to - work as it was

Second, if you are not on 2007, you can't use M:M

hmm, I only check it aginst 1 cell, rather than a range. Probably why I got away without using arrays.

Third, Each of the SEARCH() functions will return a bunch of #VALUE! errors amongst any positive finds... so the ISERROR will always yield TRUE (unless every cell in the range is filled with one of the search values....). That is why I used ISNUMBER instead... because you are looking to see if there is at least one match, not at least one error....

hmm, depends how you read the question I think. I took it as that he wanted to check for both conditions being present, rather than one or the other, but yes, mine would not work in that case, but does for the former.
Even then, you could do a nested if() to check for the second criteria, Not pretty, but gets the job done

Edit: Just tried it on a range of data, and still works without using array formula, but of course, I'm looking to match both strings, rather than either of them