Formula help PLEASE! Three fold IF formula

Tracey Tume
Tracey Tume ✭✭✭✭✭
edited 09/02/22 in Formulas and Functions

Hi

This is not working for me hoping someone can help me please?

This fr

=IF(NOT(CONTAINS("Fit", Description@row)), (OR(IF(AND(Description@row = "To Aim spraypainters", [Install/Pickup date]@row < TODAY()), [Install/Pickup date]@row), IF(CONTAINS("Install", Description@row), (VLOOKUP([Job Number]@row, {Factory QC Signoff Range 1}, 7, false))))), " ")

I am trying to look at the lines that do not contain "Fit" in the description column. Then that the date in the cell is determined by two groups of choices:

1) Description = To Aim spraypainters AND the date or Install/Pickup date is less than today - to then make the date = the Install/Pickup date

2) Description contains Install to then look up another cell in another sheet (and match the Job Number) then put in the date from the 7th column.

IF neither of the two options above match to then make the date cell blank.

Any help would be gratefully received.

Thank you Tracey

Best Answer

  • Tracey Tume
    Tracey Tume ✭✭✭✭✭
    Answer ✓

    Thank you!

    I had to tweak this =if(find("Fit",description@row)>0,"" to =if(find("Fit",description@row)=0 and it seemed to work.

    Very grateful and you exposed me to a new formula option with the FIND so doubly good.

    Have a great day.

    Tracey

Answers

  • L_123
    L_123 ✭✭✭✭✭✭

    =if(find("Fit",description@row)>0,"",if(and(description@row = "To Aim spraypainters",[Install/Pickup date]@row <= today()),[Install/Pickup date]@row,if(find("install",description@row)>0,VLOOKUP([Job Number]@row, {Factory QC Signoff Range 1}, 7, false)

  • Tracey Tume
    Tracey Tume ✭✭✭✭✭
    Answer ✓

    Thank you!

    I had to tweak this =if(find("Fit",description@row)>0,"" to =if(find("Fit",description@row)=0 and it seemed to work.

    Very grateful and you exposed me to a new formula option with the FIND so doubly good.

    Have a great day.

    Tracey

  • L_123
    L_123 ✭✭✭✭✭✭

    whoops, my bad. glad you figured it out.


    I'd also recommend looking into using index/match instead of vlookup. in almost all cases, it is much more efficient on the backend, and won't slow your sheets down as much, and when you have the concept down it is much clearer to read and write.

  • Tracey Tume
    Tracey Tume ✭✭✭✭✭

    Okay thank you I will adjust to that to help with speed.

    Regards

    Tracey

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!