Multiple Embedded IF & AND formula - not giving the expected result

[ChrisMP]

Hi all,

I have a formula as shown below,

Please Login or Register  to view this content.

ReqTyp & BusGrp are named ranges.

The expected result is if ReqTyp = New Customer, BusGrp = AESE and Cell E50 = GB, is to return "EU12 - SEBV Domestic Customers" / Expected result if E50 <> GB is ""EU11 - SEBV Foreign Customer".

When I test the following separately, it returns the expected result,

Please Login or Register  to view this content.

but when on the end of the full formula, it is returning "FALSE", I get the following error - and I cannot for the life of me figure out where the issue is.

IFAND.JPG

Any guidance or help here would be appreciated. Even if it is a suggestion on how to improve it!

Thank you.
Chris

[Bob Phillips]

No idea if it does what you want, but this corrects the syntax

=IF($E$12="Intercompany","Intercompany",
IF(AND(ReqTyp="New Ship-to/ZN Partners",BusGrp="AESE"),"EU30 - SEBV Ship-to",
IF(AND(ReqTyp="New Payer",BusGrp="AESE"),"EU13 - SEBV Payer",
IF(AND(ReqTyp="New Bill-to",BusGrp="AESE"),"EU14 - SEBV Bill-to",
IF(AND(ReqTyp="Amend existing Ship-to",BusGrp="AESE"),"EU30 - SEBV Ship-to",
IF(AND(ReqTyp="Amend existing Bill-to",BusGrp="AESE"),"EU14 - SEBV Bill-to",
IF(AND(ReqTyp="Amend existing Payer",BusGrp="AESE"),"EU13 - SEBV Payer",
IF(AND(ReqTyp="Amend Existing Sold-to",BusGrp="AESE"),
IF(AND(ReqTyp="New Customer",BusGrp="AESE",$E$50="GB"),"EU12 - SEBV DOMESTIC CUSTOMERS",
IF(AND(ReqTyp="New Customer",BusGrp="AESE",$E$50<>"GB"),"EU11 - SEBV Foreign Customer"))))))))))

[ChrisMP]

Hi Bob, thank you for your reply.
I have looked through your suggestion but couldn't find the syntax error in my formula.

Tried your version and I am still getting FALSE returned when ReqTyp = "New Customer". So I am going to rethink how to go about what I need.

Thanks anyway. I will close this post.

[davsth]

You will be getting a false as none of the if statements are true. Any typos or trailing spaces for example will mean that the match does not happen. if you attach an example people will solve this for you, but Bob's Formula works. I suspect the text does not match the text in your workbook

[ChrisMP]

Thanks davsth, i have inherited this formula from a previous colleague and am trying to get it to work with new criteria the company wants to use. I am now rewriting the formula from scratch, using the correction Bob provided.

I didn't mean to imply that his suggestion was not correct (Apologies if it came across that way!!)

[davsth]

I just wanted to say don't give up! People will still try to help you reach a solution. Usually if the values are completed from data validation (drop down lists) this reduces the potential for error in typing and so formulas work more reliably

[ChrisMP]

No worries and I haven't given up - just restructured the formula a little bit, as the issue seemed to be with the last IF's, so started from scratch and used:

Please Login or Register  to view this content.

now working properly.... lesson here is that sometimes it is easier to start from scratch than to try and amend an existing formula