Nested If Functions

Constance King

I have been wracking my head over nested if functions. I seem to get the opposite of what is expected with a false answer being given when it should be a true answer! I have 4 possible responses between 3 columns that are yes or no. If the first column is no then the response should be no, if it is yes and the other two columns are no it should be "Yes-Review/Negotiation in Process." If the first is no and the second is yes and the third is no the response should be "CSTIP Approved." If all are yes the response should be "DGLHA Approved." I changed the "yes" and "no" to 1 and zero so I could use the total for a row to apply the category.

0=No; 1= Yes-Review/Negotiation in Process; 2= CSTIP Approved; 3= DGLHA Approved

I pulled a table from another table to get the 22 Budget Status Snapshot Flow-thru. From that table I need to create a table my supervisor can view in Card View based on the 4 categories.

The formula before added the "total" column:

=IF(AND((VLOOKUP(Grant@row, {22BudStatusFlow}, 6, false)= 1), (VLOOKUP(Grant@row, {22BudStatusFlow}, 5, false) = 1), (VLOOKUP(Grant@row, {22BudStatusFlow}, 4, false) = 1)), "DGLHA Approved", IF(AND((VLOOKUP(Grant@row, {22BudStatusFlow}, 6, false)= 1), (VLOOKUP(Grant@row, {22BudStatusFlow}, 5, false) = 1)), "CSTIP Approved", IF((VLOOKUP(Grant@row, {22BudStatusFlow}, 6, false)= 1), "No", "Yes-Review/Negotiation", "No")))

After the total column:

=IF((VLOOKUP(Grant@row, {22BudStatusFlow}, 7, false)= 3), "DGLHA Approved", IF((VLOOKUP(Grant@row, {22BudStatusFlow}, 7, false)= 2), "CSTIP Approved", IF((VLOOKUP(Grant@row, {22BudStatusFlow}, 7, false)= 1), "Yes-Review/Negotiation", "No")))

This is the flow-thru table

This is the final table:

This would be a piece of cake in Excel, but it isn't working worth anything in Smartsheet! This should be easy!

Bassam Khalil

Hi @Constance King 

Hope you are fine, if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.

My Email for sharing : Bassam.k@mobilproject.it

Constance King

The files have been shared. Thank you for your offer.

Bassam Khalil

Hi @Constance King 

i need you to share me on the summary sheet also to create the formula as cross sheet formula

Bassam Khalil

@Constance King 

Please try the following formula and convert it to column format formula :

=IFERROR(IF(VLOOKUP(Grant@row, {22BudStatusFlow}, 7) = 0, "No", IF(VLOOKUP(Grant@row,
{22BudStatusFlow}, 7) = 1, "Yes-Review/Negotiation in Procesee", IF(VLOOKUP(Grant@row,
{22BudStatusFlow}, 7) = 2, "CSTIP Approved", IF(VLOOKUP(Grant@row, {22BudStatusFlow}, 7) = 3,
"DGLHA Approved")))), "")

the following screenshot shows the result: