Sådan bruges VLOOKUP med flere kriterier i Excel

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

Excel VLOOKUP -funktion kan i sin grundform 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 en data med elevernes navn, eksamentype og matematik score (som vist nedenfor):

Det kan være en udfordring at bruge funktionen VLOOKUP til at få den matematiske score for hver elev for de respektive eksamensniveauer.

Man kan argumentere for, at en bedre mulighed ville være at omstrukturere datasættet eller bruge en pivottabel. Hvis det virker 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ælperkolonne.
• Brug af funktionen VÆLG.

VLOOKUP med flere kriterier - Brug af en hjælperkolonne

Jeg er fan af hjælperkolonner i Excel.

Jeg finder to væsentlige fordele ved at bruge hjælperkolonner frem for matrixformler:

• Det gør det let at forstå, hvad der foregår i regnearket.
• Det gør det hurtigere sammenlignet med matrixfunktionerne (kan ses i store datasæt).

Nu skal du ikke misforstå mig. Jeg er ikke imod matrixformler. Jeg elsker de fantastiske ting, der kan gøres med array -formler. Det er bare det, at jeg gemmer dem til særlige lejligheder, når alle andre muligheder ikke hjælper.

For at vende tilbage til spørgsmålet i punkt, er hjælperkolonnen 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:

• Indsæt en hjælperkolonne mellem kolonne B og C.
• Brug følgende formel i hjælperkolonnen: = A2 & ”|” & B2
  • Dette ville skabe unikke kvalifikatorer for hver instans som vist nedenfor.
• Brug følgende formel i G3 = VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
• Kopiér for alle cellerne.

Hvordan virker det?

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

Præciseringer:

Der er et par spørgsmål, der sandsynligvis vil komme til at tænke på dig, så jeg tænkte, at 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) tilstande kan du have to kriterier, der er forskellige, men ender med at give det samme resultat, når de kombineres. Her er et meget enkelt eksempel (tilgiv mig for min mangel på kreativitet her):

Bemærk, at mens A2 og A3 er forskellige, og B2 og B3 er forskellige, ender kombinationerne 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ælperkolonnen mellem kolonne B og C og ikke yderst til venstre? - Der er ingen skade ved at indsætte hjælperkolonnen yderst til venstre. Faktisk, hvis du ikke vil temperere med de originale data, burde det være vejen at gå. Jeg gjorde det, da det får mig til at bruge et mindre antal celler i VLOOKUP -funktionen. I stedet for at have 4 kolonner i tabellen array, kunne jeg klare mig med kun 2 kolonner. Men det er bare mig.

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

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

Download eksempelfilen

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

Ved at bruge matrixformler i stedet for hjælperkolonner sparer du regnearksejendomme, og ydelsen kan være lige god, hvis den bruges færre gange i en projektmappe.

I betragtning af det samme datasæt som brugt ovenfor, her er 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 virker det?

Formlen bruger også begrebet en hjælperkolonne. Forskellen er, at i stedet for at sætte hjælperkolonnen i regnearket, betragter det som virtuelle hjælperdata, der er en del af formlen.

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

I ovenstående illustration, da jeg vælger VÆLG delen af ​​formlen og trykker på F9, viser det resultatet, som VÆLG formlen ville give.

Resultatet er {"Matt | Unit Test", 91; "Bob | Unit Test", 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 skaber denne formel 2 kolonner med data - En kolonne har den unikke identifikator, og den ene har scoren.

Når du nu bruger VLOOKUP -funktionen, leder den simpelthen efter værdien i den første kolonne (af disse virtuelle 2 kolonnen data) og returnerer den tilsvarende score.

Download eksempelfilen

Du kan også bruge andre formler til at opslag med flere kriterier (f.eks. INDEX/MATCH eller SUMPRODUCT).

Er der nogen anden måde du ved, hvordan du gør dette på? Hvis ja, så del med mig i kommentarfeltet.

Du kan også lide følgende LOOKUP -vejledninger:

• VLOOKUP Vs. INDEX/MATCH
• Få flere opslagsværdier uden gentagelse i en enkelt celle.
• Sådan gør du VLOOKUP Case Sensitive.
• Brug IFERROR med VLOOKUP for at slippe af med #N/A -fejl.