4 Weeër fir VLOOKUP Case Sensitiv an Excel ze maachen

Dëse klenge Tutorial erkläert wéi een eng Funktioun mécht VPR (VLOOKUP) Case-sensibel, weist e puer aner Formelen, déi Excel kann op eng Case-sensibel Manéier sichen, a weist op d'Stäerkten a Schwächten vun all Funktioun.

Ech denken datt all Excel Benotzer weess wéi eng Funktioun vertikal Sich mécht. Dat ass richteg, et ass eng Funktioun VPR. Wéi och ëmmer, wéineg Leit wëssen dat VPR ass net caseempfindlech, dh d'Schrëften am ënneschten an d'Gréisst si identesch dofir.

Hei ass e séier Beispill dat d'Onméiglechkeet weist VPR erkennen aschreiwen. Ugeholl an enger Zell A1 enthält de Wäert "Rechnung" an d'Zelle A2 - "Bill", Formel:

=VLOOKUP("Bill",A1:A10,2)

=ВПР("Bill";A1:A10;2)

... wäert seng Sich op "Rechnung" stoppen well dee Wäert als éischt an der Lëscht kënnt, an de Wäert aus der Zell extrahéiert B1.

Méi spéit an dësem Artikel weisen ech Iech wéi Dir maacht VPR Fall sensibel. Zousätzlech wäerte mir e puer méi Funktiounen léieren, déi case-sensibel Sichen an Excel ausféieren kënnen.

Mir fänken un mat dem einfachsten - VIEW (LOOKUP) an SUMPRODUCT (SUMPRODUCT), déi, leider, verschidde bedeitend Aschränkungen hunn. Als nächst wäerte mir déi liicht méi komplex Formel méi no kucken INDEX+MATCH (INDEX + MATCH), wat an all Situatioun a mat all Dataset perfekt funktionnéiert.

• Case-sensibel VLOOKUP - erfuerdert eng Hëllefskolonn
• Case-sensibel LOOKUP Funktioun - erfuerdert d'Donnéeën ze sortéieren
• SUMPRODUCT - gëtt nëmmen numeresch Wäerter zréck
• INDEX + MATCH - case-sensibel Sich fir all Datentypen

D'VLOOKUP Funktioun ass case sensibel

Wéi Dir scho wësst, ass déi üblech Funktioun VPR ass case-onsensibel. Wéi och ëmmer, et gëtt e Wee fir et Fallsensibel ze maachen. Fir dëst ze maachen, musst Dir eng Hëllefskolonn an den Dësch addéieren, wéi am folgende Beispill gewisen.

Ugeholl an enger Kolonn B et gi Produktidentifizéierer (Artikel) an Dir wëllt de Präis vum Produkt an de entspriechende Kommentar aus de Kolonnen extrahéieren C и D. De Problem ass datt d'Identificateuren souwuel kleng Buschtawen wéi och grouss Zeechen enthalen. Zum Beispill, Zell Wäerter B4 (001Tvci3u) an B5 (001Tvci3U) ënnerscheede sech nëmmen am Fall vum leschte Charakter, u и U bzw..

Wéi Dir kënnt virstellen, déi üblech Sichformel

=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)

=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)

wäert erëm zeréck $ 90, zanter dem Wäert 001Tvci3u ass am Sichberäich méi fréi wéi 001Tvci3U. Awer dat ass net wat mir brauchen, oder?

Fir mat enger Funktioun ze sichen VPR an Excel Fall sensibel, musst Dir eng Helper Kolonn derbäi a fëllt seng Zellen mat der folgender Formel (wou B ass d'Sich Kolonn):

=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")

=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")

Dës Formel brécht de gewënschten Wäert an getrennten Zeechen, ersetzt all Charakter mat sengem Code (zum Beispill amplaz A op 65, amplaz a Code 97) a kombinéiert dann dës Coden an eng eenzegaarteg String vun Zuelen.

Duerno benotze mir eng einfach Funktioun VPR fir Fall sensibel Sich:

=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)

=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)

Richteg Operatioun vun der Funktioun VPR Fall-sensibel hänkt vun zwee Faktoren of:

1. D'Hëllefkolonne muss déi lénks Kolonn am sichtbare Beräich sinn.
2. De Wäert deen Dir sicht muss e Charaktercode enthalen amplaz vum realen Wäert.

Wéi benotzt Dir d'CODE Funktioun korrekt

D'Formel, déi an d'Zellen vun der Hëllefskolonne agebaut gëtt, gëtt ugeholl datt all Är Sichwäerter déiselwecht Unzuel vun Zeechen hunn. Wann net, da musst Dir déi klengst a gréisst Zuelen wëssen an esou vill Features derbäi IFERROR (IFERROR) wéivill Zeechen ass den Ënnerscheed tëscht dem kürzeste a längste gesichte Wäert.

Zum Beispill, wann de kürzeste Sichwäert 3 Zeechen ass an de längsten ass 5 Zeechen, benotzt dës Formel:

=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")

=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")

Fir Funktioun PSTR (MID) Dir gitt déi folgend Argumenter:

• Argument 1 - Text (Text) ass den Text oder Zellreferenz mat den Zeeche fir extrahéiert ze ginn (an eisem Fall ass et B2)
• Argument 2 - start_num (start_position) ass d'Positioun vun den éischte vun dësen Zeechen, déi extrahéiert ginn. du gitt 1 an der éischter Funktioun PSTR, 2 - an der zweeter Funktioun PSTR etc.
• Argument 3 - num_chars (number_of_characters) - Spezifizéiert d'Zuel vun den Zeeche fir aus dem Text ze extrahieren. Well mir brauchen nëmmen 1 Charakter all der Zäit, an all Funktiounen mir schreiwen 1.

Aschränkungen: Funktioun VPR ass net déi bescht Léisung fir case-sensibel Sichen an Excel. Als éischt ass d'Zousatz vun enger Hëllefskolonn erfuerderlech. Zweetens, mécht d'Formel eng gutt Aarbecht nëmmen wann d'Donnéeën homogen sinn, oder déi exakt Zuel vun Zeechen an de gesichte Wäerter bekannt ass. Wann dëst net Äre Fall ass, ass et besser eng vun de Léisungen ze benotzen déi mir hei ënnen weisen.

LOOKUP Funktioun fir Fall sensibel Sich

Funktioun VIEW (LOOKUP) Zesummenhang VPR, awer seng Syntax erlaabt case-sensibel Sichen ouni eng Hëllefskolonn derbäizefügen. Fir dëst ze maachen, benotzt VIEW kombinéiert mat der Funktioun EXAKT (EXAKT).

Wa mir d'Donnéeën aus dem fréiere Beispill huelen (ouni Hëllefskolonne), da wäert déi folgend Formel mat der Aufgab eens:

=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)

=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)

Formel Sich am Beräich A2:A7 genee Match mat Zell Wäert F2 Fall sensibel a gëtt de Wäert vun der Kolonn B vun der selwechter Zeil zréck.

wëll VPRFunktioun VIEW Wierker gläich mat Text an numeresche Wäerter, wéi Dir am Screenshot hei ënnen kënnt gesinn:

Wichteg! Fir d'Funktioun VIEW richteg geschafft, d'Wäerter an der Lookup Kolonn sollen an opsteigend Uerdnung zortéiert ginn, dh vum klengste bis gréissten.

Loosst mech kuerz erkläre wéi d'Funktioun funktionnéiert EXAKT an der Formel hei uewen gewisen, well dëst de Schlësselpunkt ass.

Funktioun EXAKT vergläicht déi zwee Textwäerter am 1. an 2. Argument a gëtt TRUE zréck wa se genau d'selwecht sinn, oder FALSE wann se net sinn. Et ass wichteg fir eis datt d'Funktioun EXAKT Fall sensibel.

Loosst eis kucken wéi eis Formel funktionnéiert VIEW + EXAKT:

=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)

=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)

• Funktioun EXAKT vergläicht Zell Wäert F2 mat all Elementer an enger Kolonn A (A2:A7). Gitt TRUE zréck wann e genee Match fonnt gëtt, soss FALSCH.
• Well Dir déi éischt Funktioun Argument ginn VIEW Wäert TRUE, et extrahéiert de entspriechende Wäert aus der spezifizéierter Kolonn (an eisem Fall, Kolonn B) nëmmen wann e genee Match fonnt gëtt, Fall sensibel.

Ech hoffen dës Erklärung war kloer an elo verstitt Dir d'Haaptidee. Wann jo, da wäert Dir keng Schwieregkeeten mat anere Funktiounen hunn, déi mir weider analyséieren, well. si schaffen all um selwechte Prinzip.

Aschränkungen: D'Donnéeën an der Lookup Kolonn mussen an opsteigend Uerdnung zortéiert ginn.

SUMPRODUCT - fënnt Textwäerter, ka sensibel, awer gëtt nëmmen Zuelen zréck

Wéi Dir schonn aus dem Titel verstanen hutt, SUMPRODUCT (SUMPRODUCT) ass eng aner Excel Funktioun déi Iech hëlleft eng case-sensibel Sich ze maachen, awer nëmmen numeresch Wäerter zréckginn. Wann dës Optioun Iech net passt, da kënnt Dir direkt op de Bündel weidergoen INDEX+MATCH, déi eng Léisung fir all Fall a fir all Datentypen gëtt.

Als éischt, loosst mech d'Syntax vun dëser Funktioun kuerz erklären, dëst wäert Iech hëllefen déi case-sensibel Formel déi folgend ass besser ze verstoen.

Funktioun SUMPRODUCT multiplizéiert d'Elementer vun de gegebene Arrays a gëtt d'Zomm vun de Resultater zréck. D'Syntax gesäit esou aus:

SUMPRODUCT(array1,[array2],[array3],...)

СУММПРОИЗВ(массив1;[массив2];[массив3];…)

Well mir eng case-sensibel Sich brauchen, benotze mir d'Funktioun EXAKT (EXAKT) vum fréiere Beispill als ee vun de Multiplikatore:

=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))

=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))

Wéi Dir Iech erënnert EXAKT vergläicht Zell Wäert F2 mat all Elementer an enger Kolonn A. Gitt TRUE zréck wann e genee Match fonnt gëtt, soss FALSCH. A mathematesch Operatiounen hëlt Excel TRUE als 1, a FALSCH fir 0Weider SUMPRODUCT multiplizéiert dës Zuelen a summéiert d'Resultater.

Nullen ginn net gezielt, well wann se multiplizéiert ginn, ginn se ëmmer 0. Loosst eis e méi no kucken wat geschitt wann e genee Match an enger Kolonn A fonnt an zréck 1... Funktioun SUMPRODUCT multiplizéiert d'Zuel an der Kolonn B on 1 a gëtt d'Resultat zréck - genee déi selwecht Zuel! Dëst ass well d'Resultater vun den anere Produkter null sinn, a si beaflossen net déi resultéierend Zomm.

Leider d'Funktioun SUMPRODUCT kann net mat Textwäerter an Datume schaffen well se net multiplizéiert kënne ginn. An dësem Fall kritt Dir eng Fehlermeldung #WAARDE! (#VALUE!) wéi an enger Zell F4 op der Foto hei drënner:

Aschränkungen: Gitt nëmmen numeresch Wäerter zréck.

INDEX + MATCH - case-sensibel Sich fir all Datentyp

Schlussendlech si mir no bei enger onlimitéierter a case-sensibel Sichformel déi mat all Dateset funktionnéiert.

Dëst Beispill kënnt lescht, net well dat Bescht fir Dessert lénks ass, mee well d'Wëssen, déi aus de fréiere Beispiller gewonnen goufen, hëlleft Iech déi case-sensibel Formel besser a méi séier ze verstoen. INDEX+MATCH (INDEX+MATCH).

Wéi Dir wahrscheinlech virgestallt hutt, ass d'Kombinatioun vu Funktiounen MÉI ausgesat и INDEX benotzt an Excel als méi flexibel a mächteg Alternativ fir VPR. Den Artikel Benotzt INDEX a MATCH amplaz VLOOKUP wäert perfekt erklären wéi dës Funktiounen zesumme schaffen.

Ech wäert just d'Schlësselpunkte widderhuelen:

• Funktioun MÉI ausgesat (MATCH) sicht no engem Wäert an engem bestëmmte Beräich a gëtt seng relativ Positioun zréck, dat ass, der Rei an / oder Kolonn Zuel;
• Als nächst, d'Funktioun INDEX (INDEX) gëtt e Wäert vun enger spezifizéierter Kolonn an / oder Zeil zréck.

Fir d'Formel INDEX+MATCH kéint case-sensibel sichen, Dir braucht nëmmen eng Funktioun derbäi. Et ass net schwéier ze roden wat et erëm ass EXAKT (EXAKT):

=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))

=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))

An dëser Formel EXAKT funktionnéiert an der selwechter Manéier wéi a Verbindung mat der Funktioun VIEW, a gëtt datselwecht Resultat:

Notéiert datt d'Formel INDEX+MATCH a Curly Klammeren zougemaach ass eng Arrayformel an Dir musst et fäerdeg bréngen andeems Dir dréckt Ctrl + Umeldung + Enter.

Firwat ass INDEX + MATCH déi bescht Léisung fir case-sensibel Sich?

D'Haaptvirdeeler vum Bündel INDEX и MÉI ausgesat:

1. Erfuerdert keng Hëllefskolonn derbäizesetzen, am Géigesaz zu VPR.
2. Erfuerdert net datt d'Sich Kolonn zortéiert gëtt, anescht wéi VIEW.
3. Schafft mat all Typ vun Daten - Zuelen, Text an Datumen.

Dës Formel schéngt perfekt, oder? Eigentlech ass et net. An dofir.

Gitt un datt d'Zell an der Retourwäerter Kolonn, déi mam Lookupwäert ass, eidel ass. Wéi eng Resultat gëtt d'Formel zréck? Nee? Loosst eis kucken wat d'Formel tatsächlech zréckkënnt:

Oops, d'Formel gëtt null zréck! Dëst ass vläicht kee grousse Problem wann Dir mat puren Textwäerter schafft. Wéi och ëmmer, wann den Dësch Zuelen enthält, dorënner "richteg" Nullen, gëtt dëst e Problem.

Tatsächlech behuelen all déi aner Lookupformelen (VLOOKUP, LOOKUP, an SUMPRODUCT) déi mir virdru diskutéiert hunn déiselwecht Manéier. Awer Dir wëllt déi perfekt Formel, richteg?

Fir eng Formel Case sensibel ze maachen INDEX+MATCH perfekt, huet et an eng Funktioun IF (IF) déi eng Zell mat engem Retourwäert testen an en eidel Resultat zréckginn wann et eidel ass:

=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")

=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")

An dëser Formel:

• B ass eng Kolonn mat Retour Wäerter
• 1+ ass eng Nummer déi d'relativ Positioun vun der Zell dréit, déi vun der Funktioun zréckgeet MÉI ausgesat, op déi richteg Adress vun der Zell. Zum Beispill, an eiser Funktioun MÉI ausgesat Sich Array uginn A2:A7, dat ass, déi relativ Positioun vun der Zell A2 wäert 1, well et deen éischten am Array ass. Awer déi aktuell Positioun vun der Zell A2 an der Kolonn ass 2, also addéiere mer 1den Ënnerscheed ze maachen an d'Funktioun ze hunn INDIREKTE (INDIRECT) huet de Wäert aus der gewënschter Zell zréckgezunn.

D'Biller hei drënner weisen déi korrigéiert case-sensibel Formel INDEX+MATCH An Aktioun. Et gëtt en eidel Resultat zréck wann déi zréckginn Zell eidel ass.

Ech hunn d'Formel a Kolonnen ëmgeschriwwen B:Dfir d'Formelbar op de Screenshot ze passen.

Formel zréck 0wann der zréck Zell enthält null.

Wann Dir de Link wëllt INDEX и MÉI ausgesat e Message ugewisen wann de Retourwäert eidel ass, kënnt Dir et an de leschten Zitater ("") vun der Formel schreiwen, zum Beispill, sou:

=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")

=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")