If statement or Find statement?

Hi all,

I need help again on an If statement?!?!?!

I am working on contracts (minefield!!) I need it to bring back 100, if the account is out of Warranty but does not begin with Coffee in the account name.

Account Name Warranty Code
Coffee Y
Coffee N
Sugar N 100
Tea N 100

I have attached a spreadsheet for you to play with.

Cheers

Donna

Put this in D3:

Formula:

Please Login or Register  to view this content.

then copy down.

Hope this helps.

Pete

Excellant, both approaches work a treat.

I now need to join this with another formula that I have.

I have this formula:

=IF(ISNUMBER(SEARCH("*hotel*",G2)),"1",IF(ISNUMBER(SEARCH("*motel*",G2)),"1",IF(ISNUMBER(SEARCH("*pork*",G2)),"1",IF(ISNUMBER(SEARCH("*beef*",G2)),"1",IF(ISNUMBER(SEARCH("*chicken*",G2)),"1",IF(ISNUMBER(SEARCH("*lamb*",G2)),"1",IF(ISNUMBER(SEARCH("**",F2)),"1",IF(ISNUMBER(SEARCH("**",F2)),"","")))))

this is saying inthe cell where you are picking up "Coffee", if it says Hotel, Motel, pork, beef, chicken, lamb then bring back 1.

Is there a way of joining the 2 together, as I need this hierarchy to sort the importance of servicing by company!!

Cheers for your help

Donna

Can you explain the last two terms in your expression, i.e.:

...,IF(ISNUMBER(SEARCH("**",F2)),"1",IF(ISNUMBER(SEARCH("**",F2)),"","")))))

as these seem to be the same condition. How does G2 and F2 relate to the other cells in your sample workbook? Do you really want the text value "1" returned, or should it be the number 1?

Pete

Hi Pete,

these are just extra's waiting to be filled in. So if needed please feel free to remove them if it helps your formula to work more smoothly.


cheers

Donna

Well, you could simplify your existing formula by using this array* formula:

=IF(SUM(IF(ISNUMBER(MATCH({"*hotel*","*motel*","*pork*","*beef*","*chicken*","*lamb*"},G2,0)),1))>1,1,"")

so to combine this with the other formula you may like to do this:

=IF(COUNTIF(G2,"Coffee*")>0,"",IF(A3="N",100,IF(SUM(IF(ISNUMBER(MATCH({"*hotel*","*motel*","*pork*","*beef*","*chicken*","*lamb*"},G2,0)),1))>1,1,"")))

(also an array* formula), although I'm not sure as you haven't really defined your hierarcharcy.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>.

Hope this helps.

Pete

Hi Pete,

I'll just ahave a go with this. My Hierarchy would be to sort this with 1's at the top, then the blanks with 100's at the bottom. these will be sorted by weeknumber.

I hope this makes sense.

cheers

donna