EXCEL向嵌套的if语句中添加新列

Question

=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
)

)

到目前为止，我已经在excel文件中嵌套了if语句，它工作得很好，但是新的列被添加到工作簿中，所以我不得不稍微修改这段代码，以覆盖新的列。

=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
)

)

这是我的新代码得到的，但它给了我#REF！错误。我搞不懂到底是怎么回事。由于原始代码工作正常，我知道调用引用没有什么问题。如何纠正此错误？

谢谢!

Answer 1

更改VLOOKUP中的所有IF()以匹配：

=MATCH($B$6,$Y$11:$DD$11,0)

这将不需要更新，除非您有比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))