SOLVED

XVERWEIS konvertieren für Excel 2013

%3CLINGO-SUB%20id%3D%22lingo-sub-2314809%22%20slang%3D%22de-DE%22%3EConvert%20XVERWEIS%20for%20Excel%202013%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314809%22%20slang%3D%22de-DE%22%3E%3CTABLE%20width%3D%22322%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2282%22%3EBs%3FA19%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E06.05.2020%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E13.05.2020%3C%2FTD%3E%3CTD%20width%3D%2280%22%3E20.05.2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E20.05.2020%3C%2FTD%3E%3CTD%3EBS1A19%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EBS1A19%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EB1%3DXVERWEIS(A1%3B%20B2%3AD2%3B%20B1%3AD1%3B%22%20-%22%3B2%3B-1)%20%3D%2005%2F20%2F2020%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%20I%20would%20like%20to%20convert%20the%20above%20XVERWEIS%20for%20Excel%202013%20%3CBR%20%2F%3E%20INDEX%20VERGLEICH%20returns%20as%20a%20result%20%2206.05.2020%22%2C%20but%20I%20would%20like%20to%20have%20%2220.05.2020%22%20output.%3C%2FP%3E%3CP%3EMaybe%20someone%20could%20help%20me.%3CBR%20%2F%3EThank%20you%20very%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2314809%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314834%22%20slang%3D%22en-US%22%3ERe%3A%20XVERWEIS%20konvertieren%20f%C3%BCr%20Excel%202013%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314834%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042960%22%20target%3D%22_blank%22%3E%40learner1234%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EKopieren%20Sie%20diese%20benutzerdefinierte%20Funktion%20in%20ein%20Modul%20im%20Visual%20Basic-Editor%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFunction%20ReverseLookup(What%20As%20String%2C%20LookupRange%20As%20Range%2C%20ReturnRange%20As%20Range)%20As%20Variant%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20For%20i%20%3D%20LookupRange.Count%20To%201%20Step%20-1%0A%20%20%20%20%20%20%20%20If%20LCase(LookupRange(i))%20Like%20LCase(What)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20ReverseLookup%20%3D%20ReturnRange(i)%0A%20%20%20%20%20%20%20%20%20%20%20%20Exit%20Function%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20i%0A%20%20%20%20ReverseLookup%20%3D%20%22-%22%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EGeben%20Sie%20die%20folgende%20Formel%20in%20A2%20ein%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DReverseLookup(A1%3BB2%3AD2%3BB1%3AD1)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESpeichern%20Sie%20die%20Arbeitsmappe%20als%20makrof%C3%A4hige%20Arbeitsmappe%20(*.xlsm).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314853%22%20slang%3D%22de-DE%22%3ERe%3A%20Convert%20XVERWEIS%20for%20Excel%202013%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314853%22%20slang%3D%22de-DE%22%3EHello%20Her%20Vogelaar%2C%20thank%20you%20very%20much%2C%20the%20solution%20works!%20However%2C%20performance%20is%20limited%20because%20the%20table%20is%20relatively%20large.%20May%20I%20ask%20you%20whether%20there%20is%20also%20a%20solution%20that%20requires%20less%20computing%20time%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314856%22%20slang%3D%22en-US%22%3ERe%3A%20XVERWEIS%20konvertieren%20f%C3%BCr%20Excel%202013%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314856%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042960%22%20target%3D%22_blank%22%3E%40learner1234%3C%2FA%3E%26nbsp%3BTry%20this%20one%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(A1%3AD1%2C1%2CMAX((B2%3AD2%3DHLOOKUP(A1%2CB2%3AD2%2C1%2CFALSE))*COLUMN(B2%3AD2)))%0A%0A%3DINDEX(A1%3AD1%3B1%3BMAX((B2%3AD2%3DWVERWEIS(A1%3BB2%3AD2%3B1%3BFALSCH))*SPALTE(B2%3AD2)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EEntry%20needs%20to%20be%20confirmed%20with%20Ctrl-Shift-Enter%20(CSE)%20which%20will%20put%20curly%20brackets%20around%20the%20formula.%20Tested%20it%20on%20Excel2013%20and%20it%20worked.%20File%20attached%20for%20your%20reference.%20The%20formula%20should%20come%20across%20in%20German%20automatically.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314873%22%20slang%3D%22en-US%22%3ERe%3A%20XVERWEIS%20konvertieren%20f%C3%BCr%20Excel%202013%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314873%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042960%22%20target%3D%22_blank%22%3E%40learner1234%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWerden%20der%20Suchbereich%20und%20der%20R%C3%BCckgabebereich%20immer%20Zeilen%20sein%3F%20Und%20werden%20sie%20immer%20nebeneinander%20sein%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314892%22%20slang%3D%22de-DE%22%3ERE%3A%20Convert%20XVERWEIS%20for%20Excel%202013%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314892%22%20slang%3D%22de-DE%22%3ESearch%20scope%20and%20return%20area%20are%20always%20rows.%20The%20return%20area%20is%20always%20the%20header%20of%20the%20table%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314912%22%20slang%3D%22en-US%22%3ERE%3A%20XVERWEIS%20konvertieren%20f%C3%BCr%20Excel%202013%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314912%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042960%22%20target%3D%22_blank%22%3E%40learner1234%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEs%20w%C3%A4re%20besser%2C%20Riny's%20Formel%20zu%20verwenden!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(%24A%241%3A%24D%241%3B1%3BMAX((B2%3AD2%3DWVERWEIS(%24A%241%3BB2%3AD2%3B1%3BFALSCH))*SPALTE(B2%3AD2)))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
BS?A1906.05.202013.05.202020.05.2020
20.05.2020BS1A19 BS1A19

B1=XVERWEIS(A1;B2:D2;B1:D1;"-";2;-1) = 20.05.2020

 

Hallo, ich möchte o.g. XVERWEIS für Excel 2013 konvertieren
INDEX VERGLEICH liefert als Ergebnis "06.05.2020", ich möchte jedoch "20.05.2020" ausgeben lassen.

Vielleicht könnte mir jemand helfen.
Vielen Dank!

10 Replies

@learner1234 

Kopieren Sie diese benutzerdefinierte Funktion in ein Modul im Visual Basic-Editor:

Function ReverseLookup(What As String, LookupRange As Range, ReturnRange As Range) As Variant
    Dim i As Long
    For i = LookupRange.Count To 1 Step -1
        If LCase(LookupRange(i)) Like LCase(What) Then
            ReverseLookup = ReturnRange(i)
            Exit Function
        End If
    Next i
    ReverseLookup = "-"
End Function

Geben Sie die folgende Formel in A2 ein:

 

=ReverseLookup(A1;B2:D2;B1:D1)

 

Speichern Sie die Arbeitsmappe als makrofähige Arbeitsmappe (*.xlsm).

Hallo Her Vogelaar, vielen Dank, die Lösung funktioniert! Die Performance ist jedoch eingeschränkt, da die Tabelle relativ groß ist. Darf ich Sie fragen, ob es auch eine Lösung gibt, die weniger Rechenzeit erfordert?

@learner1234 Try this one:

=INDEX(A1:D1,1,MAX((B2:D2=HLOOKUP(A1,B2:D2,1,FALSE))*COLUMN(B2:D2)))

=INDEX(A1:D1;1;MAX((B2:D2=WVERWEIS(A1;B2:D2;1;FALSCH))*SPALTE(B2:D2)))

Entry needs to be confirmed with Ctrl-Shift-Enter (CSE) which will put curly brackets around the formula. Tested it on Excel2013 and it worked. File attached for your reference. The formula should come across in German automatically.

 

@learner1234 

Werden der Suchbereich und der Rückgabebereich immer Zeilen sein? Und werden sie immer nebeneinander sein?

Suchbereich und Rückgabebereich sind immer Zeilen. Der Rückgabebereich ist immer die Kopfzeile der Tabelle

@learner1234 

Es wäre besser, Riny's Formel zu verwenden!

 

=INDEX($A$1:$D$1;1;MAX((B2:D2=WVERWEIS($A$1;B2:D2;1;FALSCH))*SPALTE(B2:D2)))

Hallo Herr Vogelaar,
Hallo Herr van Eekelen,
vielen Dank für Ihre Hilfe! Diese Formel funktioniert nur leider nicht in einer Tabelle.
best response confirmed by learner1234 (Occasional Contributor)
Solution

@learner1234 

Warum nicht?

S0359.png

@learner1234 Did you enter the formula with CSE?

Screenshot 2021-05-02 at 14.41.04.png

 

See attached!

Hallo Herr Vogelaar, vielen Dank! Ich habe beim Zeilenbezug versehentlich die Header mit eingeschlossen.