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

Jen_M

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

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")))

https://app.smartsheet.com/b/publish?EQBCT=de67aa5494e94193995b79ec2651253e

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

@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”))))))))))))