Se video – Sådan bruges VLOOKUP-funktion med flere kriterier

Excel VLOOKUP-funktion kan i sin grundlæggende form søge efter en opslagsværdi og returnere den tilsvarende værdi fra den angivne række.

Men ofte er der behov for at bruge Excel VLOOKUP med flere kriterier.

Sådan bruges VLOOKUP med flere kriterier

Antag at du har data med elevers navn, eksamens type og matematikscore (som vist nedenfor):

Brug af VLOOKUP-funktionen til at få matematikscore for hver elev for de respektive eksamensniveauer kan være en udfordring.

Man kan argumentere at en bedre mulighed ville være at omstrukturere datasættet eller bruge en pivottabel. Hvis det fungerer for dig, er der ikke noget lignende. Men i mange tilfælde sidder du fast med de data, du har, og pivottabellen er muligvis ikke en mulighed.

I sådanne tilfælde er denne vejledning noget for dig.

Nu er der to måder, du kan få opslagsværdien ved hjælp af VLOOKUP med flere kriterier.

• Brug af en hjælpekolonne.
• Brug af VÆLG-funktionen.

VLOOKUP med flere kriterier – Brug af en hjælpekolonne

Jeg er en fan af hjælpekolonner i Excel.

Jeg finder to betydelige fordele ved at bruge hjælpekolonner frem for matrixformler:

• Det gør det let at forstå, hvad der sker i regnearket.
• Det gør det hurtigere sammenlignet med array-funktionerne (synlig i store datasæt).

Gør mig ikke forkert. Jeg er ikke imod matrixformler. Jeg elsker de fantastiske ting, der kan gøres med matrixformler. Det er bare, at jeg gemmer dem til specielle lejligheder, når alle andre muligheder ikke er til nogen hjælp.

Når vi kommer tilbage til det aktuelle spørgsmål, er hjælpekolonnen nødvendig for at skabe en unik kvalifikator. Denne unikke kvalifikator kan derefter bruges til at slå den korrekte værdi op. For eksempel er der tre Matt i dataene, men der er kun en kombination af Matt og Unit Test eller Matt og Mid-Term.

Her er trinene:

Hvordan fungerer dette?

Vi opretter unikke kvalifikatorer til hver forekomst af et navn og eksamen. I VLOOKUP-funktionen, der blev brugt her, blev opslagsværdien ændret til $ F3 & ”|” & G $ 2, så begge opslagskriterier kombineres og bruges som en enkelt opslagsværdi. For eksempel er opslagsværdien for VLOOKUP-funktionen i G2 Matt | Enhedstest. Nu bruges denne opslagsværdi til at få scoren fra C2: D19.

Afklaringer:

Der er et par spørgsmål, der sandsynligvis kommer til at tænke dig, så jeg troede, jeg vil prøve at besvare det her:

• Hvorfor har jeg brugt | symbol, mens du slutter dig til de to kriterier? – Under nogle usædvanligt sjældne (men mulige) forhold kan du have to kriterier, der er forskellige, men ender med at give det samme resultat, når de kombineres. Her er et meget simpelt eksempel (tilgiv mig for min manglende kreativitet her):

Bemærk at mens A2 og A3 er forskellige og B2 og B3 er forskellige, kombinationerne ender med at være de samme. Men hvis du bruger en separator, ville selv kombinationen være anderledes (D2 og D3).

• Hvorfor indsatte jeg hjælpekolonnen mellem kolonne B og C og ikke yderst til venstre? – Der er ingen skade ved at indsætte hjælpekolonnen yderst til venstre. Faktisk, hvis du ikke vil temperere med de originale data, skal det være vejen at gå. Jeg gjorde det, da det får mig til at bruge færre antal celler i VLOOKUP-funktionen. I stedet for at have 4 kolonner i tabelarrayet kunne jeg klare mig med kun 2 kolonner. Men det er bare mig.

Nu er der ingen størrelse, der passer til alle. Nogle mennesker foretrækker muligvis ikke at bruge nogen hjælpekolonne, mens de bruger VLOOKUP med flere kriterier.

Så her er metoden til ikke-hjælpekolonne til dig.

Download eksempelfilen

VLOOKUP med flere kriterier – Brug af VÆLG-funktionen

Brug af arrayformler i stedet for hjælper kolonner gemmer dig regnearkfast ejendom, og ydeevnen kan være lige så god, hvis den bruges mindre antal gange i en projektmappe.

I betragtning af det samme datasæt som brugt ovenfor er her formlen, der giver dig resultatet :

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

Da dette er en matrixformel, skal du bruge den med Control + Shift + Enter i stedet for bare Enter.

Hvordan fungerer dette?

Formlen bruger også begrebet en hjælpekolonne. Forskellen er, at i stedet for at placere hjælpekolonnen i regnearket, skal du betragte det som virtuelle hjælperedata, der er en del af formlen.

Lad mig vise dig, hvad jeg mener med virtuelle hjælperedata.

I ovenstående illustration, når jeg vælger VÆLG del af formlen og trykker på F9, viser det resultatet, at VÆLG formel ville give.

Resultatet er {“Matt | Enhedstest”, 91; “Bob | Enhedstest”, 52; ……}

Det er en matrix, hvor et komma repræsenterer næste celle i samme række, og semikolon repræsenterer, at følgende data er i den næste kolonne. Derfor opretter denne formel 2 kolonner med data – En kolonne har den unikke identifikator, og en har scoren.

Nu , når du bruger VLOOKUP-funktionen, ser den simpelthen efter værdien i den første kolonne (af disse virtuelle 2 kolonnedata) og returnerer den tilsvarende score.

Download Eksempel på fil

Du kan også bruge andre formler til at søge efter flere kriterier (såsom INDEX / MATCH eller SUMPRODUCT).