## Forum Discussion

# Dynamic IF AND statement

- Sep 23, 2023
=IF(AND(OFFSET(E11,0,0,1,$D$4)>OFFSET(E11,0,1,1,$D$4)),1,"")

You can achieve this dynamic AND condition based on the value in Cell D4 by using an array formula with the **AND** and **INDEX** functions.

Here is how you can modify your formula to achieve this:

In Cell E12, you can use the following array formula:

**=IF(AND(E11>F11:F11+ROW(F11:F11)-ROW(F11), ROW(F11:F11)-ROW(F11)<=D4), 1, "") **

To enter an array formula, you need to press **Ctrl + Shift + Enter** instead of just **Enter** after typing the formula. This will tell Excel to treat it as an array formula. If entered correctly, Excel will automatically place curly braces **{}** around the formula.

Here is how this formula works:

**E11**is compared to a range of values from**F11**to**F11 + ROW(F11:F11) - ROW(F11)**. The**ROW**function is used to create an array of numbers based on the position of each cell relative to**F11**.**ROW(F11:F11) - ROW(F11)**calculates the relative row position within the range.**D4**is used to limit the number of conditions in the**AND**function based on the number of columns you want to consider.

So, if **D4** is set to 6, the formula will evaluate **E11 > F11:F16** and **F11:F16 > G11:H16** (up to 6 columns) and return 1 if all conditions are met.

Remember to adjust **D4** to the desired number of columns you want to include in the **AND** condition, and enter the formula as an array formula as described above. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.