Forum Discussion

vaphaet's avatar
vaphaet
Copper Contributor
May 04, 2023

Named table reference not working after expanding the named table with more rows

I have multiple named tables across multiple sheets.

 

When I created the below function, it worked as intended. Then I inserted extra row to the referenced (Havi_Bér) named table. It visually shows that the new rows are in the named table as well as in the name manager, the range associated with the named table.

 

However if the below function needs to find data that is in the newly inserted rows, it returns an error as if the expanded part of the range is not part of the referenced named table.

 

=CONCAT(IF(INDEX(Havi_Bér[Szabadság];XLOOKUP($C$1;Havi_Bér_Dátum;0;2)*XLOOKUP([@Név];Havi_Bér[Munkavállaló];0;2))>0;INDEX(Havi_Bér[Szabadság];XLOOKUP($C$1;Havi_Bér_Dátum;0;2)*XLOOKUP([@Név];Havi_Bér[Munkavállaló];0;2));"");IF(INDEX(Havi_Bér[Szabadság];XLOOKUP($C$1;Havi_Bér_Dátum;0;2)*XLOOKUP([@Név];Havi_Bér[Munkavállaló];0;2))>0;" nap, ";"");IF(INDEX(Havi_Bér[Apanap];XLOOKUP($C$1;Havi_Bér[Kezdete];0;2)*XLOOKUP([@Név];Havi_Bér[Munkavállaló];0;2))>0;INDEX(Havi_Bér[Apanap];XLOOKUP($C$1;Havi_Bér_Dátum;0;2)*XLOOKUP([@Név];Havi_Bér[Munkavállaló];0;2));"");IF(INDEX(Havi_Bér[Apanap];XLOOKUP($C$1;Havi_Bér[Kezdete];0;2)*XLOOKUP([@Név];Havi_Bér[Munkavállaló];0;2))>0;" apanap";""))

 

What exotic inconsistent behavior is excel manifesting here, and how can I resolve this issue?

I really want avoid naming all the individual columns with named ranges in the table.

  • vaphaet 

    Have no idea why, but you use binary search in XMATCH() /4th parameter/ which assumes sorting of data source. Try to sort Havi_Bér[Munkavállaló] in ascending order and you have totally different result.

    I'd use default search mode like

    =CONCATENATE(
        IF(
            INDEX(
                Havi_Bér[Szabadság],
                XMATCH($C$1, Havi_Bér[Kezdete]) *
                    XMATCH([@Név], Havi_Bér[Munkavállaló])
            ) > 0,
            INDEX(
                Havi_Bér[Szabadság],
                XMATCH($C$1, Havi_Bér[Kezdete]) *
                    XMATCH([@Név], Havi_Bér[Munkavállaló])
            ),
            ""
        ),
        IF(
            INDEX(
                Havi_Bér[Szabadság],
                XMATCH($C$1, Havi_Bér[Kezdete]) *
                    XMATCH([@Név], Havi_Bér[Munkavállaló])
            ) > 0,
            " nap, ",
            ""
        ),
        IF(
            INDEX(
                Havi_Bér[Apanap],
                XMATCH($C$1, Havi_Bér[Kezdete]) *
                    XMATCH([@Név], Havi_Bér[Munkavállaló])
            ) > 0,
            INDEX(
                Havi_Bér[Apanap],
                XMATCH($C$1, Havi_Bér[Kezdete]) *
                    XMATCH([@Név], Havi_Bér[Munkavállaló])
            ),
            ""
        ),
        IF(
            INDEX(
                Havi_Bér[Apanap],
                XMATCH($C$1, Havi_Bér[Kezdete]) *
                    XMATCH([@Név], Havi_Bér[Munkavállaló])
            ) > 0,
            " apanap",
            ""
        )
    )

    The same for Fizetendő, like

    =XLOOKUP(
        [@Munkavállaló],
        Munkavállalók_Táblája[Név],
        Munkavállalók_Táblája[Alapbér],
        "Munkavállaló neve nem található a Munkavállalók_Táblája tábla Név oszlopában"
    ) *
        (
            1 -
                (
                    [@[Fizetés nélküli nap]] + [@Apanap] * 0.6 +
                        [@[Beteg szabadság]] * 0.3
                ) / [@[Tárgyhavi Munkanap]]
        ) + [@Túlóra] + [@[Utazási Támogatás]] - [@[Egyéb csökkentő]] +
        [@[Egyéb növelő]]

    Not sure about other formulae, but you have to be careful with binary search.

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Can you post an anonymized Excel file with that table, with just enough data to demonstrate the issue?
    • vaphaet's avatar
      vaphaet
      Copper Contributor

      JKPieterse Here is an anonimized version. In the process of changing the names, the error recreated itself in in an other table too. When I click on the errors, it says it can't find the value in the referenced table, when it is infact there.

       

      I'm utterly confused. I have copy-pasted the names...

       

      The table headers are in hungarian, I hope it is not an impedement for you to decypher the table, also I hope the functions translate to english. In the hungarian version of excel I have to use ";" instead of "," to separate the function parameters. Please give feedback whether excel can handle the cross-language version environment.

       

      Thanks...

       

      Peter

      • vaphaet 

        Have no idea why, but you use binary search in XMATCH() /4th parameter/ which assumes sorting of data source. Try to sort Havi_Bér[Munkavállaló] in ascending order and you have totally different result.

        I'd use default search mode like

        =CONCATENATE(
            IF(
                INDEX(
                    Havi_Bér[Szabadság],
                    XMATCH($C$1, Havi_Bér[Kezdete]) *
                        XMATCH([@Név], Havi_Bér[Munkavállaló])
                ) > 0,
                INDEX(
                    Havi_Bér[Szabadság],
                    XMATCH($C$1, Havi_Bér[Kezdete]) *
                        XMATCH([@Név], Havi_Bér[Munkavállaló])
                ),
                ""
            ),
            IF(
                INDEX(
                    Havi_Bér[Szabadság],
                    XMATCH($C$1, Havi_Bér[Kezdete]) *
                        XMATCH([@Név], Havi_Bér[Munkavállaló])
                ) > 0,
                " nap, ",
                ""
            ),
            IF(
                INDEX(
                    Havi_Bér[Apanap],
                    XMATCH($C$1, Havi_Bér[Kezdete]) *
                        XMATCH([@Név], Havi_Bér[Munkavállaló])
                ) > 0,
                INDEX(
                    Havi_Bér[Apanap],
                    XMATCH($C$1, Havi_Bér[Kezdete]) *
                        XMATCH([@Név], Havi_Bér[Munkavállaló])
                ),
                ""
            ),
            IF(
                INDEX(
                    Havi_Bér[Apanap],
                    XMATCH($C$1, Havi_Bér[Kezdete]) *
                        XMATCH([@Név], Havi_Bér[Munkavállaló])
                ) > 0,
                " apanap",
                ""
            )
        )

        The same for Fizetendő, like

        =XLOOKUP(
            [@Munkavállaló],
            Munkavállalók_Táblája[Név],
            Munkavállalók_Táblája[Alapbér],
            "Munkavállaló neve nem található a Munkavállalók_Táblája tábla Név oszlopában"
        ) *
            (
                1 -
                    (
                        [@[Fizetés nélküli nap]] + [@Apanap] * 0.6 +
                            [@[Beteg szabadság]] * 0.3
                    ) / [@[Tárgyhavi Munkanap]]
            ) + [@Túlóra] + [@[Utazási Támogatás]] - [@[Egyéb csökkentő]] +
            [@[Egyéb növelő]]

        Not sure about other formulae, but you have to be careful with binary search.

Resources