IF Statement for multiple data points from a main sheet and reference sheet
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
-
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")))
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!