HELP! - Multiple arguments in IF/AND/OR Formula

[Supernova1]

Hi,

I have created a formula for searching in a text string for certain text and returning a value based on the finding the required text.

I have 2 codes which appear in text strings in a report - 0836 = "Charge" and 0102 = "No Charge" - I want to return either of these values based on which code is contained in the text string - Each cell will have either 0836 or 0102 contained in the text string and there will be no conflicting data contained in these cells.

My original formula based on returning the "Charge" value -
=IF(SEARCH("0836",F2),"Charge","N/A")

Now I want to modify this formula to include another argument which states =IF(SEARCH("0102",F2),"No Charge","N/A")

I have tried a few combinations of IF/AND/OR and cannot seem to get both formula to work with each other, this is where i got to...

=IF(SEARCH("0836",F2),"Charge",OR(IF(SEARCH("0102",F2),"No Charge","N/A"))) - this formula will return the "Charge" value but will not return the "No Charge" value when searching for code 0102.

Any help would be greatly appreciated.

 

[KRice]

Try this:
=IF(ISNUMBER(SEARCH("0836",F2)),"Charge",IF(ISNUMBER(SEARCH("0102",F2)),"No Charge","N/A"))

 

[Supernova1]

Try this:
=IF(ISNUMBER(SEARCH("0836",F2)),"Charge",IF(ISNUMBER(SEARCH("0102",F2)),"No Charge","N/A"))

Legend!

Thanks you so much! Been scratching my head for a while with this one. Will look into the If(ISNUMBER formula to see where i was going wrong.

Thanks again!

 

[Peter_SSs]

Welcome to the MrExcel board!

Each cell will have either 0836 or 0102 contained in the text string and there will be no conflicting data contained in these cells.

If that is so, and there are no blank cells, this should suffice.
=IF(FIND("0836",F2&"0836")<LEN(F2),"Charge","No Charge")

If there could be blank cells then
=IF(F2="","",IF(FIND("0836",F2&"0836")<LEN(F2),"Charge","No Charge"))