SOLVED

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

Copper Contributor

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.

6 Replies
Can you post an anonymized Excel file with that table, with just enough data to demonstrate the issue?

@Jan Karel Pieterse 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

Looks like the range name 'Havi_Bér_Dátum' is missing from your sample file.
best response confirmed by Hans Vogelaar (MVP)
Solution

@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.

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.
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@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.

View solution in original post