Som standard er oppslagsverdien i VLOOKUP -funksjonen ikke store og små bokstaver. For eksempel, hvis oppslagsverdien din er MATT, matt eller Matt, er det samme for VLOOKUP -funksjonen. Den returnerer den første matchende verdien uavhengig av saken.
Gjør VLOOKUP saksfølsom
Anta at du har dataene som vist nedenfor:
Som du kan se, er det tre celler med samme navn (A2, A4 og A5), men med et annet bokstav. Til høyre (i E2: F4) har vi de tre navnene (Matt, MATT og matt) sammen med poengsummen sin i matematikk.
Excel VLOOKUP-funksjonen er ikke utstyrt for å håndtere store bokstaver. I dette eksemplet ovenfor, uansett hvilken oppslagsverdi det er (Matt, MATT eller matt), vil den alltid returnere 38 (som er den første matchende verdien).
I denne opplæringen lærer du hvordan du gjør VLOOKUP mellom store og små bokstaver:
- Bruke en hjelperkolonne.
- Uten å bruke en hjelperkolonne og bruke en formel.
Gjør VLOOKUP store og små bokstaver følsomme - bruker hjelpekolonne
En hjelperkolonne kan brukes til å få unik oppslagsverdi for hvert element i oppslagsoppsettet. Det hjelper med å skille mellom navn med forskjellige bokstaver.
Her er trinnene for å gjøre dette:
- Sett inn en hjelperkolonne til venstre for kolonnen der du vil hente dataene. I eksemplet nedenfor må du sette inn hjelpekolonnen mellom kolonne A og C.
- I hjelpekolonnen skriver du inn formelen = RAD (). Det vil sette inn radnummeret i hver celle.
- Bruk følgende formel i celle F2 for å få det saksfølsomme oppslagsresultatet.
= VLOOKUP (MAKS (EKSAKT (E2, $ A $ 2: $ A $ 9)*(RAD ($ A $ 2: $ A $ 9))), $ B $ 2: $ C $ 9,2,0)
- Kopier lim den inn for de resterende cellene (F3 og F4).
Merk: Siden dette er en matriseformel, bruker du Control + Shift + Enter i stedet for bare å skrive inn.
Hvordan virker dette?
La oss bryte ned formelen for å forstå hvordan den fungerer:
- EKSAKT (E2, $ A $ 2: $ A $ 9) - Denne delen sammenligner oppslagsverdien i E2 med alle verdiene i A2: A9. Den returnerer en rekke TRUEs/FALSEs hvor TRUE returneres når det er en eksakt samsvar. I dette tilfellet, hvor verdien i E2 er Matt, vil den returnere følgende matrise:
{SANN; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK}. - EKSAKT (E2, $ A $ 2: $ A $ 9)*(RAD ($ A $ 2: $ A $ 9) - Denne delen multipliserer matrisen TRUEs/FALSEs med radnummeret til A2: A9. Hvor det er en TRUE, er det gir radnummeret, ellers gir det 0. I dette tilfellet vil det returnere {2; 0; 0; 0; 0; 0; 0; 0}.
- MAX (EXACT (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9)))) - Denne delen returnerer maksimalverdien fra tallmassen. I dette tilfellet vil det returnere 2 (som er radnummeret der det er en eksakt samsvar).
- Nå bruker vi ganske enkelt dette tallet som oppslagsverdi og bruker oppslagsarrayen som B2: C9.
Merk: Du kan sette inn hjelpekolonnen hvor som helst i datasettet. Bare sørg for at den er til venstre for kolonnen der du vil hente dataene. Du må deretter justere kolonnenummeret i VLOOKUP -funksjonen tilsvarende.
Hvis du ikke er fan av hjelperkolonne, kan du også gjøre et sakssensitivt oppslag uten hjelperkolonnen.
Gjør VLOOKUP saksfølsom - Uten hjelperkolonnen
Selv når du ikke vil bruke hjelperkolonnen, må du fortsatt ha en virtuell hjelperkolonne. Denne virtuelle kolonnen er ikke en del av regnearket, men er konstruert i formelen (som vist nedenfor).
Her er formelen som gir deg resultatet uten hjelperkolonnen:
= VLOOKUP (MAKS (EKSAKT (D2, $ A $ 2: $ A $ 9)*(RAD ($ A $ 2: $ A $ 9)))), VELG ({1,2}, RAD ($ A $ 2: $ A $ 9) , $ B $ 2: $ B $ 9), 2,0)
Hvordan virker dette?
Formelen bruker også begrepet en hjelperkolonne. Forskjellen er at i stedet for å sette hjelperkolonnen i regnearket, betrakter den som virtuelle hjelperedata som er en del av formelen.
Her er delen som fungerer som hjelperdata (markert med oransje):
= VLOOKUP (MAKS (EKSAKT (D2, $ A $ 2: $ A $ 9)*(RAD ($ A $ 2: $ A $ 9))),VELG ({1,2}, RAD ($ A $ 2: $ A $ 9), $ B $ 2: $ B $ 9),2,0)
La meg vise deg hva jeg mener med virtuelle hjelperdata.
I illustrasjonen ovenfor, mens jeg velger VELG delen av formelen og trykker på F9, viser det resultatet som VELG formelen ville gi.
Resultatet er {2,38; 3,88; 4,57; 5,82; 6,55; 7,44; 8,75; 9,38}
Det er en matrise der et komma representerer neste celle i samme rad og semikolon representerer at følgende data er i neste rad. Derfor oppretter denne formelen to kolonner med data - En kolonne har radnummeret og en har den matematiske poengsummen.
Når du bruker VLOOKUP -funksjonen, ser den ganske enkelt etter oppslagsverdien i den første kolonnen (av disse virtuelle 2 -kolonnedataene) og returnerer den tilsvarende poengsummen. Oppslagsverdien her er et tall som vi får fra kombinasjonen av MAX og EXACT -funksjonen.
Last ned eksempelfilen
Er det noen annen måte du vet å gjøre dette på? Hvis ja, del med meg i kommentarfeltet.
Du kan også like følgende VLOOKUP -opplæringsprogrammer:
- Bruke VLOOKUP -funksjon med flere kriterier.
- Bruke VLOOKUP -funksjon for å få det siste nummeret i en liste.
- VLOOKUP Vs. INDEKS/MATCH
- Bruk IFERROR med VLOOKUP for å bli kvitt #N/A -feil.
