if and or formula

Christine Menke

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

Nick Korna

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!

Nick Korna

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!

Christine Menke

Thanks@Nick Korna it works!