Se video – Hvordan bruke VLOOKUP-funksjonen med flere kriterier

Excel VLOOKUP-funksjon, i sin grunnleggende form, kan se etter en oppslagsverdi og returnere den tilsvarende verdien fra den angitte raden.

Men ofte er det behov for å bruke Excel VLOOKUP med flere kriterier.

Slik bruker du VLOOKUP med flere kriterier

Anta at du har data med studentenes navn, eksamenstype og matematikkscore (som vist nedenfor):

Å bruke VLOOKUP-funksjonen for å få matematikkpoengene for hver student for respektive eksamensnivå kan være en utfordring.

Man kan argumentere at et bedre alternativ ville være å restrukturere datasettet eller bruke en pivottabell. Hvis det fungerer for deg, ikke noe sånt. Men i mange tilfeller sitter du fast med dataene du har, og pivottabellen er kanskje ikke et alternativ.

I slike tilfeller er denne opplæringen noe for deg.

Nå er det to måter du kan få oppslagsverdien ved å bruke VLOOKUP med flere kriterier.

• Ved hjelp av en hjelpekolonne.
• Bruk av VELG-funksjonen.

VLOOKUP med flere kriterier – Ved hjelp av en hjelpekolonne

Jeg er en fan av hjelpekolonner i Excel.

Jeg finner to viktige fordeler ved å bruke hjelpekolonner fremfor matriseformler:

• Det gjør det enkelt å forstå hva som skjer i regnearket.
• Det gjør det raskere sammenlignet med matrisefunksjonene (merkbar i store datasett).

Ikke misforstå meg nå. Jeg er ikke imot matriseformler. Jeg elsker de fantastiske tingene kan gjøres med matriseformler. Det er bare det at jeg lagrer dem til spesielle anledninger når alle andre alternativer ikke hjelper.

Når vi kommer tilbake til spørsmålet i punktet, er det nødvendig med hjelpekolonnen for å lage en unik kvalifisering. Denne unike kvalifiseringen kan deretter brukes til å slå opp den riktige verdien. For eksempel er det tre Matt i dataene, men det er bare en kombinasjon av Matt og Unit Test eller Matt og Mid-Term.

Her er trinnene:

Hvordan fungerer dette?

Vi lager unike kvalifikasjoner for hver forekomst av et navn og eksamen. I VLOOKUP-funksjonen som ble brukt her, ble oppslagsverdien endret til $ F3 & ”|” & G $ 2 slik at begge oppslagskriteriene kombineres og brukes som en enkelt oppslagsverdi. For eksempel er oppslagsverdien for VLOOKUP-funksjonen i G2 Matt | Enhetstest. Nå brukes denne oppslagsverdien for å få poengsummen fra C2: D19.

Avklaringer:

Det er et par spørsmål som du sannsynligvis kommer til å tenke på, så jeg trodde jeg vil prøve å svare på det her:

• Hvorfor har jeg brukt | symbol mens du blir med på de to kriteriene? – Under noen usedvanlig sjeldne (men mulige) forhold kan det hende du har to kriterier som er forskjellige, men som ender med å gi det samme resultatet når de kombineres. Her er et veldig enkelt eksempel (tilgi meg for min manglende kreativitet her):

Merk at mens A2 og A3 er forskjellige og B2 og B3 er forskjellige, kombinasjonene blir det samme. Men hvis du bruker en separator, ville til og med kombinasjonen være annerledes (D2 og D3).

• Hvorfor satte jeg inn hjelpekolonnen mellom kolonne B og C og ikke helt til venstre? – Det er ingen skade å sette inn hjelpekolonnen ytterst til venstre. Faktisk, hvis du ikke vil temperere med de opprinnelige dataene, bør det være veien å gå. Jeg gjorde det da det får meg til å bruke færre antall celler i VLOOKUP-funksjonen. I stedet for å ha 4 kolonner i tabelloppsettet, kunne jeg klare meg med bare to kolonner. Men det er bare meg.

Nå er det ingen størrelse som passer alle. Noen mennesker foretrekker kanskje å ikke bruke noen hjelpekolonne mens de bruker VLOOKUP med flere kriterier.

Så her er ikke-hjelpekolonnemetoden for deg.

Last ned eksempelfilen

VLOOKUP med flere kriterier – Bruke VELG-funksjonen

Bruke matriseformler i stedet for hjelper kolonner sparer regnearkets eiendom, og ytelsen kan være like god hvis den brukes færre ganger i en arbeidsbok.

Tatt i betraktning det samme datasettet som brukt ovenfor, her er formelen som gir deg resultatet :

= VLOOKUP ($ E3 & ”|” & F $ 2, VELG ({1,2} , $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19 ), 2,0)

Siden dette er en matriseformel, bruk den med Control + Shift + Enter, i stedet for bare Enter.

Hvordan fungerer dette?

Formelen bruker også konseptet med en hjelpekolonne. Forskjellen er at i stedet for å plassere hjelpekolonnen i regnearket, kan du betrakte det som virtuelle hjelperdata som er en del av formelen.

La meg vise deg hva jeg mener med virtuelle hjelperdata.

I illustrasjonen ovenfor, når jeg velger VELG delen av formelen og trykker på F9, viser det resultatet at VELG formel vil gi.

Resultatet er {«Matt | Unit Test», 91; «Bob | Unit Test», 52; ……}

Det er en matrise der et komma representerer neste celle i samme rad, og semikolon representerer at følgende data er i neste kolonne. Derfor oppretter denne formelen to kolonner med data – En kolonne har den unike identifikatoren og en har poengsummen.

Nå , når du bruker VLOOKUP-funksjonen, ser den bare etter verdien i den første kolonnen (av denne virtuelle 2 kolonnedataene) og returnerer den tilsvarende poengsummen.

Last ned Eksempelfil

Du kan også bruke andre formler for å slå opp med flere kriterier (for eksempel INDEX / MATCH eller SUMPRODUCT).