Slik slår du opp flere verdier i Excel: Trinn-for-trinn

Når du arbeider med store sett med data i Microsoft Excel, kan det være utfordrende å finne flere verdier som samsvarer med flere forhold. Noen innebygde funksjoner, f.eks. VLOOKUP, ble opprinnelig designet for å fungere med enkeltverdier.

Flere kan kombineres for å slå opp flere verdier. De inkluderer funksjonene VLOOKUP, INDEX, MATCH og IF. Gjeldende versjoner av Excel kan bruke dynamiske matriser, mens eldre versjoner bruker matriseformler.

Denne artikkelen vil vise deg nøyaktig hvordan du bruker disse funksjonene i formler som finner flere verdier i dataene dine.

La oss gå!

Innholdsfortegnelse

• Hvordan bruke VLOOKUP med flere verdier
  • Hva er matriseformler?
  • VLOOKUP Eksempel
  • Siste versjoner av Excel
  • Eldre versjoner
  • Eksakt samsvar vs. omtrentlig samsvar
  • Mer om VLOOKUP
• Hvordan bruke INDEX og MATCH for å slå opp flere verdier
  • Slik bruker du INDEX og MATCH Together i Excel 365
  • Arbeide med eldre versjoner av Excel
• Hvordan bruke IF-funksjonen til å finne flere verdier
  • Gjeldende versjoner av Excel
  • Eldre versjoner av Excel
• Slik bruker du SUMPRODUCT for flere kriterier
• 3 vanlige feil med oppslagsfunksjoner
  • 1. #N/A feil
  • 2. #REF! Feil
  • 3. Sirkulære feil
• Hvordan bruke IFERROR med oppslagsfunksjoner
• 7 avanserte oppslagsteknikker
  • 1. Relativ posisjonsoppslag
  • 2. LITEN funksjon
  • 3. RAD-funksjon
  • 4. FILTER Funksjon
  • 5. Hjelpekolonner
  • 6. Dynamiske matriser
  • 7. Power Query
• Siste tanker

Hvordan bruke VLOOKUP med flere verdier

Funksjonen brukes ofte til å finne enkeltverdier i en rekke data. Du kan imidlertid også slå opp flere treff i Excel med denne oppslagsformelen.

Som standard vil den bare returnere den første samsvarende verdien den finner. Du kan imidlertid endre funksjonen for å returnere flere verdier ved å bruke en matriseformel.

Hva er matriseformler?

En matriseformel er en formel som kan utføre beregninger på datamatriser. Det kalles en matriseformel fordi den kan returnere en rekke resultater, i stedet for en enkelt verdi.

Det er flere enkle trinn når du lager en matriseformel:

1. Velg et celleområde for å søke.

2. Skriv inn formelen i formellinjen.

3. Trykk Ctrl + Shift + Enter for å fullføre det.

Syntaksen til en matriseformel ligner på en vanlig formel, men den inkluderer krøllete klammeparenteser {} rundt formelen. De krøllete klammeparentesene indikerer at formelen er en matriseformel og at den vil returnere en rekke verdier.

Eksemplene i denne artikkelen vil vise deg hvordan du bruker matriseformler riktig.

VLOOKUP Eksempel

Vårt eksempel har fem elementer i kolonne A i et regneark:

• eple

• Banan

• Brød

• Gulrot

• kirsebær

Oppgaven er å sjekke om tre spesifikke frukter er på denne listen: eple, banan og kirsebær.

Siste versjoner av Excel

VLOOKUP-syntaksen avhenger av hvilken versjon av Excel du bruker.

De nyeste versjonene ( Excel 365 eller Excel 2021 ) støtter dynamiske matriser. Denne funksjonen lar formler returnere flere resultater som "søler" inn i tilstøtende celler.

Dette er syntaksen (likningstegnet starter formelen):

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

• lookup_value: Verdien du vil slå opp.

• table_array: Hele datatabellen du vil søke i.

• col_index_num: Tabellkolonnenummeret i table_array som inneholder dataene du vil returnere.

• range_lookup: Valgfritt. Angir om du vil ha et eksakt samsvar eller et omtrentlig samsvar.

Vårt spesifikke eksempel bruker denne formelen:

=VLOOKUP({“eple”,,”banan”,”kirsebær”},A1:A5,1,FALSE)

Når formelen legges inn i celle B1, søles resultatene inn i cellene C1 og D1. Dette bildet viser eksempelet i aksjon:

Eldre versjoner

Hvis du bruker en eldre versjon av Excel som ikke støtter dynamiske matriser (f.eks. Excel 2019 eller tidligere), må du bruke en litt annen tilnærming med en matriseformel.

Følg disse instruksjonene:

1. Klikk på cellen der du vil vise resultatene for det første elementet (f.eks. kolonne B).

2. Skriv inn følgende formel uten å trykke Enter ennå: =VLOOKUP({“eple”,,”banan”,,”kirsebær”}, A1:A5, 1, FALSE)

3. Trykk Ctrl + Shift + Enter for å gjøre dette om til en matriseformel.

4. Kopier celle B1 og lim den inn i cellen under (eller bruk fyllhåndtakene).

Når du bruker Ctrl + Shift + Enter, legger Excel til krøllete klammeparenteser rundt formelen. Dette indikerer at det er en matriseformel.

Eksakt samsvar vs. omtrentlig samsvar

Som standard bruker VLOOKUP-funksjonen et omtrentlig samsvar. Dette betyr at det vil returnere det nærmeste samsvaret det kan finne, selv om celleverdiene ikke er et eksakt samsvar.

Hvis du vil utføre et eksakt samsvar, kan du sette range_lookup-argumentet til FALSE.

Husk at omtrentlige treff fungerer best med ordnede numeriske verdier. Det er vanligvis ikke hensiktsmessig når celleverdien er tekst.

Mer om VLOOKUP

Hvis du vil lære mer om denne allsidige funksjonen, sjekk ut disse artiklene:

Nå som du er satt med VLOOKUP-funksjonen, la oss ta en titt på to andre funksjoner som kan gjøre det den gjør på en annen måte: INDEX og MATCH.

Hvordan bruke INDEX og MATCH for å slå opp flere verdier

Du kan kombinere INDEX- og MATCH-funksjonene sammen for å finne flere verdier i flere rader.

INDEKS-funksjonen i Excel returnerer en verdi eller referanse til en celle innenfor et spesifisert område.

=INDEKS(matrise; radnummer; [kolonne_nummer])

• array: Området med celler som skal søkes etter verdien.

• row_num: Radnummeret i matrisen som en verdi skal returneres fra.

• column_num: (Valgfritt) Kolonnenummeret i matrisen som en verdi skal returneres fra. Hvis den utelates, returnerer funksjonen hele raden.

MATCH-funksjonen i Excel returnerer posisjonen til en verdi innenfor et spesifisert område.

=MATCH(oppslagsverdi, oppslagsmatrise, [samsvarstype])

• lookup_value: Verdien det skal søkes etter i lookup_array.

• lookup_array: Området med celler som skal søkes etter oppslagsverdien.

• match_type: (Valgfritt) Typen match som skal utføres. Hvis den utelates, utfører funksjonen et eksakt samsvar.

Slik bruker du INDEX og MATCH Together i Excel 365

For å bruke INDEX og MATCH sammen for å slå opp flere verdier i Excel, må du bruke en matriseformel.

Når du arbeider med de tidligere eksempeldataene, er dette formelen i Excel 365:

=INDEKS(A1:A5, MATCH({"eple","banan","kirsebær"}, A1:A5, 0))

Eksemplet ovenfor brytes ned slik:

• INDEKS: dette returnerer verdien til en celle i et spesifisert område basert på et gitt rad- og kolonnenummer. I dette tilfellet vil den returnere verdien fra området A1:A5.

• A1:A5: Dette er det definerte tabellområdet der du søker etter verdien og som resultatet vil bli returnert fra.

• MATCH: dette søker etter et spesifisert element i et celleområde og returnerer den relative plasseringen til elementet i området.

• {“apple”,,”banana”,,”cherry”}: dette er matrisekonstanten som inneholder verdiene du vil slå opp.

• A1:A5: dette er området der MATCH vil søke etter verdiene fra matrisekonstanten.

• 0: dette er matchtypen for MATCH-funksjonen. I dette tilfellet er det 0, noe som betyr at du ser etter en eksakt match i stedet for en nær match.

 Dette bildet viser formelen i aksjon:

Arbeide med eldre versjoner av Excel

Hvis du bruker en eldre Excel-fil som ikke støtter dynamiske arrays (f.eks. Excel 2019 eller tidligere), må du bruke en annen tilnærming.

Fordi eldre versjoner ikke støtter formlene som "søler" inn i tilstøtende celler, må du dele ut bruken i tre separate formler.

Følg disse instruksjonene:

1. Klikk på cellen der du vil ha resultatet for det første elementet (f.eks. celle B1)

2. Skriv inn formelen nedenfor:

3. =INDEKS(A1:A5; MATCH("eple", A1:A5; 0))

4. Trykk Enter for å utføre formelen.

5. Skriv inn denne formelen i celle B2: =INDEKS(A1:A5, MATCH(“banan”, A1:A5, 0))

6. Skriv inn denne formelen i celle B3: =INDEKS(A1:A5, MATCH("kirsebær", A1:A5, 0))

Dette bildet viser cellereferanse B3:

INDEX- og MATCH-funksjonene er ikke de eneste som kan gjøre det mulig for deg å finne flere verdier. I neste avsnitt ser vi på hvordan du kan bruke IF-funksjonen som et alternativ.

Hvordan bruke IF-funksjonen til å finne flere verdier

En annen måte å slå opp flere celleverdier basert på visse kriterier er å bruke HVIS-funksjonen med andre funksjoner.

IF-funksjonen lar deg teste flere forhold og returnere forskjellige resultater avhengig av resultatet av disse testene.

La oss for eksempel si at du har en tabell med salgsdata med kolonner for produkt og salg. Du ønsker å slå opp og summere salgssummen for to av de tre produktene.

Gjeldende versjoner av Excel

For å finne summen av Sales-kolonnen der produktet er enten "Eple" eller "Banana" ved å bruke HVIS-funksjonen, kan du bruke en matriseformel med HVIS-, SUMME- og ELLER-funksjoner.

Forutsatt at dataene dine starter i celle A1, bruk følgende formel:

=SUM(HVIS((A2:A4=”Eple”)+(A2:A4=”Banan”), B2:B4, 0))

Seksjonen (A2:A4=”Apple”)+(A2:A4=”Banana”) lager en matrise som har en verdi på 1 hvis cellen i området A2:A4 inneholder “Eple” eller “Banana”, og 0 ellers .

IF-setningen kontrollerer hvert element i matriseargumentet. Hvis verdien er 1 (dvs. produktet er enten "Apple" eller "Banana"), tar det den tilsvarende verdien i Sales-kolonnen (område B2:B4); ellers tar det 0.

SUM-funksjonen legger sammen verdiene fra HVIS-funksjonen, og summerer effektivt salgsverdiene for både "Apple" og "Banana".

Dette bildet viser formelen i aksjon på søkeområdet:

Eldre versjoner av Excel

I Excel 2019 eller tidligere må du bruke en matriseformel. Følg disse instruksjonene:

1. Skriv inn formelen, men ikke trykk Enter.

2. Trykk Ctrl + Shift + Enter for å gjøre det til en matriseformel.

Excel legger til krøllede parenteser {} rundt formelen, noe som indikerer at det er en matriseformel.

Deretter ser vi på hvordan du kan bruke SUMPRODUCT til å slå opp flere verdier basert på kriteriene dine. La oss gå!

Slik bruker du SUMPRODUCT for flere kriterier

SUMPRODUCT-funksjonen lar deg også slå opp flere verdier basert på flere kriterier.

Fordi det ikke krever bruk av en matriseformel, er syntaksen den samme uavhengig av versjonen av Excel.

Ved å bruke de samme dataene som i forrige eksempel, ser formelen slik ut:

=SUMPRODUKT((A2:A4=”Eple”)+(A2:A4=”Banan”), B2:B4)

Seksjonen (A2:A4=”Apple”)+(A2:A4=”Banana”) lager en matrise som har en verdi på 1 hvis cellen i området A2:A4 inneholder “Eple” eller “Banana”, og 0 ellers .

SUMPRODUKT-funksjonen multipliserer elementene i arrayen med de tilsvarende elementene i Sales-kolonnen (område B2:B4). Den legger så sammen de resulterende verdiene, og summerer effektivt salgsverdiene for både "Apple" og "Banana".

Formelen nedenfor viser det i aksjon:

Excel-funksjoner er fantastiske når de fungerer som forventet, men noen ganger kan du støte på feil. I neste avsnitt dekker vi noen av de vanlige feilene og hvordan du kan håndtere dem.

3 vanlige feil med oppslagsfunksjoner

Oppslagsfunksjoner kan noen ganger returnere feil som kan være frustrerende og tidkrevende å feilsøke. De tre vanligste feilene du vil støte på er:

1. #N/A feil

2. #REF! Feil

3. Sirkulære feil

1. #N/A feil

#N/A-feilen oppstår når oppslagsverdien ikke kan finnes i oppslagsmatrisen.

Det er flere grunner til at denne feilen kan oppstå, inkludert:

• oppslagsverdien er feilstavet eller feil.

• oppslagsmatrisen er ikke sortert i stigende rekkefølge.

• oppslagsverdien er ikke i datasettet.

Når oppslagsverdien ikke er i datasettet, er dette nyttig informasjon. Imidlertid kan uerfarne Excel-brukere tro at #N/A betyr at noe har gått galt med formelen. Den neste delen viser hvordan du kan gjøre dette mer brukervennlig.

2. #REF! Feil

#REF! feil oppstår når oppslagsmatrisen eller returmatrisen slettes eller flyttes.

Denne feilen kan fikses ved å oppdatere cellereferansene i oppslagsfunksjonen.

3. Sirkulære feil

Når du kombinerer funksjoner i komplekse formler, kan Excel fortelle deg at du har en sirkulær referanse.

Du vil finne disse lettere å undersøke ved å bruke vår guide til .

Hvordan bruke IFERROR med oppslagsfunksjoner

IFERROR-funksjonen er et nyttig verktøy for å håndtere feil i oppslagsfunksjoner. Den lar deg spesifisere en verdi eller formel som skal returneres hvis oppslagsfunksjonen returnerer en feil.

Syntaksen for IFERROR-funksjonen er som følger:

=IFERROR(verdi; verdi_hvis_feil)

· verdi: verdi eller formel du vil evaluere.

· Verdi_hvis_feil: verdien eller formelen som skal returneres hvis det første argumentet returnerer en feil.

La oss for eksempel si at du har en VLOOKUP-funksjon som søker opp flere verdier i en tabell. På bildet nedenfor finnes ikke en av verdiene i det søkte dataområdet.

Som du kan se, vises #N/A-feilen, noe som kan være forvirrende for uerfarne Excel-brukere.

I stedet kan du bruke IFERROR til å vise en tom celle eller en melding som sier "Ikke funnet" med denne syntaksen:

=IFERROR(VLOOKUP(oppslagsverdi, tabellmatrise, kolonneindeks, FALSE), “Ikke funnet”)

I dette eksemplet, hvis VLOOKUP-funksjonen returnerer en feil, vil IFERROR-funksjonen returnere meldingen "Ikke funnet" i stedet.

Dette bildet viser formelen i aksjon. Kolonne B har den manglende verdien, mens kolonne C og kolonne D har funnet samsvar.

Vi har dekket mye bakken så langt, og du er endelig klar til å lære mer avanserte teknikker for oppslag, som er temaet i neste seksjon.

7 avanserte oppslagsteknikker

Å slå opp flere verdier i Excel kan være en utfordrende oppgave, spesielt når du arbeider med store datasett. Du kan støte på ytelsesproblemer med langsom behandling.

Det er syv avanserte oppslagsteknikker du kan bruke for å gjøre prosessen enklere og mer effektiv.

• Relativt posisjonsoppslag

• LITEN funksjon

• ROW-funksjon

• FILTER Funksjon

• Hjelperkolonner

• Dynamiske matriser

• Power Query

1. Relativ posisjonsoppslag

En av de enkleste er å bruke relativ posisjonsoppslag. Dette innebærer å spesifisere rad- og kolonneforskyvninger fra en gitt celle for å finne ønsket verdi.

For eksempel, hvis du har en tabell med data og ønsker å slå opp en verdi som er to rader ned og tre kolonner til høyre for en gitt celle, kan du bruke følgende formel:

=OFFSET(celle; 2; 3)

2. LITEN funksjon

En annen nyttig teknikk for oppslag med flere verdier er å bruke SMALL-funksjonen. Denne funksjonen returnerer den n'te minste verdien i et celleområde.

Ved å kombinere denne funksjonen med andre oppslagsfunksjoner som INDEX og MATCH, kan du slå opp flere verdier basert på spesifikke kriterier.

For eksempel ser følgende formel opp den nest minste verdien i et celleområde som oppfyller en bestemt betingelse:

=INDEKS(data, MATCH(LITEN(HVIS(kriterier; område); 2); område; 0))

3. RAD-funksjon

ROW-funksjonen kan også brukes for flere verdioppslag i Excel. Denne funksjonen returnerer radnummeret til en gitt celle, som kan brukes til å referere til celler i en datatabell.

Følgende formel ser for eksempel opp en verdi i en tabell basert på en unik identifikator:

=INDEKS(data; MATCH(unik_identifikator; data[unik_identifikator], 0); kolonneindeksnummer)

4. FILTER Funksjon

FILTER-funksjonen er ikke tilgjengelig i eldre versjoner av Excel.

I Excel 365 kan du bruke den til å filtrere en rekke celler basert på bestemte kriterier, og returnere bare verdiene som oppfyller disse kriteriene. Dette er syntaksen og tre argumenter:

=FILTER(matrise, inkludere, [hvis_tom])

• array: De spesifikke dataene du vil filtrere.

• include: Inkluder-argumentet er kriteriene eller betingelsene du vil bruke på matrisen.

• [if_empty] (valgfritt): Verdien som skal returneres hvis ingen rader eller kolonner oppfyller kriteriene spesifisert i include-argumentet.

For eksempel fungerer følgende formel på eksempeldataene for å finne treff for to av de tre elementene i den første kolonnen og summere deres tilsvarende verdier i den andre kolonnen.

=SUM(FILTER(B2:B4, (A2:A4=”Eple”)+(A2:A4=”Banan”)))

Dette bildet viser hvor mange rader som matches og summen av de forskjellige verdiene:

5. Hjelpekolonner

Du kan bruke en hjelpekolonne til å slå sammen flere felt innenfor en funksjon som VLOOKUP.

Anta at du jobber med for- og etternavn i et eget ark. Du kan sette dem sammen i en hjelpekolonne som det refereres til i den endelige formelen.

6. Dynamiske matriser

Som du har lært i tidligere eksempler, kan Microsoft 365-brukere dra nytte av dynamiske matriser for flere verdioppslag i Excel.

Dynamiske matriser lar deg returnere flere verdier fra én enkelt formel, noe som gjør det enklere å slå opp store datamengder.

7. Power Query

Power Query er et kraftig verktøy i Excel som kan brukes til å returnere verdier basert på flere kriterier.

For eksempel finner denne videoen rotete data i et regneark og rydder det opp.

Siste tanker

Det avslutter vårt dypdykk i kunsten å slå opp flere verdier i Excel! Når du har mestret formlene for VLOOKUP, INDEX, MATCH og array, vil du finne deg selv å gå gjennom komplekse datasett som en varm kniv gjennom smør.

Nøkkelen er å forstå syntaksen og logikken bak hver formel. Husk at disse formlene kan være komplekse, så det er viktig å ta deg god tid og teste formlene dine grundig før du stoler på dem for viktig dataanalyse.

Excel er et kraftig verktøy, men det er bare så bra som brukeren. Så fortsett å finpusse disse ferdighetene, fortsett å eksperimentere, og du vil snart bli mester i oppslag med flere verdier. Til neste gang, fortsett å knuse disse tallene og få Excel til å gjøre det harde arbeidet for deg!