IF Statement for multiple data points from a main sheet and reference sheet

Jen_M
Jen_M ✭✭
edited 05/16/24 in Formulas and Functions

Hello,

I need help with a IF statement that I am trying to have work in a main sheet, that looks at other cells in the main sheet and reference sheets. The formula I have returns UNPARSEABLE,

=IF({Eligibility Review Status}=”Ineligible”, “11. Ineligible”, (IF([Date of Withdrawal]@row >0, “12. Withdrawn”,(IF([Date Closeout Approved]@row >0, “10. Complete”,(IF([Date Disbursement Issued to Applicant]@row>0, “09. Pending Closeout”,(IF([Date Award Signing Event Scheduled]@row >0, “08. Pending Disbursement”,(IF([Date Approved by Agency]@row>0, “07. Approved and Pending Signing Event”,(IF([Date Approved by Grant Specialist]@row >0, “06. Request for Agency Approval”,(IF([Date Approved by Lead Inspector]@row >0, “05. Final Eligibility Review”,(IF([Date of Prelim Eligibility Review]@row >0, “04. Residential Property Assessment”,(IF((AND([Completed Application]@row=1, [Photo ID]@row =1, [Proof of Primary Residency]@row=1, [Proof of Legal Residency]@row=1, [Proof of Ownership]@row=1, [Income Information]@row =1)), “03. Initial Eligibility Review”,(IF([Date Application Received]@row >0, “02. Application Received”,“01. Application Pending”)))))))))))))))))))

To add, the first IF statement looking at Eligibility Review Status is looking at a reference sheet, so I do think the formula needs to be modified to include INDEX/MATCH, but I am getting turned around on how to correctly write out the formula. I have seen other posts similar to my question, but I am confused at this point.

The first part I am trying to have the formula reference another sheet and if the status is "Ineligible", the status would flip to "11. Ineligible", if that criteria is not met, it would subsequently follow the above to update the status to Withdrawn, Complete, and so on (as spelled out above). Hopefully that adds more clarity into what I am trying to achieve.

I have started with =INDEX({Eligibility Review Status}, MATCH([Applicant ID]@row, {EligibilityAppID}, 0)) for the first part of the formula. How would I combine this statement with the IF statements above? Is this the right course of action?

Thanks,

Jen

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @Jen_M

    I have yet to get into the details of your formula, but the first thing I noticed is that you need the correct format for nested IF statements.

    Your format:

    IF(condition1, result1, (IF(condition2, result2, (IF(condition3, result3, default_result)))

    The ( in front of second and third IF are not necessary.

    Correct format:

    IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, default_result)))

    or in the demo sheet below, the formula is;

    =IF([condition1]@row, [result1]@row, IF([condition2]@row, [result2]@row, IF([condition3]@row, [result3]@row, "N/A")))

  • Jen_M
    Jen_M ✭✭

    Hi @jmyzk_cloudsmart_jp. I tried your suggested edit with the first part of the formula for Ineligible and Withdrawn but its not exactly working. For the first part its looking if the file is has been flagged as ineligible, its ineligible, unless there is a date entered for Withdrawn. Should this be and IF OR statement?

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/17/24

    @Jen_M

    See if this works for you. I went through and cleaned up your current formula. As well as added the index match to it.

    =IF(INDEX({Eligibility Review Status}, MATCH([Applicant ID]@row, {EligibilityAppID}, 0)=”Ineligible”, “11. Ineligible”, IF([Date of Withdrawal]@row >0, “12. Withdrawn”,IF([Date Closeout Approved]@row >0, “10. Complete”,IF([Date Disbursement Issued to Applicant]@row>0, “09. Pending Closeout”,IF([Date Award Signing Event Scheduled]@row >0, “08. Pending Disbursement”,IF([Date Approved by Agency]@row>0, “07. Approved and Pending Signing Event”,IF([Date Approved by Grant Specialist]@row >0, “06. Request for Agency Approval”,IF([Date Approved by Lead Inspector]@row >0, “05. Final Eligibility Review”,IF([Date of Prelim Eligibility Review]@row >0, “04. Residential Property Assessment”,IF(AND([Completed Application]@row=1, [Photo ID]@row =1, [Proof of Primary Residency]@row=1, [Proof of Legal Residency]@row=1, [Proof of Ownership]@row=1, [Income Information]@row =1), “03. Initial Eligibility Review”,IF([Date Application Received]@row >0, “02. Application Received”,“01. Application Pending”))))))))))))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!