If with multiple arguments

Hi,

I am looking for help on an IF statement where I would like the specific customer number to determine which sales team pot it goes into -

Column C has a formula that determines what team pot it goes into but I need to adjust current formula to reflect -

Code in column G starts with "CP" then goes to "INTRA"
Code in column G starts with "CS" then goes to "INTER"
If column M shows as "FHA" after the first 2 arguments then populate column C as "FHA"
IF column M is not ANY of the values as above then it should be classed as "TRADE"

Original formula assigned companies to 3 separate pots -
=IF(LEFT(G2,2)="CP","INTRA",IF(LEFT(G2,2)="CS","INTER",IF(G2<>"","TRADE","")))

Any help would be appreciated.

Thanks in advance.

Ostensibly this:

=IF(LEFT(G2,2)="CP","INTRA",IF(LEFT(G2,2)="CS","INTER",IF([@[Cost Designator on Main Ord/Network]]="FHA","FH",IF(G2<>"","TRADE",""))))

However, I don't think that's what you mean.

Can you tell us ONE cell in column C that should be returned as FHA and why?

Try:

=IF(AND(OR(LEFT(G2,2)="CP",LEFT(G2,2)="CS"),M2="FHA"),"FHA",IF(LEFT(G2,2)="CP","INTRA",IF(LEFT(G2,2)="CS","Inter","Trade")))

On the assumption that it's OK... you're welcome. If not, shout.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

You haven't fully answered my question, actually.

So WHY is row 8 FHA and NOT INTER? In what circumstances should FHA trump INTER, or any other category?

Ignore my previous post - it seems that Glenn worked it out.

Because it meets both the "either CP or CS" criterion (G) and the "FHA" criterion (M).

Yes, you worked it out - I thought that's what he was getting at, but it wasn't 100% clear (to me).

Nor to me, tbh. I had the IFs the wrong way round at first and noticed that there were no FHAs in the answers. So, I guessed that one HAD to be there.

@AliGW @Glenn thanks both of you for answering so quickly. I will see if this is what my new boss was meaning and then close this post asap, but if not then I shall come back and clarify further.

I will add rep for both for your help.

That's kind of you. Yes, leave the thread open until you are sure, but I think that's what you need.