Using nested IF and IF(AND) statements to display different info in calculated columns

Question

I'm trying to display some text in a calculated column depending on whether or not the input of the other columns is blank.

The columns are single-line text: [Creator] and [Organisation].

The idea is to check if these columns have text in them and then display results accordingly:

1. If [Organisation] is blank, just display the content of [Creator] (John Smith)
2. If [Creator] is blank, just display the content of [Organisation] (StackExchange)
3. If [Organisation] and [Creator] are blank, display the text "Unknown" (Unknown)
4. If [Organisation] and [Creator] both have text, display [Creator] - [Organisation] (John Smith - StackExchange)

I've managed to work out a bit of code but can't seem to get it to work together properly.

For example:

1. =IF(Organisation="",Creator,Creator&" - "&Organisation)
2. =IF(Creator="",Organisation,Creator&" - "&Organisation)
3. =IF(AND(Organisation="", Creator="", "Unknown", Creator&" - "&Organisation))
4. =Creator&" - "&Organisation

When I try to put it together like this:

=IF(Organisation="",Creator,Creator&" - "&Organisation, IF(Creator="",Organisation, Creator&" - "&Organisation, IF(AND(Organisation="", Creator="", "Unknown", Creator&" - "&Organisation)))) 

I get a technical error.

Any help would be appreciated!

Edit: I realized I was missing a , before the IF(AND but it still didn't work.

Answer 1

Use below formula:

=IF(ISBLANK([Organisation]), IF(ISBLANK([Creator]), "Unknown", [Creator]), IF(ISBLANK([Creator]), [Organisation], [Creator]&" - "&[Organisation]))

Official documentation:

1. Calculated Field Formulas