Nested IF statement with FIND

[BRYANTASCENSION]

Hello. I am trying to do a nested IF statement returning a different value if criteria is met. When I use one value to find, it works fine.

=IF(FIND("NET A", A2)=1, "NET ASSETS", "")

If I try adding additional criteria, it results in an error.

=IF(FIND("NET A", A2)=1, "NET ASSETS", "", IF(FIND("ALPHA FUNDS", A2)=1, "ALPHA", ""))

I know my syntax is wrong but I do not know how to fix. Any help would be greatly appreciated.

 

[Fluff]

Hi & welcome to MrExcel
Try
=IF(FIND("NET A", A2)=1, "NET ASSETS", IF(FIND("ALPHA FUNDS", A2)=1, "ALPHA", ""))

 

[Marcelo Branco]

Try

=IF(IFERROR(FIND("NET A", A2),0)=1, "NET ASSETS", IF(IFERROR(FIND("ALPHA FUNDS", A2),0)=1, "ALPHA", ""))

M.

 

[Eric W]

If you have multiple criteria, then this construction might be easier to modify:

=IFERROR(LOOKUP(2^15,FIND({"NET A","ALPHA FUNDS"},A2),{"NET ASSETS","ALPHA"}),"")

The search criteria are in the first array, the corresponding result in the second array. Note that this will find the search criteria anywhere in the A2 cell, not just the 1st position.

 

[jtakw]

Hi,

Here's another way if you Don't require the formula to be case-sensitive:

=IF(LEFT(A2,5)="NET A","NET ASSETS",IF(LEFT(A2,11)="ALPHA FUNDS","ALPHA",""))

 

[WelshWillis]

If you have multiple criteria, then this construction might be easier to modify:

=IFERROR(LOOKUP(2^15,FIND({"NET A","ALPHA FUNDS"},A2),{"NET ASSETS","ALPHA"}),"")

The search criteria are in the first array, the corresponding result in the second array. Note that this will find the search criteria anywhere in the A2 cell, not just the 1st position.

Hi there, I know this is an old post but the quoted formula has helped me 100% so thank you, but I need to know why the 2^15 inside the LOOKUP part of the formula? I've tried looking it up (pun not intended lol) but all I'm getting is it's supposed to represent 2x2 15 times? (Which equals 32,768) not sure what that has to do with the value for LOOKUP when we're looking for strings?

Thanks in advance

 

[Michael M]

the largest number of characters allowed in a cell is 32767 or 2^15-1
Having a lookup value of 2^15 therefore guarantees that that value will be larger than anything that SEARCH returns

 

[WelshWillis]

the largest number of characters allowed in a cell is 32767 or 2^15-1
Having a lookup value of 2^15 therefore guarantees that that value will be larger than anything that SEARCH returns

You sir are a legend! I can't wait to implement this into my macro!
Also thank you for your swift reply