SOLVED

SVERWEIS Berechnungsfehler

%3CLINGO-SUB%20id%3D%22lingo-sub-1071566%22%20slang%3D%22de-DE%22%3ESVERWEIS%20Calculation%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1071566%22%20slang%3D%22de-DE%22%3E%3CP%3EGood%20day!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20I%20have%20a%20question%20about%20a%20SVERWEIS%20issue.%20An%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162277iA93F29343D14C7D0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%221.png%22%20title%3D%221.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162278i5FEF044FBF13CB78%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%222.png%22%20title%3D%222.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%2C%20the%20SVERWEIS%20works%20on%20%22Photo%201%22%2C%20at%20%22Foto%202%22%20I%20get%20a%20%23NV%20message.%20The%20reason%20for%20the%20message%20is%20that%20the%20search%20value%20in%20A7%20results%20from%20a%20calculation.%20If%20I%20write%20the%20same%20value%20myself%20in%20A7%20(even%20the%20formatting%20is%20equal%20-%20%22hh%3Amm%22)%2C%20the%20value%20with%20the%20SVERWEIS%20function%20is%20not%20found%20in%20the%20matrix.%20Apparently%20it%20has%20to%20do%20with%20the%20fact%20that%20SVERWEISE%20can't%20work%20with%20calculations%2C%20is%20that%20true%20or%20is%20it%20due%20to%20another%20problem%20and%20can%20this%20be%20solved%20in%20any%20other%20way%3F%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20very%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1071566%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1071672%22%20slang%3D%22de-DE%22%3ESubject%3A%20SVERWEIS%20Calculation%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1071672%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F492399%22%20target%3D%22_blank%22%3E%40DennnisG92%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20just%20a%20question%20of%20transformation.%20Enough%20is%20enough%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DA7*24%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ESet%20the%20formatting%20to%20%22Default%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eotherwise%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%7B%3DINDEX(B1%3AB4%3BVERGLEICH(STUNDE(A7)%2BMINUTE(A7)%25%3BSTUNDE(A1%3AA4)%2BMINUTE(A1%3AA4)%25%3B0))%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EEnter%20without%20the%20.%20Complete%20input%20with%20CTRL%20SWITCH%20ENTER%20instead%20of%20only%20WITH%20ENTER.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1073682%22%20slang%3D%22de-DE%22%3ESubject%3A%20SVERWEIS%20Calculation%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1073682%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3ESometimes%20you%20just%20think%20too%20complicated%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThe%20conversion%20with%20*24%20and%20formatting%20to%20standard%20is%20enough%20for%20me%20in%20this%20case%2C%20thanks%20for%20the%20tip!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Guten Tag!

 

Und zwar habe ich eine Frage zu einer SVERWEIS Ausgabe. Ein Beispiel:

 

 

1.png2.png

 

 

Wie man sieht, funktioniert der SVERWEIS auf "Foto 1", bei "Foto 2" bekomme ich eine #NV-Meldung. Grund der Meldung ist, dass sich der Suchwert in A7 aus einer Berechnung ergibt. Wenn ich den gleichen Wert selbst in A7 schreibe (sogar die Formatierungen sind gleich - "hh:mm"), wird der Wert mit der SVERWEIS Funktion nicht in der Matrix gefunden. Es hat anscheinend damit zu tun, dass SVERWEISE nicht mit Berechnungen arbeiten können, stimmt das oder liegt es an einem anderen Problem und kann man das irgendwie anders lösen?


Vielen Dank!

2 Replies
Best Response confirmed by DennnisG92 (New Contributor)
Solution

@DennnisG92 

Es geht hier ja nur um eine Umwandlung. Da reicht:

=A7*24

Formatierung auf "Standard" setzen.

 

Ansonsten:

{=INDEX(B1:B4;VERGLEICH(STUNDE(A7)+MINUTE(A7)%;STUNDE(A1:A4)+MINUTE(A1:A4)%;0))}

Eingabe ohne die {}. Eingabe mit STRG-UMSCHALT-ENTER abschliessen statt nur mit ENTER.

 

@Detlef Lewin 
Manchmal denkt man einfach zu kompliziert


Die Umwandlung mit *24 und Formatierung auf Standard reicht mir in dem Fall aus, vielen Dank für den Tipp!