Can you do multiple options for the =if(isnumber(search))) formula in excel?

Question

This formula looks for the string "BFA" inside cell I3. If found, it returns "BFA"; if not, it returns "No".

=IF(ISNUMBER(SEARCH("BFA",I3)),"BFA","No")

How can I modify this to work for multiple strings at the same time? For example, if any of {"BFA", "MFA", "BA", "MA"} is found, then return what was found; if not, return "No".

Bloodline

I don't believe off hand that SEARCH can work like that... but you could just nest a few IF(ISNUMBER(SEARCH(...))) statements. Like IF(ISNUMBER(SEARCH("BFA",I3)),"BFA",IF(ISNUMBER(SEARCH("MFA",I3)),"MFA",IF(... – Bahia

Answer 1

This is absolutely possible, but I would recommend a different approach:

=INDEX({"BFA","MFA","BA","MA","No"},MATCH(1,COUNTIF(I3,"*"&{"BFA","MFA","BA","MA",""}&"*"),0))

You can also put the items to look for in a range of cells and their results in the range next to them. So if you had the following setup:

    _____A____  ___B__
1   Search For  Return
2   BFA         BFA
3   MFA         MFA
4   BA          BA
5   MA          MA
6               No

Then the formula would look like this:

=INDEX($B$2:$B$6,MATCH(1,COUNTIF(I3,"*"&$A$2:$A$6&"*"),0))

For making it scalable and updatable I recommend putting what to search for and what that search should return if found in a range as shown. You could put it on a separate sheet or hide those columns if preferred.

Revelation

it's a nice approach - note that the latter formula needs array entry, also with both versions you'd still get #N/A error if I3 is blank – Elconin

Answer 2

You can use this version with LOOKUP:

=IFERROR(LOOKUP(2^15,SEARCH({"BFA","MFA","BA","MA"},I3),{"BFA","MFA","BA","MA"}),"No")

The SEARCH function returns an array of either numbers or errors (depending on whether each string is found or not). When you lookup 2^15 in that array, the match is always with the last number, and then LOOKUP returns the corresponding text string. If there are no matches, you get #N/A and IFERROR converts that to "No".

So if you have any cells that contain both "BFA" and "BA", for example, the formula will return the last one in your list, i.e. "BA".

Elconin

Answer 3

This will do what you are looking for:

=IF(ISNUMBER(FIND("BFA",I3)),"BFA",IF(ISNUMBER(FIND("MFA",I3)),"MFA",IF(ISNUMBER(FIND("BA",I3)),"BA",IF(ISNUMBER(FIND("MA",I3)),"MA","NO")))

Although the more nested If statements you make, it can get very complex. You might consider trying a custom formula in the future.

Knucklebone

Answer 4

This is absolutely possible, but I would recommend a different approach:

=INDEX({"BFA","MFA","BA","MA","No"},MATCH(1,COUNTIF(I3,"*"&{"BFA","MFA","BA","MA",""}&"*"),0))

You can also put the items to look for in a range of cells and their results in the range next to them. So if you had the following setup:

    _____A____  ___B__
1   Search For  Return
2   BFA         BFA
3   MFA         MFA
4   BA          BA
5   MA          MA
6               No

Then the formula would look like this:

=INDEX($B$2:$B$6,MATCH(1,COUNTIF(I3,"*"&$A$2:$A$6&"*"),0))

For making it scalable and updatable I recommend putting what to search for and what that search should return if found in a range as shown. You could put it on a separate sheet or hide those columns if preferred.

Avitzur

Answer 5

Formula in column B - copied down the column

=IF(ISERROR(MATCH(A2,$D:$D,0)),"Body","Face")

A        B       C    D
                      Face Parts
Ear      Face         Ear 
Mouth    Face         Mouth 
Leg      Body

Marcelmarcela

While this might answer the authors question, it lacks some explaining words and/or links to documentation. Raw code snippets are not very helpful without some phrases around them. You may also find how to write a good answer very helpful. Please edit your answer - From Review – Purlin

Answer 6

=SUM(1*(ISNUMBER(SEARCH({"apple","orange"},A1)))) > 0

=SUM(1*(ISNUMBER(SEARCH($B$1:$B$2,A1)))) > 0

This just returns a TRUE/FALSE. Can be adapted to suit your needs.

Ethanol