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

[Dan]

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,J14>J17),J17,(IF(AND(M19=G18,J14>J18),J18,(IF(AND(M19=G19,J14>J19),J19,(IF(AND(M19=G20,J14>J20),J20,(IF(AND(M19=G21,J14>J21),J21,(IF(AND(M19=G22,J14>J22),J22,(IF(AND(M19=G23,J14>J23),J23,(IF(AND(M19=G24,J14>J24),J24,(IF(AND(M19=G25,J14>J25),J25,J14)))))))))))))))))

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

Thanks for your help!
Dan

 

[Randy Harmelink]

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...

 

[Dan]

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

Dan

 

[Randy Harmelink]

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.