=IF(
Payment_Rules = Save_Only,
IF(
VLOOKUP(
F7 & $B$13,
$Y$13:$AC$429,
IF(
$Z$11 = $B$6,
2,
IF(
$B$6 = $AA$11,
3,
IF(
$B$6 = $AB$11,
4,
5
)
)
),
FALSE
) < D6,
D6,
VLOOKUP(
F7 & $B$13,
$Y$13:$AC$429,
IF(
$Z$11 = $B$6,
2,
IF(
$B$6 = $AA$11,
3,
IF(
$B$6 = $AB$11,
4,
5
)
)
),
FALSE
)
),
VLOOKUP(
F7 & $B$13,
$Y$13:$AC$429,
IF(
$Z$11 = $B$6,
2,
IF(
$B$6 = $AA$11,
3,
IF(
$B$6 = $AB$11,
4,
5
)
)
),
FALSE
)
)
So far I have nested if statement in my excel file like this and it works fine, but new columns are added to the workbook so I have to tweak this code little bit to cover new columns.(column AC:AF are added)
=IF(
Payment_Rules = Save_Only,
IF(
VLOOKUP(
F6 & $B$13,
$Y$13:$AF$429,
IF(
$Z$11 = $B$6,
2,
IF(
$B$6 = $AA$11,
3,
IF(
$B$6 = $AB$11,
4,
IF(
$B$6 = $AC$11,
5,
IF(
$B$6 = $AD$11,
6,
IF(
$B$6 = $AE$11,
7,
IF(
$B$6 = $AF$11,
8
)
)
)
)
)
)
),
FALSE
) < D5,
D5,
VLOOKUP(
F6 & $B$13,
$Y$13:$AF$429,
IF(
$Z$11 = $B$6,
2,
IF(
$B$6 = $AA$11,
3,
IF(
$B$6 = $AB$11,
4,
IF(
$B$6 = $AC$11,
5,
IF(
$B$6 = $AD$11,
6,
IF(
$B$6 = $AE$11,
7,
IF(
$B$6 = $AF$11,
8
)
)
)
)
)
)
),
FALSE
)
),
VLOOKUP(
F6 & $B$13,
$Y$13:$AC$429,
IF(
$Z$11 = $B$6,
2,
IF(
$B$6 = $AA$11,
3,
IF(
$B$6 = $AB$11,
4,
IF(
$B$6 = $AC$11,
5,
IF(
$B$6 = $AD$11,
6,
IF(
$B$6 = $AE$11,
7,
IF(
$B$6 = $AF$11,
8
)
)
)
)
)
)
),
FALSE
)
)
This is what I got for my new code, but it gives me the #REF! error. I can't figure it out what is wrong with. Since original code works fine I know there's nothing wrong with calling reference. How can I fix this error ?
Thank you!
Change all the IF() inside the VLOOKUP to MATCH:
=MATCH($B$6,$Y$11:$DD$11,0)
This will not need to be updated unless you have more column than DD.
=IF(Payment_Rules=Save_Only,IF(VLOOKUP(F7&$B$13,$Y$13:$DD$429,
MATCH($B$6,$Y$11:$DD$11,0),FALSE)<D6,D6,VLOOKUP(F7&$B$13,$Y$13:$DD$429,
MATCH($B$6,$Y$11:$DD$11,0),FALSE)),VLOOKUP(F7&$B$13,$Y$13:$DD$429,
MATCH($B$6,$Y$11:$DD$11,0),FALSE))