CODEWITHSUNDEEP
excel
Ka Mok
Ka Mok

Reputation: 83

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

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".

Upvotes: 6

Views: 114083

Answers (6)

Chris
Chris

Reputation: 798

=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.

Upvotes: 0

user10675527
user10675527

Reputation: 1

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

Upvotes: 0

barry houdini
barry houdini

Reputation: 46361

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".

Upvotes: 4

Abhinav Kommula
Abhinav Kommula

Reputation: 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.

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26650

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.

Upvotes: 5

Chrismas007
Chrismas007

Reputation: 6105

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.

Upvotes: 2

Related Questions