Seach Function with multiple Criteria

**paradise2sr** · 10-23-2020, 02:04 AM

Hi,
Current I am using below function,

Please Login or Register  to view this content.

I want to add another criteria in above function which is that >> IF(SEARCH("Sales" OR "Debit",C1334) then J1334 but if (C1334="",then 0)

i.e

Please Login or Register  to view this content.

&

Please Login or Register  to view this content.

&

Please Login or Register  to view this content.

How to combine into one.

**Glenn Kennedy** · 10-23-2020, 02:19 AM

One way:

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($E$1:$E$2,A2)))))>0,$G$1,"")

see sheet for set-up. Adjust the ranges to suit your sheet.

**paradise2sr** · 10-23-2020, 02:34 AM

Oops it not working in my case,when I modify it to

-

Please Login or Register  to view this content.

It should produce result -J1334 when there is text "Credit" i.e is you have to combine this function also

Please Login or Register  to view this content.

**Glenn Kennedy** · 10-23-2020, 02:42 AM

Ooops. See sheet again:

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH({"Sales","Debit"},A2)))))>0,B2,IF(ISNUMBER(SEARCH("Credit",A2)),-B2,0))

**paradise2sr** · 10-23-2020, 03:01 AM

ok now based on your formula,

Please Login or Register  to view this content.

It has now worked.

Thanx for the help.

**bebo021999** · 10-23-2020, 03:02 AM

Look like nagative values in only for "Credit"?

Try:

Please Login or Register  to view this content.

@Glenn, OP expected zero only if C1334="", but yours gives zero for cell with "blah blah"

**paradise2sr** · 10-23-2020, 04:36 AM

Kindly mark this thread as solved as "Thread solved" option is not displaying.

Re: Seach Function with multiple Criteria