# Need assistance modifying a formula

#### kcleere

##### Active Member
I'm currently using the formula below to return the value in C4 if the text in K4 "contains the phrase" "BRANCH DEPOSIT" anywhere in the string.

I need to expand the formula to also return the C4 value if it finds "BRANCH DEPOSIT" OR "TRSF" anywhere in the K4 text string.

I have not been successful at adding the second condition.

= IF(COUNTIF(K4,"*BRANCH DEPOSIT*"),C4,"")

K

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
theres definitely a better way to do it but the below works for me.

Code:
``=IF(SEARCH("Branch Deposit",K4)=1,C4,IF(SEARCH("TSRF",K4)=1,C4,""))``

I had hopes for that one but it's returning the #value error.

Try

=IFERROR(IF(LOOKUP(1,1/(SEARCH({"trsf","branch deposit"},K4))),C4),"")

theres definitely a better way to do it but the below works for me.

Code:
``=IF(SEARCH("Branch Deposit",K4)=1,C4,IF(SEARCH("TSRF",K4)=1,C4,""))``

This won't work unless the search value is the 1st text.

Try
=IF(ISNUMBER(SEARCH("Branch Deposit",K4)),C4,IF(ISNUMBER(SEARCH("TRSF",K4)),C4,""))

Gaz...that one returns a blank in all cells regardless.

Which one? They both appear to work ok for me.

Code:
``````[TABLE="width: 576"]
<tbody>[TR]
[TD]Formulas[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formulas[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]77[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]fd  tgb  dbranch deposit posit advga[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]77[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]``````

Gaz.....this one works

= IFERROR(IF(LOOKUP(1,1/(SEARCH({"trsf","branch deposit"},K4))),C4),"")

Excellent...thank you both.

You're welcome.

Replies
5
Views
246
Replies
3
Views
3K
Replies
6
Views
380
Replies
9
Views
304
Replies
6
Views
252

1,196,073
Messages
6,013,275
Members
441,759
Latest member
ab_

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back