Hello everyone, It would be great if someone could help me. In the attached table, in the first tab "Overview," there is employee data with their classification (columns B & C). The other tabs contain pay scale tables, each valid for specific periods. By entering the period in columns F and G, the correct value for the pay group and step should be read from the pay scale tables and entered in column H. So, my draft is producing incorrect values. I'm curious. Thanks


Manfred
.

Attached Files

PK VKO_Abrechnungsabgleich.xlsx (23.1 KB, 6 views)

In H2:
=IF(CONCATENATE(G2&TEXT(F2,"00"))<CONCATENATE('TVö D Bund Jul 23 - Feb 24'!$K$1&TEXT('TVöD Bund Jul 23 - Feb 24'!$J$1,"00")),OFFSET('TVöD Bund Jul 23 - Feb 24'!$A$1,MATCH(B2,'TVöD Bund Jul 23 - Feb 24'!$A:$A,0)-1,MATCH(C2,'TVöD Bund Jul 23 - Feb 24'!$2:$2,0)-1),OFFSET('TVöD Bund ab März 24'!$A$1,MATCH(B2,'TVöD Bund ab März 24'!$A:$A,0)-1,MATCH(C2,'TVöD Bund ab März 24'!$2:$2,0)-1))*D2

Copy down to your other cells.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

thank you . but it dont worked - maybe why i used german excel?
Best
Manfred

Works for me. Depending on your system's regional settings, you may need to change the comma separators to semi-colons, which you can do via a simple Find/Replace. See attached.

Do note that your rows with just '9' in column B won't find a match, because your TVöD Bund sheets have '9a', '9b', 9c', so there's nothing for the '9' to match with.

Attached Files

PK VKO_Abrechnungsabgleich.xlsx (22.0 KB, 1 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Note: I am using the semicolon ";" as the list separator because you probably have one. If you have an error, change the semicolon ";" to a comma ","

thank you all 😀