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

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.

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