O Excel VLOOKUP funkcija je zmogljivo orodje, ki vam pomaga poiskati določeno vrednost z ujemanjem v prvem stolpcu tabele ali obsega navpično in nato vrne ustrezno vrednost iz drugega stolpca v isti vrstici. Čeprav je funkcija VLOOKUP izjemno uporabna, je lahko začetnikom včasih težko razumeti. Ta vadnica vam pomaga obvladati VLOOKUP z zagotavljanjem razlaga argumentov po korakih, uporabni primeri in rešitve pogostih napak na katere lahko naletite pri uporabi funkcije VLOOKUP.

• Postopna razlaga argumentov
• Sintaksa in argumenti
• VLOOKUP Primeri
  Natančno ujemanje proti približnemu ujemanju   |   Vlookup z več pogoji
• VLOOKUP Pogoste napake in rešitve
  Napaka #N/A   |   Napaka #VALUE   |   Napaka #REF   |   Nepravilna vrednost

VLOOKUP primeri .xlsx Napake VLOOKUP .xlsx

Related Videos

Postopna razlaga argumentov

Kot je prikazano na zgornjem posnetku zaslona, ​​se funkcija VLOOKUP uporablja za iskanje e-pošte na podlagi dane ID številke. Zdaj bom podrobno razložil, kako uporabljati VLOOKUP v tem primeru, tako da bom vsak argument razčlenil korak za korakom.

1. korak: Zaženite funkcijo VLOOKUP

Izberite celico (v tem primeru H6), da izpišete rezultat, nato zaženite funkcijo VLOOKUP tako, da v polje vnesete naslednjo vsebino. Formula Bar.

=VLOOKUP(

2. korak: Določite iskalno vrednost

Najprej določite iskalno vrednost (kar iščete) v funkciji VLOOKUP. Tukaj se sklicujem na celico G6, ki vsebuje določeno ID številko 1005.

=VLOOKUP(G6

3. korak: Določite polje tabele

Nato določite obseg celic, ki vsebuje vrednost, ki jo iščete, in vrednost, ki jo želite vrniti. V tem primeru izberem obseg B6:E12. Formula je zdaj videti takole:

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

4. korak: Določite stolpec, iz katerega želite vrniti vrednost

Nato določite stolpec, iz katerega želite vrniti vrednost.

V tem primeru, ker moram vrniti e-pošto na podlagi številke ID, tukaj vnesem številko 4, da VLOOKUP-u sporočim, naj vrne vrednost iz četrtega stolpca obsega podatkov.

=VLOOKUP(G6,B6:E12,4

5. korak: Poiščite približno ali natančno ujemanje

Na koncu ugotovite, ali iščete približno ujemanje ali natančno ujemanje.

• Če želite najti natančno ujemanje, morate uporabiti FALSE kot zadnji argument.
• Če želite najti približno ujemanje, Uporaba TRUE kot zadnji argument ali pa pustite prazno.

V tem primeru uporabljam FALSE za natančno ujemanje. Formula zdaj izgleda takole:

=VLOOKUP(G6,B6:E12,4,FALSE

Pritisnite tipko Enter, da dobite rezultat

Z razlago vsakega argumenta enega za drugim v zgornjem primeru so sintaksa in argumenti funkcije VLOOKUP zdaj veliko lažje razumljivi.

Sintaksa in argumenti

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

• Iskalna_vrednost (obvezno): vrednost (resnična vrednost ali sklic na celico), ki jo iščete. Ne pozabite, da mora biti ta vrednost v prvem stolpcu table_array.
• Tabela_ matrika (obvezno): obseg celic vsebuje stolpec iskalne vrednosti in stolpec vrnjene vrednosti.
• Col_index (obvezno): Celo število predstavlja številko stolpca, ki vsebuje vrnjeno vrednost. Začne se s številko 1 za skrajno levi stolpec table_array.
• Območje_iskanje (neobvezno): logična vrednost, ki določa, ali želite, da VLOOKUP najde približno ali natančno ujemanje.
  • Približno ujemanje - Ta argument nastavite na TRUE, 1 ali pusti prazno.
    Pomembno: Če želite najti približno ujemanje, morajo biti vrednosti v prvem stolpcu table_array razvrščene v naraščajočem vrstnem redu, če VLOOKUP vrne napačen rezultat.
  • Natančna tekma - Ta argument nastavite na FALSE or 0.

Primeri

V tem razdelku je prikazanih nekaj primerov, ki vam bodo pomagali bolje razumeti funkcijo VLOOKUP.

Primer 1: Natančno ujemanje proti približnemu ujemanju v VLOOKUP

Če ste zmedeni glede natančnega in približnega ujemanja pri uporabi funkcije VLOOKUP, vam lahko ta razdelek pomaga odpraviti to zmedo.

Natančno ujemanje v VLOOKUP

V tem primeru bom poiskal ustrezna imena na podlagi rezultatov, navedenih v obsegu E6:E8, zato bom v celico F6 vnesel naslednjo formulo in ročico za samodejno izpolnjevanje povlekel navzdol do F8. V tej formuli je zadnji argument določen kot FALSE za iskanje natančnega ujemanja.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

Ker pa ocena 98 ne obstaja v prvem stolpcu obsega podatkov, VLOOKUP vrne rezultat napake #N/A.

Približno ujemanje v VLOOKUP

Še vedno uporabljate zgornji primer, če spremenite zadnji argument v TRUE, bo VLOOKUP izvedel iskanje približnega ujemanja. Če ne najde nobenega ujemanja, bo poiskal naslednjo največjo vrednost, ki je manjša od iskalne vrednosti, in vrnil ustrezen rezultat.

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

Ker rezultat 98 ne obstaja, VLOOKUP poišče naslednjo največjo vrednost, ki je manjša od 98, kar je 95, in vrne ime rezultata 95 kot najbližji rezultat.

2. primer: uporabite VLOOKUP z več kriteriji

Ta razdelek prikazuje, kako uporabljati VLOOKUP z več pogoji v Excelu. Kot je prikazano na spodnjem posnetku zaslona, ​​če poskušate poiskati plačo na podlagi navedenega imena (v celici H5) in oddelka (v celici H6), sledite spodnjim korakom, da to storite.

1. korak: Dodajte pomožni stolpec za združevanje vrednosti iz iskalnih stolpcev

V tem primeru moramo ustvariti pomožni stolpec za združevanje vrednosti iz Ime stolpec in Oddelek stolpec.

1. Dodajte pomožni stolpec na levo od obsega podatkov in temu stolpcu dodajte glavo. Oglejte si posnetek zaslona:
   
   
2. V tem pomožnem stolpcu izberite prvo celico pod glavo, v polje vnesite naslednjo formulo Vrstica formulein pritisnite Vnesite.
   

   =C6&" "&D6

   
   Opombe: V tej formuli uporabljamo ampersand (&), da združimo besedilo v dveh stolpcih, da ustvarimo en sam del besedila.

   • C6 je prvo ime Ime stolpec za pridružitev, D6 je prvi oddelek v Oddelek stolpcu, da se pridružite.
   • Vrednosti teh dveh celic sta povezani s presledkom vmes.
3. Izberite to celico z rezultati in nato povlecite Ročaj za samodejno izpolnjevanje navzdol, da uporabite to formulo za druge celice v istem stolpcu.
   
   

2. korak: Uporabite funkcijo VLOOKUP z danimi kriteriji

Izberite celico, v katero želite izpisati rezultat (tu izberem I7), v polje vnesite naslednjo formulo Vrstica formule, nato pritisnite Vnesite.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

Rezultat

Pogoste napake VLOOKUP in rešitve

V tem razdelku so navedene pogoste napake, na katere lahko naletite pri uporabi funkcije VLOOKUP, in rešitve za njihovo odpravo.

Vrnjena napaka #N/A

Najpogostejša napaka pri VLOOKUP je napaka #N/A, kar pomeni, da Excel ni mogel najti vrednosti, ki ste jo iskali. Tukaj je nekaj razlogov, zakaj lahko VLOOKUP vrne napako #N/A.

1. razlog: iskalna vrednost ni v prvem stolpcu table_array

Ena od omejitev Excelovega VLOOKUP-a je, da omogoča samo pogled od leve proti desni. Torej morajo biti iskalne vrednosti v prvem stolpcu table_array.

Kot je prikazano na spodnjem posnetku zaslona, ​​želim vrniti ime glede na dani naziv delovnega mesta. Tukaj je iskalna vrednost (vodja prodaje) je v drugem stolpcu table_array in vrnjena vrednost je levo od stolpca za iskanje, zato VLOOKUP vrne napako #N/A.

rešitve

Za odpravo te napake lahko uporabite katero koli od naslednjih rešitev.

• Preuredite stolpce
  
  Stolpce lahko preuredite tako, da stolpec za iskanje postavite v prvi stolpec table_array.
• Uporabite funkciji INDEX in MATCH skupaj
  
  Tukaj skupaj uporabljamo funkciji INDEX in MATCH kot alternativo VLOOKUP za rešitev te težave.

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• Uporabite funkcijo XLOOKUP (na voljo v Excelu 365, Excelu 2021 in novejših različicah)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

2. razlog: iskalne vrednosti ni mogoče najti v iskalnem stolpcu (natančno ujemanje)

Eden od najpogostejših razlogov, zakaj VLOOKUP vrne napako #N/A, je, ker vrednost, ki jo iščete, ni najdena.

Kot je prikazano v spodnjem primeru, bomo poiskali ime na podlagi podane ocene 98 v E6. Vendar ta rezultat ne obstaja v prvem stolpcu obsega podatkov, zato VLOOKUP vrne rezultat napake #N/A.

rešitve

Če želite odpraviti to napako, lahko poskusite z eno od naslednjih rešitev.

• Če želite, da VLOOKUP išče naslednjo največjo vrednost, ki je manjša od iskalne vrednosti, spremenite zadnji argument FALSE (natančno ujemanje) z TRUE (približno ujemanje). Za več informacij glejte Primer 1: Natančno ujemanje proti približnemu ujemanju z uporabo funkcije VLOOKUP.
• Če se želite izogniti spreminjanju zadnjega argumenta in prejeti opomnik, če iskalna vrednost ni najdena, lahko funkcijo VLOOKUP vključite v funkcijo IFERROR:
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

3. razlog: iskalna vrednost je manjša od najmanjše vrednosti v iskalnem stolpcu (približno ujemanje)

Kot je prikazano na spodnjem posnetku zaslona, ​​izvajate iskanje približnega ujemanja. Vrednost, ki jo iščete (ID številka 1001 v tem primeru) je manjša od najmanjše vrednosti 1002 v stolpcu za iskanje, zato VLOOKUP vrne napako #N/A.

rešitve

Tukaj sta dve rešitvi za vas.

• Prepričajte se, da je iskalna vrednost večja ali enaka najmanjši vrednosti v iskalnem stolpcu.
• Če želite, da vas Excel opomni, da iskalna vrednost ni bila najdena, samo ugnezdite funkcijo VLOOKUP v funkcijo IFERROR, kot sledi:
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

4. razlog: Številke so oblikovane kot besedilo

Kot lahko vidite na spodnjem posnetku zaslona, ​​je rezultat napake #N/A v tem primeru posledica neujemanja vrste podatkov med celico za iskanje (G6) in stolpcem za iskanje (B6:B12) izvirne tabele. Tu je vrednost v G6 številka, vrednosti v obsegu B6:B12 pa so številke, oblikovane kot besedilo.

rešitve

Če želite rešiti to težavo, morate iskalno vrednost pretvoriti nazaj v število. Tukaj sta dve metodi za vas.

• Uporabite funkcijo Pretvori v številko
  
  Kliknite na celico, v kateri želite besedilo pretvoriti v številko, izberite ta gumb  poleg celice in nato izberite Pretvori v številko.
  
• Uporabite priročno orodje za paketno pretvorbo med besedilom in številko
  
  O Pretvori med besedilo in številko značilnost Kutools for Excel vam pomaga enostavno pretvoriti vrsto celic iz besedila v številko in obratno. Zagotovite si 30-dnevno brezplačno preskusno različico vseh funkcij!
  

5. razlog: table_array ni konstantna, ko povlečete formulo VLOOKUP v druge celice

Kot je prikazano na spodnjem posnetku zaslona, ​​sta v E6 in E7 dve iskalni vrednosti. Ko dobite prvi rezultat v F6, povlecite formulo VLOOKUP iz celice F6 v F7, vrnjen je rezultat napake #N/A. To je zato, ker so sklice na celice (B6:C12) privzeto relativne in se prilagajajo, ko se premikate navzdol po vrsticah. Matrika tabel je bila premaknjena navzdol na B7:C13, ki ne vsebuje več rezultata iskanja 73.

Rešitev

Matriko tabel morate zakleniti, da ostane konstantna, tako da dodate a $ znak pred vrsticami in stolpci v referencah celic. Če želite izvedeti več o absolutni referenci v Excelu, si oglejte to vadnico: Excel absolutna referenca (kako narediti in uporabljati).

Vrnjena napaka #VALUE

Naslednji pogoji lahko povzročijo, da VLOOKUP vrne rezultat napake #VALUE.

1. razlog: iskalna vrednost presega 255 znakov

Kot je prikazano na spodnjem posnetku zaslona, ​​iskalna vrednost v celici H4 presega 255 znakov, zato VLOOKUP vrne rezultat napake #VALUE.

rešitve

Če se želite izogniti tej omejitvi, lahko uporabite drugo funkcijo iskanja, ki lahko obravnava daljše nize. Poskusite eno od naslednjih formul.

• INDEX in MATCH:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• Funkcija XLOOKUP (na voljo v Excelu 365, Excelu 2021 in novejših različicah):
  

  =XLOOKUP(H4,B5:B11,E5:E11)

2. razlog: argument col_index je manjši od 1

Indeks stolpca določa številko stolpca v matriki tabele, ki vsebuje vrednost, ki jo želite vrniti. Ta argument mora biti pozitivno število, ki ustreza veljavnemu stolpcu v matriki tabele.

Če vnesete indeks stolpca, ki je manjši od 1 (tj. nič ali negativen), VLOOKUP ne bo mogel poiskati stolpca v matriki tabele.

Rešitev

Če želite odpraviti to težavo, se prepričajte, da je argument indeksa stolpca v formuli VLOOKUP pozitivno število, ki ustreza veljavnemu stolpcu v matriki tabele.

Vrnjena napaka #REF

Ta razdelek navaja enega od razlogov, zakaj VLOOKUP vrne napako #REF, in nudi rešitve za to težavo.

Razlog: argument col_index je večji od števila stolpcev

Kot lahko vidite na spodnjem posnetku zaslona, ​​ima matrika tabele le 4 stolpce. Vendar pa je indeks stolpca, ki ste ga določili v formuli VLOOKUP, 5, kar je večje od števila stolpcev v matriki tabele. Posledično VLOOKUP ne bo mogel poiskati stolpca in bo na koncu vrnil napako #REF.

rešitve

• Določite pravilno številko stolpca
  Prepričajte se, da je argument indeksa stolpca v formuli VLOOKUP številka, ki ustreza veljavnemu stolpcu v matriki tabele.
• Samodejno pridobite številko stolpca na podlagi določene glave stolpca
  Če tabela vsebuje veliko stolpcev, boste morda imeli težave pri določanju pravilne številke indeksa stolpca. Tukaj lahko ugnezdite funkcijo MATCH v funkcijo VLOOKUP, da poiščete položaj stolpca na podlagi določene glave stolpca.

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  Opombe: V zgornji formuli je MATCH("E-pošta",B5:E5, 0) funkcija se uporablja za pridobitev številke stolpca "E-pošta" v časovnem obsegu B6:E12. Tukaj je rezultat 4, ki se uporablja kot col_index v funkciji VLOOKUP.

Vrnjena je napačna vrednost

Če ugotovite, da VLOOKUP ne vrne pravilnega rezultata, je to lahko posledica naslednjih razlogov

1. razlog: stolpec za iskanje ni razvrščen v naraščajočem vrstnem redu

Če ste zadnji argument nastavili na TRUE (ali pustil prazno) za približno ujemanje in stolpec za iskanje ni razvrščen v naraščajočem vrstnem redu, je lahko dobljena vrednost napačna.

Rešitev

Razvrščanje stolpca za iskanje v naraščajočem vrstnem redu vam lahko pomaga rešiti to težavo. Če želite to narediti, sledite spodnjim korakom:

1. Izberite podatkovne celice v stolpcu za iskanje, pojdite na datum jeziček, kliknite Razvrsti od Najmanjše do Največje v Razvrsti in filtriraj skupina.
2. v Razvrsti opozorilo v pogovornem oknu izberite Razširite izbor in kliknite OK.

2. razlog: stolpec je vstavljen ali odstranjen

Kot je prikazano na spodnjem posnetku zaslona, ​​je vrednost, ki sem jo prvotno želel vrniti, v četrtem stolpcu matrike tabele, zato podam številko col_index kot 4. Ko je vstavljen nov stolpec, stolpec z rezultati postane peti stolpec tabele matriko, zaradi česar VLOOKUP vrne rezultat iz napačnega stolpca.

rešitve

Tukaj sta dve rešitvi za vas.

• Številko indeksa stolpca lahko ročno spremenite tako, da se ujema s položajem povratnega stolpca. Tukaj je treba formulo spremeniti v:

  =VLOOKUP(H6,B6:F12,5,FALSE)

• Če želite vedno vrniti rezultat iz določenega stolpca, kot je stolpec E-pošta v tem primeru. Naslednja formula lahko pomaga samodejno ujemati indeks stolpca na podlagi podane glave stolpca, ne glede na to, ali so stolpci vstavljeni ali odstranjeni iz matrike tabele.

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

Druge opombe o funkcijah

• VLOOKUP išče samo vrednost od leve proti desni.
  Iskalna vrednost je v skrajnem levem stolpcu, vrednost rezultata pa mora biti v katerem koli stolpcu desno od iskanega stolpca.
• Če pustite zadnji argument prazen, VLOOKUP privzeto uporabi približno ujemanje.
• VLOOKUP izvede iskanje, ki ne razlikuje med velikimi in malimi črkami.
• Za več ujemanj VLOOKUP vrne samo prvo ujemanje, ki ga najde v matriki tabele, glede na vrstni red vrstic v matriki tabele.

Povezani članki