Multiple IF Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2537977%22%20slang%3D%22en-US%22%3EMultiple%20IF%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2537977%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EHi!%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EBelow%20is%20my%20full%20if%20formula%2C%20for%20now%20Im%20just%20testing%20this%20one%3A%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%3DIF(%5BHOTEL%5D%3D%22CVS%20-%20Centralv%C3%A6rkstedet%22%2C%22%3CA%20href%3D%22mailto%3Ainfo%40t.dkk%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20ugc%20noreferrer%22%3Einfo%40t.dk%3C%2FA%3E%22%2C%22Test%22)%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EPretty%20basic%2C%20but%20it%20is%20not%20working.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EError%20message%3A%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%22%20Sorry%2C%20something%20went%20wrong%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EThe%20formula%20contains%20a%20syntax%20error%20or%20is%20not%20supported.%22%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(%5BHOTEL%5D%3D%22CVS%20-%20Centralv%C3%A6rkstedet%22%2C%22info%40t.dk%22%2C%0AIF(%5BHOTEL%5D%3D%22CBO%20-%20Borupgaard%22%2C%22info%40t.dk%22%2C%0AIF(%5BHOTEL%5D%3D%22CCP%20-%20Copenhagen%20Portside%22%2C%22info%40t.dk%22%2C%0AIF(%5BHOTEL%5D%3D%22CHC%20-%20HC%20Andersen%20Odense%22%2C%22info%40t.dk%22%2C%0AIF(%5BHOTEL%5D%3D%22CHO%20-%20Holte%22%2C%22info%40t.dk%22%2C%0AIF(%5BHOTEL%5D%3D%22CAL%20-%20Hvide%20Hus%20Aalborg%22%2C%22info%40t.dk%22%2C%0AIF(%5BHOTEL%5D%3D%22CKP%20-%20Kellers%20Park%22%2C%22info%40t.dk%22%2C%0AIF(%5BHOTEL%5D%3D%22CKL%20-%20Klarskovgaard%22%2C%22info%40t.dk%22%2C%0AIF(%5BHOTEL%5D%3D%22CKO%20-%20Kolding%22%2C%22info%40t.dk%22%2C%0AIF(%5BHOTEL%5D%3D%22CKG%20-%20Kongerbrogaarden%22%2C%22info%40t.dk%22%2C%0AIF(%5BHOTEL%5D%3D%22CKS%20-%20K%C3%B8ge%20Strand%22%2C%22info%40t.dk%22%2C%0AIF(%5BHOTEL%5D%3D%22CMI%20-%20Middelfart%22%2C%22info%40t.dk%22%2C%0AIF(%5BHOTEL%5D%3D%22CRE%20-%20Rebild%20Bakker%22%2C%22info%40t.dkm%22%2C%0AIF(%5BHOTEL%5D%3D%22CRO%20-%20Roskilde%22%2C%22info%40t.dk%22%2C%0AIF(%5BHOTEL%5D%3D%22CSO%20-%20Sor%C3%B8%22%2C%22info%40t.dk%22%2C%0AIF(%5BHOTEL%5D%3D%22CAA%20-%20Aarhus%22%2C%22info%40t.dk%22))))))))))))))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2537977%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMicrosoft%20Lists%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2538359%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2538359%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1101291%22%20target%3D%22_blank%22%3E%40asra1989%3C%2FA%3E%26nbsp%3BQuestion%3A%20What%20kind%20of%20column%20is%20%5BHOTEL%5D%20%3F%20Lookup%3F%20Choice%3F%20Or%20just%20Text%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2538400%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20IF%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2538400%22%20slang%3D%22en-US%22%3E%3CP%3EWhile%20I%20couldn't%20figure%20out%20the%20exact%20issue%2C%20I%20received%20the%20same%20error%20when%20I%20copy%2Fpaste%20your%20test%20formula.%20I%20erased%20it%20and%20started%20building%20it%20one%20element%20at%20a%20time.%20I%20started%20with%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(%5BHOTEL%5D%3D%22%22%2C%221%22%2C%222%22)%3CBR%20%2F%3E%3CBR%20%2F%3Eand%20substitute%20one%20string%20at%20a%20time%2C%20testing%20between%20each%20change.%20I%20ended%20up%20with%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(%5BHOTEL%5D%3D%22CVS%20-%20Centralv%C3%A6rkstedet%22%2C%22info%40t.dk%22%2C%22Test%22)%3CBR%20%2F%3E%3CBR%20%2F%3Ewhich%20works.%20It%20looks%20the%20same%2C%20but%20something%20is%20different.%20(I%20suspect%20a%20hidden%20invalid%20character.)%20Just%20keep%20building%20the%20formula%20piece%20by%20piece.%20It's%20slow%20and%20frustrating%20sometimes%2C%20but%20without%20a%20better%20error%20message%20from%20SharePoint%2C%20there%20is%20not%20much%20else%20you%20can%20do.%20It%20will%20either%20work%2C%20or%20you%20will%20find%20the%20exact%20change%20that%20is%20causing%20the%20issue.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20this%20helps%2C%20please%20consider%20marking%20it%20as%20the%20solution.%20Thanks%20and%20good%20luck!%3CBR%20%2F%3E%3CBR%20%2F%3EDon%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi!

Below is my full if formula, for now Im just testing this one:

=IF([HOTEL]="CVS - Centralværkstedet","info@t.dk","Test")

Pretty basic, but it is not working.

 

Error message:

 

" Sorry, something went wrong

The formula contains a syntax error or is not supported."

 

 

 

=IF([HOTEL]="CVS - Centralværkstedet","info@t.dk",
IF([HOTEL]="CBO - Borupgaard","info@t.dk",
IF([HOTEL]="CCP - Copenhagen Portside","info@t.dk",
IF([HOTEL]="CHC - HC Andersen Odense","info@t.dk",
IF([HOTEL]="CHO - Holte","info@t.dk",
IF([HOTEL]="CAL - Hvide Hus Aalborg","info@t.dk",
IF([HOTEL]="CKP - Kellers Park","info@t.dk",
IF([HOTEL]="CKL - Klarskovgaard","info@t.dk",
IF([HOTEL]="CKO - Kolding","info@t.dk",
IF([HOTEL]="CKG - Kongerbrogaarden","info@t.dk",
IF([HOTEL]="CKS - Køge Strand","info@t.dk",
IF([HOTEL]="CMI - Middelfart","info@t.dk",
IF([HOTEL]="CRE - Rebild Bakker","info@t.dkm",
IF([HOTEL]="CRO - Roskilde","info@t.dk",
IF([HOTEL]="CSO - Sorø","info@t.dk",
IF([HOTEL]="CAA - Aarhus","info@t.dk"))))))))))))))))

 

8 Replies

@asra1989 

I think you simply nested too many IFs. Isn't 7 the maximum?

 

What kind of column is [HOTEL] ? Text, Choice, Lookup?

While I couldn't figure out the exact issue, I received the same error when I copy/paste your test formula. I erased it and started building it one element at a time. I started with

=IF([HOTEL]="","1","2")

and substitute one string at a time, testing between each change. I ended up with

=IF([HOTEL]="CVS - Centralværkstedet","info@t.dk","Test")

which works. It looks the same, but something is different. (I suspect a hidden invalid character.) Just keep building the formula piece by piece. It's slow and frustrating sometimes, but without a better error message from SharePoint, there is not much else you can do. It will either work, or you will find the exact change that is causing the issue.

If this helps, please consider marking it as the solution. Thanks and good luck!

Don

Hi,

I am trying one single line.
Does it work for you if you try with letters/alphabeth?
@Bernd71 also has a valid point for the full formula.

https://support.microsoft.com/en-us/office/introduction-to-sharepoint-formulas-and-functions-94e1b4c...

has the following statement:

Nesting level limits A formula can contain up to eight levels of nested functions.

It doesn't explain the issue in the test formula, but you will need to make changes based on number of nested formulas. It will take multiple calculated fields to reach the final result.
Basic approach I would take is maximize the nesting in the first calculated column, with the final result being either the custom result from one of the nested IFs, or the original HOTEL if none match. Then in the next calculated column, the initial IF will test IF [Calc1]=[HOTEL]. If they match, add additional IFs. If they don't match, return the value of Calc1.
Yes, I showed the formula that is working for me. It looks identical to your formula, but I also get the same error if I just copy/paste your formula. I think there is a hidden character causing the error, but rather than troubleshoot that, just recreate the formula one piece at a time, which will hopefully bypass the bad character.
Sadly this nesting limit is not good for me.

I am now trying to use lookup columns instead. But I need Power Automate to Insert the Microsoft Form entry into that lookup-column. That is also an issue now.

Thanks for your help.

@Bernd71 

 

Jesus, what an annoying limitation :(

The solution was actually like this.

https://i.stack.imgur.com/YCALp.png