Need assistance modifying a formula

[kcleere]

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,"")

Thanks in advance.

K

 

[BarryL]

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

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

 

[kcleere]

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

 

[gaz_chops]

Try

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

 

[gaz_chops]

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

=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,""))

 

[kcleere]

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

 

[gaz_chops]

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

 

[gaz_chops]

[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="colspan: 2"]adcv cvdS TRSF FVAEC[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[kcleere]

Gaz.....this one works

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

Excellent...thank you both.

 

[gaz_chops]

You're welcome.