Disse eksemplene vil lære deg hvordan du Vlookup flere kriterier, returnerer en bestemt forekomst eller alle treff, gjør dynamisk Vlookup i flere ark og mer.
Det er den andre delen av serien som vil hjelpe deg med å utnytte kraften i Excel VLOOKUP. Eksemplene antyder at du vet hvordan denne funksjonen fungerer. Hvis ikke, er det grunn til å begynne med de grunnleggende bruken av VLOOKUP i Excel.
Før jeg går videre, la meg kort minne om syntaksen:
Nå som alle er på samme side, la oss se nærmere på de avanserte eksemplene på VLOOKUP-formelen:
- Hvordan Vlookup flere kriterier i Excel
- Vlookup og retur nte kamp
- Vlookup flere verdier
- Vlookup basert på rad- og kolonneverdier
- Nestet Vlookup-formel
- VLOOKUP og INDIRECT for å hente data fra flere ark
Slik Vlookup flere kriterier
Excel VLOOKUP-funksjonen er veldig nyttig når det gjelder å søke på tvers av en database for en viss verdi. Den mangler imidlertid en viktig funksjon – syntaksen tillater bare en oppslagsverdi. Men hva om du vil slå opp med flere forhold? Det er noen forskjellige løsninger du kan velge mellom.
Formel 1. VLOOKUP basert på tw o verdier
Anta at du har en liste over ordrer og ønsker å finne antall basert på to kriterier, kundenavn og produkt. En kompliserende faktor er at hver kunde bestilte flere produkter, som vist i tabellen nedenfor:
En vanlig VLOOKUP-formel vil ikke fungere i dette situasjon fordi den returnerer det første funnet samsvaret basert på en enkelt oppslagsverdi du angir.
For å overvinne dette kan du legge til en hjelpekolonne og sammenkoble verdiene fra to oppslagskolonner (kunde og produkt) der. Det er viktig at hjelpekolonnen skal være kolonnen lengst til venstre i tabelloppsettet fordi det er der Excel VLOOKUP alltid søker etter oppslagsverdien.
Så legg til en kolonne til venstre for tabellen og kopier formelen nedenfor over den kolonnen. Dette vil fylle hjelpekolonnen med verdiene fra kolonne B og C (mellomromstegnet er sammenkoblet i mellom for bedre lesbarhet):
=B2&" "&C2
Bruk deretter en standard VLOOKUP-formel og legg begge kriteriene i argumentet lookup_value, atskilt med et mellomrom:
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
Eller skriv inn kriteriene i separate celler (G1 og G2 i vårt tilfelle) og sammenkoble disse cellene:
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
Når vi ønsker å returnere en verdi fra kolonne D, som er fjerde i tabellmatrisen, bruker vi 4 for col_index_num. Argumentet range_lookup er satt til FALSE til Vlookup en nøyaktig samsvar. Skjermbildet nedenfor viser resultatet:
Hvis oppslagstabellen din er i et annet ark, inkluderer du arkets navn i VLOOKUP-formelen. For eksempel:
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
Alternativt kan du opprette et navngitt område for oppslagstabellen (si ordrer) for å gjøre formelen enklere- å lese:
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
For mer informasjon, se Hvordan Vlookup fra et annet ark i Excel.
Formel 2. Excel VLOOKUP med flere forhold
I teorien kan du bruke ovennevnte tilnærming til Vlookup mer enn to kriterier. uansett, det er et par forbehold. For det første er en oppslagsverdi begrenset til 255 tegn, og for det andre kan det hende at regnearkets design ikke tillater å legge til en hjelpekolonne.
Heldigvis gir Microsoft Excel ofte mer enn en måte å gjøre det samme på. For å Vlookup flere kriterier, kan du bruke enten en INDEX MATCH-kombinasjon eller XLOOKUP-funksjonen som nylig ble introdusert i Office 365.
For eksempel å slå opp basert på 3 forskjellige verdier (dato, kundenavn og produkt), bruk en av følgende formler:
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
Hvor:
For detaljert forklaring av formlene, se:
- XLOOKUP med flere kriterier
- INDEX MATCH formel med flere kriterier
Hvordan bruke VLOOKUP for å få andre, tredje eller nte kamp
Som du allerede vet, kan Excel VLOOKUP bare hente en samsvarende verdi, mer presist, den returnerer den første funnet. Men hva om det er flere treff i oppslagsmatrisen, og du vil få 2. eller 3. forekomst? Oppgaven høres ganske intrikat ut, men løsningen eksisterer!
Formel 1. Vlookup Nth-forekomst
Anta at du har kundenavn i en kolonne, produktene de kjøpte i en annen, og du ønsker å finne det andre eller tredje produktet kjøpt av en gitt kunde.
Den enkleste måten er å legge til en hjelpekolonne til venstre for tabellen som vi gjorde i det første eksemplet. Men denne gangen vil vi fylle den ut med kundenavn og forekomstnumre som «John Doe1», «John Doe2» osv.
For å få forekomsten, bruk COUNTIF-funksjonen med en referanse for blandet område ( første referanse er absolutt og den andre er relativ som $ B $ 2: B2). Siden den relative referansen endres basert på en posisjon i cellen der formelen kopieres, blir den i rad 3 $ B $ 2: B3, i rad 4 – $ B $ 2: B4, og så videre.
Sammenkoblet med kundenavnet (B2), har formelen denne formen:
=B2&COUNTIF($B$2:B2, B2)
Formelen ovenfor går til A2, og deretter kopierer du den ned til så mange celler som nødvendig.
Deretter skriver du inn målnavnet og forekomstnummeret i separate celler (F1 og F2), og bruker formelen nedenfor for å Vlookup en spesifikk forekomst:
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
Formel 2. Vlookup 2. forekomst
Hvis du leter etter 2. forekomst av oppslagsverdien, kan du klare deg uten hjelpekolonnen. I stedet lager du tabellmatrisen dynamisk ved å bruke INDIRECT-funksjonen sammen med MATCH:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
Hvor:
- E1 er oppslagsverdien
- A2: A11 er oppslagsområdet
- B11 er den siste (nederst til høyre) cellen i oppslagstabellen
Vær oppmerksom på at formelen ovenfor er skrevet for et bestemt tilfelle der dataceller i oppslagstabellen begynner i rad 2. Hvis tabellen din er et sted i Bruk denne universelle formelen på midten av arket, der A1 er den øverste venstre cellen i oppslagstabellen som inneholder en kolonneoverskrift:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
Slik fungerer denne formelen
Her er nøkkeldelen av formelen som skaper et dynamisk vlookup-område:
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
MATCH-funksjonen som er konfigurert for nøyaktig samsvar (0 i det siste argumentet) sammenligner målnavnet (E1) mot listen over navn (A2: A11) og returnerer posisjonen til det først funnet treffet, som er 3 i o saken din. Dette tallet skal brukes som startradkoordinat for vlookup-området, så vi legger til 2 til det (+1 for å ekskludere første forekomst og +1 for å ekskludere rad 1 med kolonneoverskriftene). Alternativt kan du bruke 1 + RAD (A1) for å beregne den nødvendige justeringen automatisk basert på posisjonen til overskriftsraden (A1 i vårt tilfelle).
Som et resultat får vi følgende tekststreng, som INDIRECT konverterer til en referanse:
INDIRECT("A"&5&":B11") -> A5:B11
Dette området går til argumentet table_array for VLOOKUP og tvinger det til å begynne å søke i rad 5, utelater den første forekomsten av oppslagsverdien:
VLOOKUP(E1, A5:B11, 2, FALSE)
Hvordan Vlookup og returnere flere verdier i Excel
Excel VLOOKUP-funksjonen er designet for å returnere bare en kamp. Er det en måte å Vlookup flere forekomster på? Ja, det er det, men ikke enkelt. Dette krever en kombinert bruk av flere funksjoner som INDEX, SMALL og ROW er en matriseformel.
For eksempel kan nedenfor finne alle forekomster av oppslagsverdien F2 i oppslagsområdet B2: B16 og retur flere treff fra kolonne C:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Det er to måter å skrive inn formelen i regnearket:
- Skriv inn formelen i den første cellen, trykk Ctrl + Shift + Enter, og dra den deretter ned til noen få celler.
- Velg flere tilstøtende celler i en enkelt kolonne (F1: F11 i skjermbildet nedenfor), skriv inn formelen og trykk Ctrl + Shift + Enter for å fullføre den.
Uansett må antallet celler du skriver inn formelen være lik eller større enn maksimalt antall mulige treff.
For detaljert forklaring av formellogikken og flere eksempler, se Hvordan SLETTE opp flere verdier i Excel .
Hvordan Vlookup i rader og kolonner (toveisoppslag)
Toveisoppslag (aka matriseoppslag eller 2-dimensjonalt oppslag) er et fancy ord for å slå opp en verdi i skjæringspunktet mellom en bestemt rad og kolonne.Det er noen forskjellige måter å gjøre todimensjonalt oppslag i Excel, men siden fokuset i denne opplæringen er på VLOOKUP-funksjonen, vil vi naturlig bruke den.
For dette eksemplet tar vi nedenfor tabell med månedlig salg og utarbeide en VLOOKUP-formel for å hente salgstallet for en bestemt vare i en gitt måned.
Med varenavn i A2: A9, måneders navn i B1: F1, målvaren i I1 og målmåneden i I2 går formelen slik:
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
Slik fungerer denne formelen
Kjernen i formelen er standard VLOOKUP-funksjon som søker etter en nøyaktig samsvar med oppslagsverdien i I1. Men siden vi ikke vet i hvilken kolonne nøyaktig salget for en bestemt måned er, kan vi ikke levere kolonnenummeret direkte til argumentet col_index_num. For å finne den kolonnen bruker vi følgende MATCH-funksjon:
MATCH(I2, A1:F1, 0)
Oversatt til engelsk sier formelen: se opp I2-verdien i A1: F1 og returnerer sin relative posisjon i matrisen. Ved å levere 0 til det tredje argumentet, instruerer du MATCH om å finne verdien nøyaktig lik oppslagsverdien (det er som å bruke FALSE for argumentet range_lookup til VLOOKUP).
Siden Mar er i den fjerde kolonnen. i oppslagsmatrisen returnerer MATCH-funksjonen 4, som går direkte til col_index_num-argumentet til VLOOKUP:
VLOOKUP(I1, A2:F9, 4, FALSE)
Vær oppmerksom at selv om månedsnavnene starter i kolonne B, bruker vi A1: I1 for oppslagsmatrisen. Dette gjøres for at tallet som returneres av MATCH skal svare til kolonnens posisjon i tabellarray for VLOOKUP.
For å lære flere måter å utføre matrisesøk i Excel, se INDEX MATCH MATCH og andre formler for todimensjonal oppslag.
Hvordan gjøre flere Vlookup i Excel (nestet Vlookup)
Noen ganger kan det hende at hovedtabellen og oppslagstabellen ikke har en eneste kolonne til felles, noe som forhindrer deg i å gjøre en Vlookup mellom to tabeller. Imidlertid eksisterer det en annen tabell som ikke inneholder informasjonen du leter etter, men som har en felles kolonne med hovedtabellen og en annen vanlig kolonne med oppslagstabellen.
Bildet nedenfor illustrerer situasjonen:
Målet er å kopiere priser til hovedtabellen basert på vare-ID-er. Problemet er at tabellen som inneholder priser ikke har vare-ID-ene, noe som betyr at vi må gjøre to Vlookups i en formel.
For enkelhets skyld, la oss opprette et par navngitte områder først :
- Oppslagstabell 1 heter Produkter (D3: E3).
- Oppslagstabell 2 heter Priser (G3: H3).
Tabellene kan være i samme eller forskjellige regneark.
Og nå skal vi utføre den såkalte doble Vlookup, aka nestet Vlookup.
Lag først en VLOOKUP formel for å finne produktnavnet i Oppslagstabell 1 (kalt Produkter) basert på vare-id (A3):
=VLOOKUP(A3, Products, 2, FALSE)
Deretter setter du formelen ovenfor i argumentet lookup_value for en annen VLOOKUP-funksjon for å hente priser fra oppslagstabell 2 (kalt Prices) basert på produktnavnet som returneres av den nestede VLOOKUP:
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
Skjermbildet nedenfor viser vår nestede Vlookup-formel i aksjon:
Slik Vlookup flere ark dynamisk
Noen ganger kan det hende du har data i samme format delt over flere regneark. Og målet ditt er å hente data fra et bestemt ark, avhengig av nøkkelverdien i en gitt celle.
Dette kan være lettere å forstå fra et eksempel. La oss si at du har noen få regionale salgsrapporter i samme format, og du ønsker å få salgstallene for et bestemt produkt i bestemte regioner:
Som i forrige eksempel, begynner vi med å definere noen få navn:
- Område A2: B5 i CA-ark heter CA_Sales.
- Område A2 : B5 i FL-ark heter FL_Sales.
- Område A2: B5 i KS-ark heter KS_Sales.
Som du ser, har alle de nevnte områdene et felles del (salg) og unike deler (CA, FL, KS). Husk å navngi områdene dine på samme måte som det er viktig for formelen vi skal lage.
Formel 1. INDIRECT VLOOKUP for dynamisk å hente data fra forskjellige ark
Hvis oppgaven din er å hente data fra flere ark, er en VLOOKUP INDIRECT formel den beste løsningen – kompakt og lett å forstå.
For dette eksemplet organiserer vi oppsummeringstabellen på denne måten:
- Skriv inn produktene av interesse i A2 og A3. Dette er oppslagsverdiene våre.
- Angi de unike delene av de nevnte områdene i B1, C1 og D1.
Og nå sammenføyer vi cellen som inneholder den unike delen (B1) med den felles delen («_Sales»), og mater den resulterende strengen til INDIRECT:
INDIRECT(B$1&"_Sales")
INDIRECT-funksjonen forvandler strengen til et navn som Excel kan forstå, og du legger den i argumentet table_array til VLOOKUP:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
Formelen ovenfor går til B2, og deretter kopierer du den ned og til høyre.
Vær oppmerksom på at, i oppslagsverdien ($ A2) har vi låst kolonnekoordinaten med absolutt cellereferanse slik at kolonnen forblir fast når formelen kopieres til høyre. I B $ 1-referansen låste vi raden fordi vi vil ha kolonnen koordinere for å endre og levere en passende navnedel til INDIRECT, avhengig av kolonnen formelen er kopiert til:
Hvis hovedtabellen er organisert forskjellig, oppslagsverdiene på rad og unike deler av rekkeviddenavnene i en kolonne mn, så bør du låse radkoordinaten i oppslagsverdien (B $ 1) og kolonnekoordinaten i navnedelene ($ A2):
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
Formel 2. VISNING og nestede IF-er for å slå opp flere ark
I en situasjon når du nettopp har to eller tre oppslagsark, kan du bruke en ganske enkel VLOOKUP-formel med nestede IF-funksjoner for å velge riktig ark basert på nøkkelverdien i en bestemt celle:
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
Hvor $ A2 er oppslagsverdien (varenavn) og B $ 1 er nøkkelverdien (tilstand):
I dette tilfellet trenger du ikke nødvendigvis å definere navn, og du kan bruke eksterne referanser til å referere til et annet ark eller arbeidsbok.
For flere formeleksempler, se Slik VISER DU OPP på tvers av flere ark i Excel .
Slik bruker du VLOOKUP i Excel. Jeg takker for at du leser og håper å se deg på bloggen vår neste uke!
Øv arbeidsbok for nedlasting
Avanserte eksempler på VLOOKUP-formel (.xlsx-fil)
- Excel VLOOKUP-opplæring for nybegynnere
- Hvordan Vlookup flere treff i Excel
- Hvordan bruke VLOOKUP & SUM- eller SUMIF-funksjoner i Excel
- INDEX & MATCH-funksjoner i Excel – et bedre alternativ til VLOOKUP
- 4 måter å gjøre en saksfølsom Vlookup i Excel
- Excel VLOOKUP fungerer ikke – N / A, NAME og VALUE feil