Hvordan sammenligne to kolonner i Excel ved hjelp av VLOOKUP

Når du arbeider med store datasett i Excel, må du kanskje sammenligne to kolonner for å finne likheter eller forskjeller.

VLOOKUP er en kraftig funksjon som lar deg søke etter samsvarende data mellom to kolonner. Funksjonen lar deg søke etter verdier i en kolonne som vises i en annen.

Denne artikkelen viser deg hvordan du sammenligner to kolonner ved hjelp av VLOOKUP, slik at du effektivt kan analysere dataene dine.

Innholdsfortegnelse

• Grunnleggende om Vlookup-funksjonen
  • Vlookup funksjon
• Forbereder dataene dine for VLOOKUP
  • Formatering av data
• Slik bruker du VLOOKUP for å sammenligne to kolonner
  • Håndtering av andre feil
• Alternativer til VLOOKUP for kolonnesammenligning
  • 1. Bruke indeks- og samsvarsfunksjoner
  • 2. Bruke XLOOKUP i Excel
• Fem tips for å forbedre VLOOKUP-ytelsen
  • 1. Begrens oppslagsområdet ditt
  • 2. Bruk absolutte referanser
  • 3. Sorter dataene dine
  • 4. Bruk en dobbel VLOOKUP
  • 5. Bruk betinget formatering
• Avansert bruk for VLOOKUP
• Vårt siste ord

Grunnleggende om Vlookup-funksjonen

Anta at du har et regneark med to lister over elementer i kolonne A og kolonne B. Du vil finne elementene i liste 1 som også vises i liste 2.

Du kan forestille deg at det vil være enormt tidkrevende å jobbe manuelt gjennom lister med tusenvis av varer. Heldigvis, bringer VLOOKUP til unnsetning!

Begrepet VLOOKUP står for Vertical Lookup. Funksjonen vil sammenligne to kolonner, finner samsvar mellom dem og returnerer de tilknyttede verdiene.

Vlookup funksjon

Her er en grunnleggende syntaks for VLOOKUP-funksjonen:

=VLOOKUP(oppslagsverdi, tabellmatrise, kolonneindeksnummer, [områdeoppslag])

Dette er en sammenbrudd av elementene:

• lookup_value : verdien du vil søke etter i den første kolonnen i table_array.

• table_array : celleområdet som inneholder dataene du vil søke i.

• col_index_num : kolonnenummeret i table_arrayen du vil returnere verdien fra.

• range_lookup : Valgfritt. Det er enten SANN (omtrentlig samsvar) eller FALSE (eksakt samsvar). Standard er TRUE.

Forbereder dataene dine for VLOOKUP

Før du bruker VLOOKUP til å sammenligne to , må du forberede dataene dine.

Lag to separate kolonner i regnearket ditt der du vil sammenligne verdiene. Denne artikkelen bruker kolonne A og kolonne B for våre eksempler.

Her er eksempeldataene som er brukt i denne artikkelen:

Dette er eksempeltabellen med overskrift som skal legges til i delen med tittelen "Forbereder dataene dine for VLOOKUP":

Liste 1 Liste 2
Artikkel 1 Artikkel 3
Artikkel
2 Artikkel 4 Artikkel 3 Artikkel 1
Artikkel 4 Artikkel 5

Formatering av data

Det er viktig å sikre at dataene i begge kolonnene er likt formatert. VLOOKUP skiller mellom store og små bokstaver, noe som betyr at store og små bokstaver betyr noe.

Det kan oppstå samsvarsfeil i sluttresultatet hvis formateringen er inkonsekvent.

Det er også en god idé å fjerne eventuelle dupliserte verdier eller tomme celler for å minimere risikoen for feil.

Slik fjerner du dupliserte verdier:

1. Velg kolonnen.

2. Gå til fanen Data i det øverste båndet.

3. Klikk på Fjern duplikater i delen Dataverktøy.

Knappen kan være litt vanskelig å se. Dette bildet vil hjelpe deg:

Slik fjerner du tomme celler:

1. Velg kolonnen.

2. Gå til Hjem-fanen på det øverste båndet.

3. Utvid "Finn og velg"-menyen.

4. Velg "Gå til Spesial".

5. Velg "Blanks" fra alternativene.

6. Klikk på "Slett" i Celler-delen.

Slik bruker du VLOOKUP for å sammenligne to kolonner

Når data er forberedt, kan du skrive VLOOKUP-formelen for å sammenligne to kolonner i Excel og identifisere treff. Følg disse instruksjonene:

1. Velg en celle i en ny kolonne der du vil vise sammenligningsresultatene (f.eks. celle C2).

2. Skriv inn følgende formel: =VLOOKUP(A2, B:B, 1, FALSE))

3. Trykk Enter for å bruke formelen.

4. I tilfelle en samsvarende verdi, vil verdien vises i samme rad i resultatkolonnen (f.eks. kolonne C).

5. Dra formelen ned fra C2 for å kopiere den over så mange celler du trenger.

Hvis du bruker prøvedataene våre, setter du sammenligningsresultatene i den tredje kolonnen. Formelen vil finne tre treff av de fire elementene i liste 1.

Legg merke til at element 2 vises som #N/A. Excel forteller oss at treffet ikke er aktuelt, dvs. at det ikke ble funnet.

Det er riktig og er nyttig informasjon om manglende verdier. Noen Excel-brukere kan imidlertid tro at det er et dataproblem eller funksjonsfeil.

Det er god praksis å vise en annen indikasjon på at ingen treff ble funnet. Det kan rett og slett være blankt.

For å gjøre det, kombiner VLOOKUP-funksjonen med IFNA-funksjonen slik:

=IFNA(VLOOKUP(A1; B:B; 1; FALSE); “”)

IFNA-funksjonen oppdager #N/A-feilen og erstatter utdataene med et tomt mellomrom (""). Du kan også bruke ISNA-funksjonen eller ha ekstra logikk med HVIS-funksjonen.

Håndtering av andre feil

VLOOKUP kan noen ganger generere andre feil i sammenligningsresultatene dine. #REF ! Feil i Excel er et reelt problem med dataene dine.

Det oppstår vanligvis når det angitte området er feil. Eksemplet vårt har referert til hele kolonnen, men du kan også bruke vertikale celleområder.

Sørg for at søkeområdet du referer til dekker alle verdiene du vil sammenligne.

Alternativer til VLOOKUP for kolonnesammenligning

Det er to hovedalternative oppslagsfunksjoner når du sammenligner to kolonner i Excel for å finne treff.

1. Bruke indeks- og samsvarsfunksjoner

I stedet for å bruke VLOOKUP, kan du sammenligne to kolonner i Excel ved å kombinere INDEX- og MATCH-funksjonene.

Denne metoden gir en mer fleksibel måte å slå opp data på og er spesielt nyttig når du arbeider med ikke-tilstøtende kolonner eller når kolonneindeksen kan endres.

Dette er syntaksen som skal legges inn i resultatkolonnen:

=INDEKS(returområde, MATCH(oppslagsverdi, oppslagsområde, 0))

• Return_range : celleområdet som inneholder dataene du vil returnere.

• Lookup_value : verdien du vil søke etter innenfor oppslagsområdet.

• Lookup_range : celleområdet du vil finne oppslagsverdien innenfor.

Ved å bruke de samme dataene i de foregående eksemplene erstatter vi VLOOKUP-formelen som følger:

=(INDEKS(B:B; MATCH(A2; B:B; 0)))

MATCH-funksjonen returnerer den relative posisjonen til oppslagsverdien innenfor oppslagsområdet, og INDEKS-funksjonen returnerer den tilsvarende verdien fra returområdet.

Resultatene på de samme dataene vil være de samme som VLOOKUP vi brukte tidligere. Dette bildet viser funksjonene som er i bruk:

Du kan også erstatte #N/A-feilen med en egendefinert melding. Her er et eksempel på bruk av IFERROR-funksjonen.

=FEIL(INDEKS(B:B, MATCH(A2, B:B, 0)), "Ikke funnet")

2. Bruke XLOOKUP i Excel

For brukere med Excel 365 eller Excel 2019 er XLOOKUP en alternativ logisk test til VLOOKUP for å finne vanlige verdier i to kolonner.

XLOOKUP gir flere fordeler. Du kan bruke den til å søke i data både horisontalt og vertikalt, arbeide med ikke-tilstøtende kolonner og spesifisere egendefinerte verdier for feil.

Syntaksen til XLOOKUP er:

=XLOOKUP(oppslagsverdi, oppslagsområde, returområde, [hvis_ikke_funnet], [samsvarsmodus], [søkemodus])

• lookup_value : verdien du vil søke etter innenfor oppslagsområdet.

• lookup_range : celleområdet du vil finne oppslagsverdien innenfor.

• return_range : celleområdet som inneholder dataene du vil returnere.

• Legg til egendefinerte feilverdier, samsvarsmodus og søkemodusparametere etter behov.

XLOOKUP vil finne oppslagsverdien innenfor oppslagsområdet og returnere tilsvarende verdi fra returområdet.

Formelen er spesielt nyttig når du har to lister innebygd i datasett med flere kolonner. I våre tidligere eksempler har listene vært i første kolonne og andre kolonne, men arket ditt kan ha mer data enn det.

Her er et eksempel med to lister i første og tredje kolonne i et regneark:

XLOOKUP-formelen ser slik ut:

=XLOOKUP(A2;C:C;C:C,"")

Dette bildet viser resultatet med sammenligningen i kolonne D. Den første verdien er til stede, men den andre mangler.

Merk at jeg ikke har noen ekstra feilformler, men den manglende verdien vises som tom. Det er fordi jeg bruker den fjerde parameteren som er en egendefinert feilverdi. I dette tilfellet er det en tom streng.

Som en bonus vil jeg vise deg hvordan du bruker XLOOKUP for å sammenligne på tvers av rader. Hvis du har to lister som er i rad 1 og rad 2, ser den enkleste bruken av formelen slik ut:

=XLOOKUP(B1; 2:2; 2:2; “”)

Dette bildet viser resultatene på de to radene.

Med sin forbedrede funksjonalitet og fleksibilitet er XLOOKUP et utmerket alternativ til VLOOKUP for å sammenligne lister i Excel for å finne ut om treff er tilstede.

Fem tips for å forbedre VLOOKUP-ytelsen

Optimalisering av VLOOKUP-funksjonen kan hjelpe deg med å unngå lange ventetider og forbedre den generelle responsen til Excel-regnearket.

Her er seks tips og triks du kan bruke for å forbedre ytelsen til VLOOKUP i Excel.

1. Begrens oppslagsområdet ditt

Jeg brukte hele kolonner i eksemplene for å gjøre dem enkle. Hvis du jobber med store mengder data, bør du unngå å gjøre det. Bruk av hele kolonner kan bremse Excels beregningsprosess.

Prøv i stedet å bruke det nøyaktige området som kreves for dataene dine (f.eks. A1:A100). Dette reduserer antallet celler som VLOOKUP-funksjonen din trenger for å evaluere.

2. Bruk absolutte referanser

Når du spesifiserer et dataområde (f.eks. cellene fra B2 til B5), bruk absolutte referanser. Dette sikrer at formelen er konsistent og at data eksisterer når du kopierer den på tvers av flere celler.

Her er et eksempel:

=SØK OPP(A2; $B$2:$B$5; 1; FALSE)

3. Sorter dataene dine

Hvis du vet at dataene i oppslagskolonnen er sortert i stigende rekkefølge, kan du bruke VLOOKUP med TRUE eller 1 for 'range_lookup'-argumentet.

Dette vil føre til at Excel utfører et omtrentlig samsvar, som er raskere enn et eksakt samsvar på tvers av alle cellene. Vær imidlertid forsiktig når du bruker dette alternativet, siden feil sortering kan føre til feil resultater.

4. Bruk en dobbel VLOOKUP

Du kan bruke to VLOOKUP-funksjoner for å fremskynde søkeprosessen i Excel.

Den første VLOOKUP vil avgjøre om oppslagsverdien eksisterer ved å sette 'col_index_num' til 1 og 'range_lookup' til TRUE.

Hvis den returnerer TRUE, henter en andre VLOOKUP den ønskede verdien med 'range_lookup' satt til TRUE.

5. Bruk betinget formatering

Du kan bruke betinget formatering i Excel-regnearket for å markere samsvarende eller manglende verdier i den angitte kolonnen. Du kan også bruke farger på unike verdier. Dette gjør dataene dine lettere å lese.

Du finner den betingede formateringsmenyen i stilgruppen på Hjem-fanen.

Avansert bruk for VLOOKUP

Sammenkoblede prøver T-tester brukes til å sammenligne gjennomsnittet av to relaterte prøver. Denne videoen viser bruken deres i Excel.

Hvis du har flere data eller variabler du må referere til under analysen, kan du bruke VLOOKUP for å hente de nødvendige verdiene fra en annen tabell eller regneark.

Du kan for eksempel bruke VLOOKUP for å hente demografisk informasjon eller behandlingsforhold for hver parede observasjon i datasettet ditt.

Vårt siste ord

Nå har du en solid forståelse av bruken av VLOOKUP. Denne kraftige funksjonen lar deg raskt identifisere forskjeller og samsvarende verdier mellom to lister, noe som gjør dataanalyse mer effektiv og nøyaktig.

Du så også eksempler på andre oppslags- og referansefunksjoner, for eksempel XLOOKUP, INDEX og MATCH. Hvis du legger til disse elementene til ferdighetene dine, vil du ytterligere styrke dataanalysemulighetene dine.

Fortsett å øve og avgrense VLOOKUP-ferdighetene dine, og du vil snart bli en ekspert på å sammenligne kolonner i Excel, spare tid og øke produktiviteten.