Can this working formula be made more concise?

[Eloise T]

Can this working formula be made more concise...particularly the IFERROR SEARCH pair?

FORMULA BROKEN DOWN for ease of visualization

=IF(N7<>0,
IF(AND(H7=10,
IFERROR(SEARCH("*Cancel*",J7)>0,0),
IFERROR(SEARCH("*24*",J7)>0,0)),
20,),
"--")
SAME FORMULA TOGETHER

=IF(N7<>0,IF(AND(H7=10,IFERROR(SEARCH("*Cancel*",J7)>0,0),IFERROR(SEARCH("*24*",J7)>0,0)),20,),"--")


I want it to look more like this, but this doesn't work.

=IF(N7<>0,IF(AND(H7=10,AND(ISNUMBER(SEARCH({"*Cancel*","*24*"},J7)))),20),"--")

 

[Hui]

Did you try using Ctrl+Shift+Enter with the revised formula?

 

[Eloise T]

No. Is that necessary when there's no array?

 

[shrivallabha]

You can use ISNUMBER in place of IFERROR and drop * asterisk.
=IF(N7<>0,IF(AND(H7=10,ISNUMBER(SEARCH("Cancel",J7)),ISNUMBER(SEARCH("24",J7))),20,),"--")
If 24 will come after Cancel then you can use wildcard * (or reverse sequence if it is other way round)
=IF(N7<>0,IF(AND(H7=10,ISNUMBER(SEARCH("Cancel*24",J7))),20,),"--")

 

[Eloise T]

Thank you.

 

[shrivallabha]

24 and Cancel can be in either order.

First formula is modification of IFERROR part and works irrespective of sequence.

Edit: You can drop AND by using using following construct.
=IF(N7<>0,IF(H7*ISNUMBER(SEARCH("Cancel",J7))*ISNUMBER(SEARCH("24",J7))=10,20,),"--")

 

[bobhc]

No. Is that necessary when there's no array?

You are calling an array

An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and use the array formula to calculate a column or row of subtotals. You can also place an array formula in a single cell and then calculate a single amount. An array formula that resides in multiple cells is called a multi-cell formula, and an array formula that resides in a single cell is called a single-cell formula.


https://support.office.com/en-us/ar...ecfd5caa57c7?ui=en-US&rs=en-US&ad=US&fromAR=1

 

[Hui]

To use: {"*Cancel*","*24*"}
It's an array

 

[bobhc]

As Hui has pointed out when you see the chicken lips your formula is a full array (chicken lips at the extreme outer) or part of the formula is an array when the chicken lips are in side and just part of a bigger formula as in your case

 

[NARAYANK991]

Hi Eloise ,

You have shown 2 formulae in your initial post , as follows :

1. =IF(N7<>0,IF(AND(H7=10,IFERROR(SEARCH("*Cancel*",J7)>0,0),IFERROR(SEARCH("*24*",J7)>0,0)),20,),"--")

2. =IF(N7<>0,IF(AND(H7=10,AND(ISNUMBER(SEARCH({"*Cancel*","*24*"},J7)))),20,),"--")

Both of them will work identically , provided the second one you posted had the comma highlighted in red.

The second formula is an array formula , but does not need CTRL SHIFT ENTER , because the items within the array are what are called literals ; if you use the Evaluate Formula button to step through the evaluation of the formula , you can see that the array of values is properly evaluated.

I do not know what exactly you want the formula to do , but I tried it with two text strings in J7 :

Cancelled in 24 hours

Cancelled in 4 hours

The first text string resulted in an output of 20 , while the second gave an output of --

Narayan

 

[Eloise T]

Thanks to bobhc, Hui, and especially NARAYANK991 for your valuable insights. The more I learn about Excel, the more I realize I don't know.

Good eye NARAYANK991 for catching the missing comma!!!