Forum Discussion

vaphaet's avatar
vaphaet
Copper Contributor
May 04, 2023
Solved

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.

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

    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
      Diamond Contributor
      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.
    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      May 08, 2023
      Looks like the range name 'Havi_Bér_Dátum' is missing from your sample file.

Resources