Som standard er opslagsværdien i VLOOKUP -funktionen ikke store og små bogstaver. For eksempel, hvis din opslagsværdi er MATT, mat eller Matt, er det samme for VLOOKUP -funktionen. Den returnerer den første matchningsværdi uanset sagen.
Gør VLOOKUP sagfølsom
Antag, at du har dataene som vist herunder:
Som du kan se, er der tre celler med samme navn (A2, A4 og A5), men med et andet stort bogstav. Til højre (i E2: F4) har vi de tre navne (Matt, MATT og matt) sammen med deres scoringer i matematik.
Excel VLOOKUP-funktionen er ikke udstyret til at håndtere store bogstaver-følsomme opslagsværdier. I dette eksempel ovenfor, uanset hvad opslagsværdi er (Matt, MATT eller matt), returnerer det altid 38 (hvilket er den første matchende værdi).
I denne vejledning lærer du, hvordan du gør VLOOKUP store og små bogstaver følsomme ved:
- Brug af en hjælperkolonne.
- Uden at bruge en hjælperkolonne og bruge en formel.
Gør VLOOKUP store og små bogstaver følsomme - Brug af hjælpekolonne
En hjælperkolonne kan bruges til at få en unik opslagsværdi for hvert element i opslagsmatrixen. Det hjælper med at skelne mellem navne med forskellige bogstaver.
Her er trinene til at gøre dette:
- Indsæt en hjælperkolonne til venstre for kolonnen, hvorfra du vil hente dataene. I eksemplet herunder skal du indsætte hjælperkolonnen mellem kolonne A og C.
- Indtast formlen = RÆKKE () i hjælpekolonnen. Det indsætter rækkenummeret i hver celle.
- Brug følgende formel i celle F2 for at få det mellemstore følsomme opslagsresultat.
= VLOOKUP (MAX (EXAKT (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))), $ B $ 2: $ C $ 9,2,0)
- Kopier indsæt det for de resterende celler (F3 og F4).
Bemærk: Da dette er en matrixformel, skal du bruge Control + Shift + Enter i stedet for bare at indtaste.
Hvordan virker det?
Lad os nedbryde formlen for at forstå, hvordan den fungerer:
- EXAKT (E2, $ A $ 2: $ A $ 9) - Denne del sammenligner opslagsværdien i E2 med alle værdierne i A2: A9. Det returnerer en matrix med SAND/FALSK, hvor SAND returneres, når der er et nøjagtigt match. I dette tilfælde, hvor værdien i E2 er Matt, ville den returnere følgende array:
{SAND; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK}. - EXACT (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9) - Denne del multiplicerer arrayet af SANDE/FALSKE med rækkenummeret A2: A9. Hvor som helst der er et SANDT, er det giver rækkenummeret, ellers giver det 0. I dette tilfælde ville det returnere {2; 0; 0; 0; 0; 0; 0; 0}.
- MAX (EXACT (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9)))) - Denne del returnerer den maksimale værdi fra rækken af tal. I dette tilfælde ville det returnere 2 (hvilket er rækketallet, hvor der er et eksakt match).
- Nu bruger vi ganske enkelt dette tal som opslagsværdi og bruger opslagsmatrixet som B2: C9.
Bemærk: Du kan indsætte hjælperkolonnen hvor som helst i datasættet. Bare sørg for at det er til venstre for kolonnen, hvorfra du vil hente dataene. Du skal derefter justere kolonnenummeret i VLOOKUP -funktionen i overensstemmelse hermed.
Hvis du nu ikke er fan af hjælperkolonne, kan du også foretage et opslagsfølsomt opslag uden hjælperkolonnen.
Gør VLOOKUP Case Sensitive - Uden hjælpekolonnen
Selv når du ikke vil bruge hjælperkolonnen, skal du stadig have en virtuel hjælperkolonne. Denne virtuelle kolonne er ikke en del af regnearket, men er konstrueret inden for formlen (som vist nedenfor).
Her er formlen, der giver dig resultatet uden hjælperkolonnen:
= VLOOKUP (MAX (EXAKT (D2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9)))), VÆLG ({1,2}, ROW ($ A $ 2: $ A $ 9) , $ B $ 2: $ B $ 9), 2,0)
Hvordan virker det?
Formlen bruger også begrebet en hjælperkolonne. Forskellen er, at i stedet for at sætte hjælperkolonnen i regnearket, betragter det som virtuelle hjælperdata, der er en del af formlen.
Her er den del, der fungerer som hjælperdata (fremhævet med orange):
= VLOOKUP (MAX (EXAKT (D2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))),VÆLG ({1,2}, Række ($ A $ 2: $ A $ 9), $ B $ 2: $ B $ 9),2,0)
Lad mig vise dig, hvad jeg mener med virtuelle hjælperdata.
I ovenstående illustration, da jeg vælger VÆLG delen af formlen og trykker på F9, viser det resultatet, som VÆLG formlen ville give.
Resultatet er {2,38; 3,88; 4,57; 5,82; 6,55; 7,44; 8,75; 9,38}
Det er en matrix, hvor et komma repræsenterer næste celle i samme række og semikolon repræsenterer, at følgende data er i den næste række. Derfor skaber denne formel 2 kolonner med data - En kolonne har rækkenummer og en har matematikscoren.
Når du nu bruger VLOOKUP -funktionen, leder den simpelthen efter opslagsværdien i den første kolonne (af disse virtuelle 2 kolonnen data) og returnerer den tilsvarende score. Opslagningsværdien her er et tal, som vi får fra kombinationen MAX og EXACT -funktion.
Download eksempelfilen
Er der nogen anden måde du ved, hvordan du gør dette på? Hvis ja, så del med mig i kommentarfeltet.
Du kan også lide følgende VLOOKUP -selvstudier:
- Brug af VLOOKUP -funktion med flere kriterier.
- Brug af VLOOKUP -funktion til at få det sidste nummer på en liste.
- VLOOKUP Vs. INDEX/MATCH
- Brug IFERROR med VLOOKUP for at slippe af med #N/A -fejl.
