=IF(AND(OR(IFERROR(SEARCH(“Beguiling Influence”,Attacks_Defenses_notes,1),0)>0,IFERROR(SEARCH(“Beguiling Influence”,Features_Abilities_notes_1,1),0)>0,IFERROR(SEARCH(“Beguiling Influence”,Features_Abilities_notes_2,1),0)>0),OR(AND(Z10=”Warlock”,AH10>1),AND(Z11=”Warlock”,AH11>1),AND(Z12=”Warlock”,AH12>1),AND(Z13=”Warlock”,AH13>1))),2,0)

The answer should be 2 for the current spreadsheet but it is only showing 0. Information from the spreadsheet Warlock 2 with Beguiling Influence in the FEATURES_ABILTIES_NOTES_1 area.

Each portion of the formula reports the right information & if I select the whole formula & hit InsertFunction it shows that it should be returning 2, but the actual formula does not.

(Edited down to the portion not working.)

### Like this:

Like Loading...

I had to break the formula in to components, and then substitute a countif command for the IFERROR with SEARCH command, because I have learned that SEARCH returns a location within a cell, as a result it doesn’t work on ranges. Here are the three formulas I used to isolate and get the data I wanted.

1. =IF(OR(COUNTIF(Attacks_Defenses_notes,”*”&”Beguiling Influence”&”*”)>0,COUNTIF(Features_Abilities_notes_1,”Beguiling Influence”)>0,COUNTIF(Features_Abilities_notes_2,”Beguiling Influence”)>0),”TRUE”,”false”)

2. =IF(OR(AND(Z10=”Warlock”,AH10>1),AND(Z11=”Warlock”,AH11>1),AND(Z12=”Warlock”,AH12>1),AND(Z13=”Warlock”,AH13>1)),”TRUE”,”false”)

3. =IF(AND(BQ18=”TRUE”,BR18=”TRUE”),2,0)