more than 7 nested IF(AND) statements in formula - VBA? **NEWBIE**
 

Hello all -
I see that I have exceeded the limit of nested IF statements that Excel
allows.

I have read mulitudes of previous posts trying to the answer I need. I
can't find anything for statements with an IF(AND) function however. I
have read about VLOOKUPS and VBA codes, but can't figure out the right
tweak to make them work.

Here is the original formula that I wanted to work:

=IF(AND(M19=G17,J14J17),J17,(IF(AND(M19=G18,J14J 18),J18,(IF(AND(M19=G19,J14J19),J19,(IF(AND(M19=G 20,J14J20),J20,(IF(AND(M19=G21,J14J21),J21,(IF(A ND(M19=G22,J14J22),J22,(IF(AND(M19=G23,J14J23),J 23,(IF(AND(M19=G24,J14J24),J24,(IF(AND(M19=G25,J1 4J25),J25,J14)))))))))))))))))

Can anyone please assist in some VBA code or another way to get this
formula to work?

Thanks for your help!
Dan

more than 7 nested IF(AND) statements in formula - VBA? **NEWBIE**
 

If I understand your formula correctly, I think it can be reduced to:

=MIN(J14,OFFSET(J17,MATCH(M19,G17:G25,0)-1,0))

The MATCH() function replaces each of your (M19=G17, M19=G18, ...)
conditions and then returns the value in column J for the match in
column G and then uses its value only if it is less than J14...

more than 7 nested IF(AND) statements in formula - VBA? **NEWBIE**
 

Very cool, thanks! Just for my knowledge...what does the MIN and
OFFSET functions do?

Dan

more than 7 nested IF(AND) statements in formula - VBA? **NEWBIE**
 

MIN() says to take the minimum value of all arguments.

OFFSET() says to pick up the data offset from the cell reference
specified. For example, OFFSET(A1,2,1) says to get the value that is
two rows below and 1 column to the right of cell A1.