if and or formula

Options

Hi I try to build a formula for 1st row in yellow. I have 2 drop down filters and I would like to get the deadline shown in deadline1 as a result of the lead time and TLT combination.

e.g. Lead time 60 and TLT 105 give me result deadline 15/11/23

this is working with

=IF(AND([Lead Time]1 = "60", [TLT]1 = "105"), [Deadline]6)

But now I'm not sure how to integrate the other combinations in the formula.

Thanks


Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Christine Menke,

    You can use INDEX COLLECT for this.

    Rearranging your data slightly to make the ranges a bit easier:

    =INDEX(COLLECT(Date1:Date9, [Lead Time]1:[Lead Time]9, [Lead Time]@row, TLT1:TLT9, TLT@row), 1)

    You can obviously change these to reflect your row numbers in place of mine, but this will save you having to produced a gigantic nested IF(AND) formula.

    Hope this helps, let us know if you have any problems/questions though!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!