Nested IF Statement

I am trying to write a formula that checks three different criteria in a progressive order to return the result of a supplier name.

1st - Checks New Supplier (All BUs)....if not blank would put this result.

2nd - Checks for two criteria 1) Existing Supplier is indicated and 2) Submitting Business Unit is BU A....returns BU A Supplier column if true

3rd - Checks for two criteria 1)Existing Supplier is indicated and 2) Submitting Business Unit is BU B....returns BU B Supplier column if true

4th - If none of the above are true then it would return the result of Supplier... column

My formula works for the 1st, 2nd, and 3rd check. Then does not seem to interpret the final false option to return with the info in the Supplier... column. Those cells that should be pulling something in are blank.


=IF([New Supplier (All BUs)]@row <> "", [New Supplier (All BUs)]@row, IF((AND([Supplier Type]@row = "Existing Supplier", [Submitting Business Unit]@row = "BU A")), [BU A Existing Supplier]@row, IF((AND([Supplier Type]@row = "Existing Supplier", [Submitting Business Unit]@row = "BU B")), [BU B Existing Supplier]@row, [Supplier...]@row)))


What am I doing wrong?

Answers

  • =IF([New Supplier (All BUs)]@row <> "", [New Supplier (All BUs)]@row, IF((AND([Supplier Type]@row = "Existing Supplier", [Submitting Business Unit]@row = "BU A")), [BU A Existing Supplier]@row, IF((AND([Supplier Type]@row = "Existing Supplier", [Submitting Business Unit]@row = "BU B")), [BU B Existing Supplier]@row,)), [Supplier...]@row)

    Try the above, on tablet so cannot check lovely :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!