need to link several Ifs

I need to link these if statements but have failed, can an expert out there help please.

=IF(LEFT(L2,2)="CF","SMC","")

=IF(M2="No Dupe",IF(LEFT(K2,3)="FDC","FDC",""),"")

=IF(RIGHT(K2,3)="CHX","CHX","")

=IF(M2="No Dupe",IF(LEFT(K2,2)="GB","GPS",""),"")

=IF(N6="Past",IF(LEFT(K6,1)="M","OverNight",""),"")

Thanks
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

Link how? Create just one formula that has all the IF statements? Is the logic of the order the same as in your question? If so, you can just nest the formulas in that order.

But you already use nested IFs, so you know how to do this.

What is the processing logic for each of the "false" parts of your formulas above?

cheers, teylyn
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
What do you mean with "link"? "The first true condition wins", or "concatenate all conditional results"?
The latter would e.g. result in "SMC Overnight".
0
JagwarmanAuthor Commented:
they all work separately but when I 'nest' them I am getting False and cannot figure out why

Thanks
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You just use them like this:
=IF(LEFT(L2,2)="CF","SMC","") & " " & IF(M2="No Dupe",IF(LEFT(K2,3)="FDC","FDC",""),"")

Open in new window

0
JagwarmanAuthor Commented:
Qlemo

I nested them all together like you said but get FALSE in some scenarios can you help please.

=IF(LEFT(K2,2)="CF","SMC","")&" "&IF(M2="No Dupe",IF(LEFT(K2,3)="FDC","FDC","")&" "&IF(N2="Past",IF(LEFT(K2,1)="M","OverNight","")&" "&IF(M2="No Dupe",IF(LEFT(K2,2)="GB","GPS","")&" "&IF(RIGHT(K2,3)="CHX","CHX",""),"")))

Have attached a file to help.
Nest-formula.xlsx
0
Saqib Husain, SyedEngineerCommented:
=IF(LEFT(K2,2)="CF","SMC","")&" "&IF(M2="No Dupe",IF(LEFT(K2,3)="FDC","FDC","")&" "&IF(N2="Past",IF(LEFT(K2,1)="M","OverNight","")&" "&IF(M2="No Dupe",IF(LEFT(K2,2)="GB","GPS","")&" "&IF(RIGHT(K2,3)="CHX","CHX",""),""),""),"")
0
JagwarmanAuthor Commented:
the firs 3 in the spreadsheet work but the last two return blank
0
Saqib Husain, SyedEngineerCommented:
=TRIM(IF(LEFT(K2,2)="CF","SMC","")&" "&IF(M2="No Dupe",IF(LEFT(K2,3)="FDC","FDC",""),"")&" "&IF(N2="Past",IF(LEFT(K2,1)="M","OverNight",""),"")&" "&IF(M2="No Dupe",IF(LEFT(K2,2)="GB","GPS",""),"")&" "&IF(RIGHT(K2,3)="CHX","CHX",""))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You always have to make sure you provide values for both the true and false branch of each IF, otherwise you get FALSE.
This means
= IF (A1 = A2, A2, "")

Open in new window

instead of
= IF (A1 = A2, A2)

Open in new window

0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
You have not replied to my post. I asked for clarification. How can you expect an answer if you do not reply to experts trying to help?

You have half a dozen IF statements where the FALSE part is blank.

You say you want to "link" them.

How? You have not answered that fundamental question.

You need to provide the operational logic. It is not clear if the order of the IF statements as you list them is of any significance.

In all your IF statements, the FALSE is always blank.

It is not possible to create the perfect one-cell formula unless you put into simple words what the processing logic for your data is.

You need to get a piece of paper and write out all the possible combinations and the results for each combination.

Post that, along with a sample file. In the sample file, manually mock up the expected results for a few different scenarios (i.e. type them in).

Post that file.

In general: Use your words. Nobody can guess what is in your cells and why something should be true or false. You need to "describe" that.
0
JagwarmanAuthor Commented:
teylyn,  apologies it's been a bank holiday over here for the last 4 days, but Qlemo and Saqib Husain, Syed provided details that I have been working on that has now resolved my problem.

Regards
0
JagwarmanAuthor Commented:
Qlemo, thank you for your help that put me on the road to resolving it.
0
JagwarmanAuthor Commented:
Saqib Husain, Syed, many thanks for your help with this one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.