Forum Discussion
vaphaet
May 04, 2023Copper Contributor
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...
- May 08, 2023
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
May 08, 2023Copper 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
May 08, 2023MVP
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.
- vaphaetMay 18, 2023Copper ContributorThanks 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.- SergeiBaklanMay 22, 2023MVP
vaphaet , glad to help