Help with Excel Formula String

[Segurde]

Hello,

I am trying to attempt the following with this formula but am getting errrors. Any idea what the errors might be? Thank you!

=IF(othertab!P4="Cents Off",IF(ISNUMBER(SEARCH("CE",G5)),"TRUE",IF(othertab!P4="Free",IF (ISNUMBER(SEARCH("NE",G5)),"TRUE", IF(othertab!P4="Percent Off",IF (ISNUMBER(SEARCH("PE",G5)),"TRUE", IF(othertab!P4="Price Point",IF (ISNUMBER(SEARCH("NE",G5)),"TRUE","FALSE")),"TRUE")))


1) If P4 in the othertab reads “Cents Off” search for “CE” in G5 of the current tab, if this checks out return TRUE, if it does not return FALSE. This needs to repeat for all of the following scenarios:

Cents Off = CE

Free = NE
Net Each = NE

Percent Off = PE

 

[Luke M]

That's a lot of nested functions! Since you only have two possible outcomes, and they're Boolean, you can do this all with OR/AND checks.
=OR(AND(othertab!P4="Cents Off",ISNUMBER(SEARCH("CE",G5))),
AND(OR(othertab!P4="Free",othertab!P4="Net Each"),ISNUMBER(SEARCH("NE",G5))),
AND(othertab!P4="Percent Off",ISNUMBER(SEARCH("PE",G5))))

Note how the overall OR lets you check each possible pair, and the ANDs contain the sets of criteria.

Or, if you want to get really slick:
=SUMPRODUCT((othertab!P4={"Cents Off","Free","Net Each","Percent Off"})*
ISNUMBER(SEARCH({"CE","NE","NE","PE"},G5)))=1
This takes the least amount of cell references, and is easier to read.

 

[Segurde]

Thank you for your help! Something is up though... when I hit enter to run the formula, I get a browse window labeled Update Values: File.

Any thoughts?

 

[Debraj]

Hi Segurde

Do you really have any sheet named "OtherTab"..
If No... then change the Formula ..with the actual name of the sheet .. where those words you want to lookup..

 

[Segurde]

Hello,

I did that but still got the window. I was able to figure it out however. I flipped the sheets... put the formula in the reference sheet and flipped the formula reference.

Somehow that worked. Thanks for all your help!