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 show...
Excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    May 08, 2023

    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.

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

SergeiBaklan's avatar
SergeiBaklan
MVP
May 08, 2023

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.

  • vaphaet's avatar
    vaphaet
    Copper Contributor
    May 18, 2023
    Thanks for the advice! I have forgotten my mantra: "Excel is not a database". When I set up the function, the referenced range was in alphabetical order... and then it got mixed up...
    Lesson learned.

Resources