Unknown Formula Error

S

Shaunna H.

=IF((OR((ISNUMBER(SEARCH(LEFT(H2,5)&"+",X2))),((ISNUMBER(SEARCH(RIGHT(H2,5)&"+",X2)))),
((ISNUMBER(SEARCH("+"&LEFT(H2,5),X2)))),((ISNUMBER(SEARCH("+"&RIGHT(H2,5),X2)))))),"C2",IF((OR(X2=(LEFT(H2,5))),(X2=(RIGHT(H2,5))),(ISNUMBER(SEARCH(LEFT(H2,5),X2))),(ISNUMBER(SEARCH(RIGHT(H2,5),X2))),(AND(H2="",X2=""))),"C",IF(H2="","FP","FN")))

I am trying to evaluate a very complicated scoring formula, as seen above.

Example:

If cell H2 contains "abc12 def34" and cell X2 contains "abc12+", "def34+",
"+abc12", or "+def34", then Z2=C2

If cell X2 contains "abc12" AND "def34", OR X2 is blank AND H2 is blank,
then Z2=C

If H2 is blank Z2=FP

Else Z2=FN
 
T

T. Valko

Can you explain *in words* what you're trying to do?

What's the significance of the numbers and the location of the "+" sign?
 
G

Greg Wilson

There were a lot of unecessary parentheses, which I removed. There was an
error with the parentheses of the 2nd Or function which I fixed. Otherwise,
the below is the same as what you posted:

=IF(OR(ISNUMBER(SEARCH(LEFT(H2,5)&"+",X2)),ISNUMBER(SEARCH(RIGHT(H2,5)&"+",X2)),
ISNUMBER(SEARCH("+"&LEFT(H2,5),X2)),ISNUMBER(SEARCH("+"&RIGHT(H2,5),X2))),"C2",IF(OR(X2=LEFT(H2,5),X2=RIGHT(H2,5),ISNUMBER(SEARCH(LEFT(H2,5),X2)),ISNUMBER(SEARCH(RIGHT(H2,5),X2)),AND(H2="",X2="")),"C",IF(H2="","FP","FN")))

Greg
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top