Last ned eksemplet på arbeidsbok
Saksfølsom VLOOKUP - Excel
Denne opplæringen vil demonstrere hvordan du utfører en bokstavsensitiv VLOOKUP i Excel ved hjelp av to forskjellige metoder.
Metode 1 - Hovedsensitiv VLOOKUP med hjelperesøyle
= VLOOKUP (MAKS (EKSAKT (E2, $ B $ 2: $ B $ 7)*(RAD ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)
VLOOKUP -funksjon
VLOOKUP -funksjonen brukes til å slå opp en omtrentlig eller eksakt samsvar for en verdi i kolonnen lengst til venstre i et område og returnerer den tilsvarende verdien fra en annen kolonne. Som standard vil VLOOKUP bare fungere for verdier som ikke er store og små bokstaver:
Saksfølsom VLOOKUP
Ved å kombinere VLOOKUP, EXACT, MAX og ROW, kan vi lage en bokstavsensitiv VLOOKUP-formel som returnerer den tilsvarende verdien for vår store bokstaver. La oss gå gjennom et eksempel.
Vi har en liste over varer og tilhørende priser (legg merke til at varenummeret er unikt for store og små bokstaver):
Anta at vi blir bedt om å få prisen for en vare ved å bruke varenummeret slik:
For å oppnå dette må vi først opprette en hjelperkolonne ved hjelp av ROW:
= RAD () klikk og dra (eller dobbeltklikk) for å forhåndsutfylle alle radene i området
Deretter kombinerer du VLOOKUP, MAX, EXACT og ROW i en formel slik:
= VISNING (MAKS (EKSAKT (,)*(RAD ())) ,, 0)
= VLOOKUP (MAKS (EKSAKT (E2, $ B $ 2: $ B $ 7)*(RAD ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)
Hvordan fungerer formelen?
- EKSAKT -funksjonen kontrollerer element -ID -en i E2 (oppslagsverdi) mot verdiene i B2: B7 (oppslagsområde) og returnerer en matrise med SANN der det er en eksakt samsvar eller FLASE -er i en matrise {FLASE, FLASE, FLASE, FLASE, FLASE, TRUE}.
- Denne matrisen multipliseres deretter med RAD -matrisen {2, 3, 4, 5, 6, 7} (merk at denne matcher vår hjelpekolonne).
- MAX -funksjonen returnerer maksimalverdien fra den resulterende matrisen {0,0,0,0,0,7}, som er 7 i vårt eksempel.
- Deretter bruker vi resultatet som vår oppslagsverdi i en VLOOKUP og velger vår hjelperkolonne som oppslagsområde. I vårt eksempel returnerer formelen matchende verdi på $ 16,00.
Metode 2 - Saksfølsom VLOOKUP med "virtuell" hjelperkolonne
= VLOOKUP (MAKS (EKSAKT (D2, $ B $ 2: $ B $ 7)*(RAD ($ B $ 2: $ B $ 7)))), VELG ({1,2}, RAD ($ B $ 2: $ B $ 7) , $ C $ 2: $ C $ 7), 2,0)
Denne metoden bruker samme logikk som den første metoden, men eliminerer behovet for å opprette en hjelperkolonne og bruker i stedet VELG og RAD for å lage en "virtuell" hjelperkolonne slik:
= VISNING (MAKS (EKSAKT (,)*(RAD ())), VELG ({1,2}, RAD (),),, 0)
= VLOOKUP (MAKS (EKSAKT (D2, $ B $ 2: $ B $ 7)*(RAD ($ B $ 2: $ B $ 7)))), VELG ({1,2}, RAD ($ B $ 2: $ B $ 7) , $ C $ 2: $ C $ 7), 2,0)
Hvordan fungerer formelen?
- Den første delen av formelen fungerer på samme måte som den første metoden.
- Ved å kombinere VELG og RAD returneres en matrise med to kolonner, en for radnummeret og en annen for prisen. Matrisen er atskilt med et semikolon for å representere neste rad og et komma for neste kolonne slik: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
- Vi kan deretter bruke resultatet fra den første delen av formelen i en VLOOKUP for å finne den tilsvarende verdien fra vårt VELG og RAD -array.
Saksfølsom VLOOKUP i Google Regneark
Alle eksemplene ovenfor fungerer nøyaktig det samme i Google Regneark som i Excel.
