Nested IF AND formula

[livmun]

Excel accepts it but the results aren't correct, where it should return one of my 'if true' values, it just returns #VALUE!. I thought it would be pretty simple but I must be overlooking something. Please help.

First of all I am looking at whether or not E2 contains "No – 2014-15 non compliant". Out of those results I want it to return "CAFA 2-Initial Assessment for compliance (will exceed 18 years in 2014-15)" if there's a V in A2 or "CAFA 2-Initial Assessment for compliance (will exceed 18 years in 2014-15) & concurrent with DE" if there's DET in A2.
Here's my attempt:

=IF(AND(AI2="No – 2014-15 non compliant",ISNUMBER(SEARCH("V",A2)),"CAFA 2-Initial Assessment for compliance (will exceed 18 years in 2014-15)"),IF(AND(AI2="No – 2014-15 non compliant",ISNUMBER(SEARCH("DET",A2))),"CAFA 2-Initial Assessment for compliance (will exceed 18 years in 2014-15) & concurrent with DE",""))

 

[oldbrewer]

please restate your problem using simple example

if E2="abc" and A2 contains a "v" anywhere then "bingo" or if E2 = "abc" and A2 contains a DET" anywhere...........

 

[livmun]

Sorry, I gave some incorrect references there:
First of all I am looking at whether or not AI2 contains "No – 2014-15 non compliant". Out of those results I want it to return "CAFA 2-Initial Assessment for compliance (will exceed 18 years in 2014-15)" if there's a V in A2 or "CAFA 2-Initial Assessment for compliance (will exceed 18 years in 2014-15) & concurrent with DE" if there's DET in A2.
Here's my attempt:

=IF(AND(AI2="No – 2014-15 non compliant",ISNUMBER(SEARCH("V",A2)),"CAFA 2-Initial Assessment for compliance (will exceed 18 years in 2014-15)"),IF(AND(AI2="No – 2014-15 non compliant",ISNUMBER(SEARCH("DET",A2))),"CAFA 2-Initial Assessment for compliance (will exceed 18 years in 2014-15) & concurrent with DE",""))

In simpler terms, I want to return Bingo1 if both of the following conditions are met: A2 contains V and AI contains Yes. Or, I want to return Bingo2 if both of the following conditions are met: A2 contains DET and AI contains Yes
Many thanks​

 

[oldbrewer]

=if(a2="v",if(a1="yes","bingo1","lose"),if(a2="det",if(a1="yes","bingo2","lose"),"lose")

 

[oldbrewer]

that is the structure of the if statements

you can insert the isnumber search clever stuff...

 

[livmun]

Thanks for your efforts and reply. I've managed to get it to work after all. It was due to a few erroneous brackets:

IF(ISNA(AI2),"",IF(AND(AI2="No – 2014-15 non compliant",ISNUMBER(SEARCH("V",A2))),"CAFA 2-Initial Assessment for compliance (will exceed 18 years in 2014-15)",IF(AND(AI2="No – 2014-15 non compliant",ISNUMBER(SEARCH("DET",A2))),"CAFA 2-Initial Assessment for compliance (will exceed 18 years in 2014-15) & concurrent with DE","")))

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[oldbrewer]

all's well that ends well........