How do I use IF AND formula in SharePoint list to return a value from one or more columns?

Question

Hi,

We use a SharePoint list to review legislative bills. Once reviewed, we indicate the status (A). When the bill is amended we review the new text and indicate the new status (B). I have columns set up for four reviews. I want to see the most current review status in the column "Current Status."

To get started, I used the following formula: =IF(AND(ISBLANK([Policy Status]),ISBLANK([Policy Status(2)]),ISBLANK([Policy Status(3)])),Status,"N/A")

But I need to capture the other columns. For example, in the second row below, the formula would say "If A and B are not blank, and C and D are blank, then populate Current Status with B. Similarly, the third row would be "If A, B, and C are not blank, and D is blank, then populate with C. The last would be "If all are not blank, then populate with D.

This is where I started but can't get it to work - =IF(AND(ISBLANK([Policy Status]),ISBLANK([Policy Status(2)]),ISBLANK([Policy Status(3)])),Status, “N/A",IF(AND(ISNOTBLANK([Status]),(ISNOTBLANK([Policy Status]), ISBLANK([Policy Status(2)]),ISBLANK([Policy Status(3)])),[Policy Status],"N/A")))

Any help is appreciated.

Thanks.

Answer 1

Dear respected Eric Levis,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

As per your description,

=IF(NOT(ISBLANK([Policy Status(3)])), [Policy Status(3)],

IF(NOT(ISBLANK([Policy Status(2)])), [Policy Status(2)],

IF(NOT(ISBLANK([Policy Status])), [Policy Status],

"N/A")))

If there is any misunderstanding, I apologize and please feel free to post back to us with more detailed information for better understanding and guiding you further.

Please understand that our initial reply may not always immediately resolve the issue. However, with your help and more detailed information, we can work together to find a solution.

Appreciate your patience and understanding and thank you for your time and cooperation.

Sincerely,

Waqas Muhammad

Answer 2

Thank you! Worked perfectly and much simpler than what I was trying to draft.

Answer 3

Hi Eric Levis,

I appreciate your kind words.

In this case, if you think my provided formula is helpful, if possible, could you please use the feedback tool to send us feedback? (photo below).

Your feedback really means a lot to the dedicated members of our community.

If there are any issues with Office apps in the future, feel free to post to this community again.

We will be there to help you.

Have a great day and stay safe 😊

Sincerely

Waqas Muhammad