Preskoči na glavno vsebino

20+ primerov VLOOKUP za začetnike in napredne uporabnike programa Excel

Funkcija VLOOKUP je ena najbolj priljubljenih funkcij v Excelu. Ta vadnica bo predstavila, kako uporabljati funkcijo VLOOKUP v Excelu z desetinami osnovnih in naprednih primerov korak za korakom.


Prenesite vzorčne datoteke VLOOKUP

 Osnovni primeri Vlookup   |    Primeri naprednega Vlookupa   |    Vlookup ohranja oblikovanje celic


Predstavitev funkcije VLOOKUP - Sintaksa in argumenti

V Excelu je funkcija VLOOKUP zmogljiva funkcija za večino uporabnikov Excela, saj vam omogoča, da poiščete vrednost na skrajni levi strani obsega podatkov in vrnete ujemajočo se vrednost v isti vrstici iz stolpca, ki ste ga določili kot prikazano na naslednji sliki zaslona. .

Sintaksa funkcije VLOOKUP:

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

Argumenti:

Iskalna_vrednost (obvezno): vrednost, ki jo želite iskati. Lahko je vrednost (število, datum ali besedilo) ali sklic na celico. Biti mora v prvem stolpcu obsega table_array. 

Tabela_ matrika (obvezno): Obseg podatkov ali tabela, kjer se nahajata stolpec vrednosti iskanja in stolpec vrednosti rezultata.

Col_index_num (obvezno): številka stolpca, ki vsebuje vrnjene vrednosti. Začne se z 1 v skrajnem levem stolpcu v matriki tabele.

Območje_iskanje (neobvezno): logična vrednost, ki določa, ali bo ta funkcija VLOOKUP vrnila natančno ali približno ujemanje.

  • Približno ujemanje – 1 / TRUE / izpuščeno (privzeto): Če natančnega ujemanja ni mogoče najti, formula išče najbližje ujemanje – največjo vrednost, ki je manjša od iskalne vrednosti.
    Opaziti: V tem primeru morate stolpec za iskanje (skrajno levi stolpec obsega podatkov) razvrstiti v naraščajočem vrstnem redu, sicer bo vrnil napačen rezultat ali napako #N/V.
  • Natančno ujemanje – 0 / FALSE: Uporablja se za iskanje vrednosti, ki je popolnoma enaka iskalni vrednosti. Če natančnega ujemanja ne najdemo, bo vrnjena vrednost napake # N / A.

Opombe k funkciji:

  • Funkcija Vlookup išče samo vrednost od leve proti desni.
  • Funkcija Vlookup izvede iskanje, ki ne razlikuje med velikimi in malimi črkami.
  • Če obstaja več ujemajočih se vrednosti na podlagi iskalne vrednosti, bo s funkcijo Vlookup vrnjena samo prva ujemajoča se vrednost.

Osnovni primeri VLOOKUP

V tem razdelku bomo govorili o nekaterih formulah Vlookup, ki ste jih pogosto uporabljali.

2.1 Natančno in približno ujemanje VLOOKUP

 2.1.1 Izvedite natančno ujemanje VLOOKUP

Običajno morate, če iščete natančno ujemanje s funkcijo VLOOKUP, kot zadnji argument uporabiti FALSE.

Če želite na primer dobiti ustrezne matematične ocene na podlagi določenih številk ID, naredite naslednje:

Kopirajte in prilepite spodnjo formulo v prazno celico (tukaj izberem G2) in pritisnite Vnesite ključ do rezultata:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

Opomba: v zgornji formuli so štirje argumenti:

  • F2 je celica, ki vsebuje vrednost C1005, ki jo želite poiskati;
  • A2: D7 je polje tabele, v katerem izvajate iskanje;
  • 3 je številka stolpca, iz katerega je vrnjena vaša ujemajoča se vrednost; (Ko funkcija zazna ID - C1005, bo šla v tretji stolpec matrike tabele in vrnila vrednosti v isti vrstici, kot je ID - C1005. )
  • FALSE se nanaša na natančno ujemanje.

Kako deluje formula VLOOKUP?

Najprej poišče ID - C1005 v skrajno levem stolpcu tabele. Gre od zgoraj navzdol in najde vrednost v celici A6.

Takoj, ko najde vrednost, gre v tretji stolpec desno in izvleče vrednost v njem.

Tako boste dobili rezultat, kot je prikazan na spodnjem posnetku zaslona:

Opomba: Če iskalne vrednosti ni mogoče najti v skrajno levem stolpcu, vrne napako #N/A.
🤖 Kutools AI Aide: Revolucionirajte analizo podatkov na podlagi: Inteligentna izvedba   |  Ustvari kodo  |  Ustvarite formule po meri  |  Analizirajte podatke in ustvarite grafikone  |  Prikličite funkcije Kutools...
Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup  |   Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ...
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ...
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce   |  Razkrij stolpce  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule   |  Upravitelj delovnih zvezkov in listov  |  Knjižnica virov   |  Izbirnik datuma  |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (krepko/ležeče...) ...
Najboljših 15 orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   Veliko več...

Kutools za Excel se ponaša z več kot 300 funkcijami, Zagotavljanje, da je vse, kar potrebujete, le en klik stran ...

 
 2.1.2 Izvedite približno ujemanje VLOOKUP

Približno ujemanje je uporabno za iskanje vrednosti med obsegi podatkov. Če natančnega ujemanja ni mogoče najti, bo približen VLOOKUP vrnil največjo vrednost, ki je manjša od iskalne vrednosti.

Na primer, če imate naslednji obseg podatkov in navedena naročila niso v stolpcu Naročila, kako pridobiti njegov najbližji popust v stolpcu B?

1. korak: Uporabite formulo VLOOKUP in jo napolnite z drugimi celicami

Kopirajte in prilepite naslednjo formulo v celico, kamor želite dati rezultat, nato pa povlecite ročico za polnjenje navzdol, da uporabite to formulo v drugih celicah.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Rezultat:

Zdaj boste dobili približna ujemanja na podlagi danih vrednosti, glejte posnetek zaslona:

Opombe:

  • V zgornji formuli:
    • D2 je vrednost, za katero želite vrniti njene relativne informacije;
    • A2: B9 je obseg podatkov;
    • 2 označuje številko stolpca, v kateri je vrnjena vaša ujemajoča se vrednost;
    • TRUE se nanaša na približno ujemanje.
  • Približno ujemanje bo vrnilo največjo vrednost, ki je manjša od vaše specifične iskalne vrednosti, če ni najdenega natančnega ujemanja.
  • Če želite uporabiti funkcijo VLOOKUP za pridobitev približne vrednosti ujemanja, morate skrajno levi stolpec obsega podatkov razvrstiti v naraščajočem vrstnem redu, sicer bo vrnil napačen rezultat.

2.2 Izvedite VLOOKUP z upoštevanjem velikih in malih črk v Excelu

Funkcija VLOOKUP privzeto izvede iskanje, ki ne razlikuje med velikimi in malimi črkami, kar pomeni, da male in velike črke obravnava kot enake. Včasih boste morda morali izvesti iskanje v Excelu, ki razlikuje med velikimi in malimi črkami; običajna funkcija VLOOKUP tega morda ne bo rešila. V tem primeru lahko uporabite alternativne funkcije, kot sta INDEX in MATCH s funkcijo EXACT ali funkcijama LOOKUP in EXACT.

Na primer, imam naslednje obseg podatkov, kateri stolpec ID vsebuje besedilni niz z velikimi ali malimi črkami, zdaj želim vrniti ustrezen matematični rezultat dane številke ID.

1. korak: Uporabite eno formulo in jo napolnite z drugimi celicami

Kopirajte in prilepite katero koli od spodnjih formul v prazno celico, kjer želite dobiti rezultat. Nato izberite celico s formulo, povlecite ročico za polnjenje navzdol do celic, v katere želite zapolniti to formulo.

Formula 1: Ko prilepite formulo, pritisnite Ctrl + Shift + Enter ključi.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Formula 2: Ko prilepite formulo, pritisnite Vnesite ključ.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Rezultat:

Potem boste dobili prave rezultate, ki jih potrebujete. Oglejte si posnetek zaslona:

Opombe:

  • V zgornji formuli:
    • A2: A10 je stolpec, ki vsebuje določene vrednosti, v katerih želite iskati;
    • F2 je iskalna vrednost;
    • C2: C10 je stolpec, iz katerega bo vrnjen rezultat.
  • Če je najdenih več ujemanj, bo ta formula vedno vrnila zadnje ujemanje.

2.3 Vrednosti VLOOKUP od desne proti levi v Excelu

Funkcija VLOOKUP vedno išče vrednost v skrajno levem stolpcu obsega podatkov in vrne ustrezno vrednost iz stolpca na desni strani. Če želite izvesti obratni VLOOKUP, kar pomeni iskanje določene vrednosti v desnem stolpcu in vrnitev njene ustrezne vrednosti v levi stolpec, kot je prikazano na spodnjem posnetku zaslona:

Kliknite podrobnosti o tej nalogi po korakih ...


2.4 VLOOKUP druga, n-ta ali zadnja ujemajoča se vrednost v Excelu

Če je pri uporabi funkcije Vlookup najdenih več ujemajočih se vrednosti, bo običajno vrnjen samo prvi ujemajoči se zapis. V tem razdelku bom govoril o tem, kako pridobiti drugo, n-to ali zadnjo ujemajočo se vrednost v obsegu podatkov.

 2.4.1 VLOOKUP in vrne drugo ali n-to ujemajočo se vrednost

Recimo, da imate seznam imen v stolpcu A, tečaj usposabljanja, ki so ga kupili, v stolpcu B. Zdaj iščete 2. ali n-ti tečaj usposabljanja, ki ga je kupila dana stranka. Oglejte si posnetek zaslona:

Tukaj funkcija VLOOKUP morda ne bo neposredno rešila te naloge. Lahko pa uporabite funkcijo INDEX kot alternativo.

1. korak: uporabite in izpolnite formulo v druge celice

Če želite na primer pridobiti drugo ujemajoča se vrednost na podlagi danih kriterijev, uporabite naslednjo formulo v prazno celico in pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvi rezultat. Nato izberite celico s formulo, povlecite ročico za polnjenje navzdol do celic, v katere želite zapolniti to formulo.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Rezultat:

Zdaj so vse druge ujemajoče se vrednosti na podlagi danih imen prikazane hkrati.

Opomba: V zgornji formuli:

  • A2: A14 je obseg z vsemi vrednostmi za iskanje;
  • B2: B14 je obseg ujemajočih se vrednosti, iz katerih se želite vrniti;
  • E2 je iskalna vrednost;
  • 2 označuje drugo ujemajočo se vrednost, ki jo želite dobiti, če želite vrniti tretjo ujemajočo se vrednost, jo morate samo spremeniti v 3.
 2.4.2 VLOOKUP in vrni zadnjo ujemajočo se vrednost

Če želite vlookup in vrniti zadnjo ujemajočo se vrednost, kot je prikazano na spodnji sliki zaslona, ​​to VLOOKUP in vrni zadnjo ujemajočo se vrednost vadnica vam lahko pomaga podrobneje pridobiti zadnjo ujemajočo se vrednost.


2.5 VLOOKUP ujemanje vrednosti med dvema podanima vrednostma ali datumoma

Včasih boste morda želeli poiskati vrednosti med dvema vrednostma ali datumoma in vrniti ustrezne rezultate, kot je prikazano na spodnjem posnetku zaslona. V tem primeru lahko uporabite funkcijo LOOKUP namesto funkcije VLOOKUP z razvrščeno tabelo.

 2.5.1 VLOOKUP ujemanje vrednosti med dvema podanima vrednostma ali datumoma s formulo

1. korak: Uredite podatke in uporabite naslednjo formulo

Vaša prvotna tabela mora biti razvrščen obseg podatkov. Nato kopirajte ali vnesite naslednjo formulo v prazno celico. Nato povlecite ročico za polnjenje, da zapolnite to formulo v druge celice, ki jih potrebujete.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Rezultat:

In zdaj boste dobili vse ujemajoče se zapise na podlagi dane vrednosti, glejte posnetek zaslona:

Opombe:

  • V zgornji formuli:
    • A2: A6 je obseg manjših vrednosti;
    • B2: B6 je obseg večjih števil;
    • E2 je iskalna vrednost, za katero želite pridobiti ustrezno vrednost;
    • C2: C6 je stolpec, iz katerega želite vrniti ustrezno vrednost.
  • To formulo lahko uporabite tudi za ekstrahiranje ujemajočih se vrednosti med dvema datumoma, kot je prikazano na spodnjem posnetku zaslona:
 2.5.2 VLOOKUP ujemanje vrednosti med dvema podanima vrednostma ali datumoma s priročno funkcijo

Če si težko zapomnite in razumete zgornjo formulo, bom tukaj predstavil enostavno orodje – Kutools za Excel, Z njegovim POGLED med dvema vrednostma funkcijo, lahko z lahkoto vrnete ustrezen element na podlagi določene vrednosti ali datuma med dvema vrednostma ali datumoma.

  1. klik Kutools > Super POGLED > POGLED med dvema vrednostma da omogočite to funkcijo.
  2. Nato določite operacije v pogovornem oknu na podlagi vaših podatkov.
Opombe: Če želite uporabiti to funkcijo, morate prenesti Kutools za Excel s 30-dnevno brezplačno preskusno različico najprej.


2.6 Uporaba nadomestnih znakov za delna ujemanja v funkciji VLOOKUP

V Excelu lahko nadomestne znake uporabite v funkciji VLOOKUP, ki vam omogoča delno ujemanje iskalne vrednosti. VLOOKUP lahko na primer uporabite za vrnitev ujemajoče se vrednosti iz tabele na podlagi dela iskalne vrednosti.

Recimo, da imam vrsto podatkov, kot je prikazano na spodnji sliki zaslona, ​​zdaj želim izvleči rezultat na podlagi imena (ne polnega imena). Kako bi lahko to nalogo rešil v Excelu?

1. korak: uporabite in izpolnite formulo v druge celice

Kopirajte ali vnesite naslednjo formulo v prazno celico in nato povlecite ročico za polnjenje, da to formulo zapolnite v druge celice, ki jih potrebujete:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Rezultat:

In vsi ujemajoči se rezultati so bili vrnjeni, kot je prikazano na spodnjem posnetku zaslona:

Opomba: V zgornji formuli:

  • E2 & "*" je merilo za delno matematiko. To pomeni, da iščete katero koli vrednost, ki se začne z vrednostjo v celici E2. (Nadomestni znak "*” označuje kateri koli znak ali katere koli znake)
  • A2: C11 je obseg podatkov, kjer želite iskati ujemajočo se vrednost;
  • 3 pomeni vrnitev ujemajoče se vrednosti iz 3. stolpca obsega podatkov;
  • False označuje natančno matematiko. (Ko uporabljate nadomestne znake, morate zadnji argument v funkciji nastaviti na FALSE ali 0, da omogočite način natančnega ujemanja v funkciji VLOOKUP.)
nasveti:
  • Če želite najti in vrniti ujemajoče se vrednosti, ki se končajo z določeno vrednostjo, morate pred vrednostjo postaviti nadomestni znak "*". Uporabite to formulo:
  • =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

  • Če želite poiskati in vrniti ujemajočo se vrednost na podlagi dela besedilnega niza, ne glede na to, ali je podano besedilo na začetku, na koncu ali na sredini besedilnega niza, morate samo sklic na celico ali besedilo obdati z dvema zvezdicama (*) na obeh straneh. Prosim, storite s to formulo
  • =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


2.7 Vrednosti VLOOKUP iz drugega delovnega lista

Običajno boste morda morali delati z več kot enim delovnim listom. Funkcijo VLOOKUP lahko uporabite za iskanje podatkov z drugega lista kot enake kot na enem delovnem listu.

Tako imate na primer dva delovna lista, kot je prikazano na spodnji sliki zaslona. Če želite poiskati in vrniti ustrezne podatke iz navedenega delovnega lista, naredite naslednje:

1. korak: uporabite in izpolnite formulo v druge celice

Vnesite ali kopirajte spodnjo formulo v prazno celico, kamor želite dobiti ujemajoče se elemente. Nato ročico za polnjenje povlecite navzdol do celic, za katere želite uporabiti to formulo.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Rezultat:

Dobili boste ustrezne rezultate, kot jih potrebujete, glejte posnetek zaslona:

Opomba: V zgornji formuli:

  • A2 predstavlja vrednost iskanja;
  • 'Podatkovni list'!A2:C15 označuje iskanje vrednosti iz obsega A2:C15 na delovnem listu z imenom Podatkovni list; (Če ime lista vsebuje presledke ali ločila, morate ime lista dati v enojne narekovaje, sicer lahko neposredno uporabite ime lista, npr. =VLOOKUP(A2,Podatkovni list!$A$2:$C$15,3,0) ).
  • 3 je številka stolpca, ki vsebuje ujemajoče se podatke, iz katerih se želite vrniti;
  • 0 pomeni izvesti natančno ujemanje.

2.8 Vrednosti VLOOKUP iz drugega delovnega zvezka

Ta razdelek govori o iskanju in vrnitvi ujemajočih se vrednosti iz drugega delovnega zvezka s funkcijo VLOOKUP.

Recimo, da imate dva delovna zvezka. Prvi delovni zvezek vsebuje seznam izdelkov in njihove stroške. V drugem delovnem zvezku želite izvleči ustrezne stroške za vsako postavko izdelka, kot je prikazano na spodnjem posnetku zaslona.

1. korak: Uporabite in izpolnite formulo

Odprite oba delovna zvezka, ki ju želite uporabiti, nato uporabite naslednjo formulo v celico, kamor želite dati rezultat v drugem delovnem zvezku. Nato povlecite in kopirajte to formulo v druge celice, ki jih potrebujete

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Rezultat:

Opombe:

  • V zgornji formuli:
    • B2 predstavlja vrednost iskanja;
    • '[Seznam izdelkov.xlsx]List1'!A2:B6 označuje iskanje v obsegu A2:B6 na listu z imenom List1 iz delovnega zvezka Seznam izdelkov; (Sklicevanje na delovni zvezek je v oglatih oklepajih, celoten delovni zvezek + list pa v enojnih narekovajih.)
    • 2 je številka stolpca, ki vsebuje ujemajoče se podatke, iz katerih se želite vrniti;
    • 0 označuje vrnitev natančnega ujemanja.
  • Če je delovni zvezek za iskanje zaprt, bo celotna pot datoteke za delovni zvezek za iskanje prikazana v formuli, kot je prikazan na naslednjem posnetku zaslona:

2.9 Vrni prazno ali določeno besedilo namesto napake 0 ali #N/A

Običajno, ko uporabite funkcijo VLOOKUP za vrnitev ustrezne vrednosti, bo ujemajoča se celica prazna vrnila 0. In če ujemajoče se vrednosti ni mogoče najti, boste prejeli vrednost napake #N/A, kot je prikazano v posnetek zaslona spodaj. Če želite prikazati prazno celico ali določeno vrednost namesto 0 ali #N/A, to VLOOKUP za vrnitev prazne ali določene vrednosti namesto 0 ali N/A vadnica vam lahko naredi uslugo.


Primeri naprednega VLOOKUP-a

3.1 Dvosmerno iskanje (VLOOKUP v vrstici in stolpcu)

Včasih boste morda morali izvesti 2-dimenzionalno iskanje, kar pomeni istočasno iskanje vrednosti v vrstici in stolpcu. Na primer, če imate naslednji obseg podatkov in boste morda morali pridobiti vrednost za določen izdelek v določenem četrtletju. Ta razdelek bo predstavil formulo za obravnavo tega posla v Excelu.

V Excelu lahko uporabite kombinacijo funkcij VLOOKUP in MATCH za dvosmerno iskanje.

Uporabite naslednjo formulo v prazno celico in pritisnite Vnesite ključ, da dobite rezultat.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

Opomba: V zgornji formuli:

  • G2 je iskalna vrednost v stolpcu, na podlagi katere želite pridobiti ustrezno vrednost;
  • A2: E7 je podatkovna tabela, iz katere boste gledali;
  • H1 je vrednost iskanja v vrstici, na podlagi katere želite pridobiti ustrezno vrednost;
  • A2: E2 so celice naslovov stolpcev;
  • FALSE označuje, da dobite natančno ujemanje.

3.2 Vrednost ujemanja VLOOKUP na podlagi dveh ali več meril

Enostavno lahko poiščete ujemajočo se vrednost na podlagi enega kriterija, če pa imate dva ali več kriterijev, kaj lahko storite?

 3.2.1 Vrednost ujemanja VLOOKUP na podlagi dveh ali več kriterijev s formulami

V tem primeru vam lahko funkcije LOOKUP ali MATCH in INDEX v Excelu pomagajo hitro in enostavno rešiti to nalogo.

Na primer, imam spodnjo tabelo s podatki, da vam vrnem ujeto ceno glede na določen izdelek in velikost, vam lahko pomagajo naslednje formule.

1. korak: Uporabite katero koli formulo

Formula 1: Ko prilepite formulo, pritisnite Vnesite ključ.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Formula 2: Ko prilepite formulo, pritisnite Ctrl + Shift + Enter ključi.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Rezultat:

Opombe:

  • V zgornjih formulah:
    • A2: A12 = G1 pomeni iskanje kriterijev G1 v območju A2:A12;
    • B2: B12 = G2 pomeni iskanje po kriterijih G2 v območju B2:B12;
    • D2: D12 is obseg, iz katerega želite vrniti ustrezno vrednost.
  • Če imate več kot dva kriterija, morate v formulo samo združiti druge kriterije, na primer:
    =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
    =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
 3.2.2 Vrednost ujemanja VLOOKUP na podlagi dveh ali več meril s pametno funkcijo

Zapomniti si zgornje zapletene formule, ki jih je treba večkrat uporabiti, je lahko izziv, kar lahko upočasni vašo delovno učinkovitost. vendar Kutools za Excel ponudbe Iskanje v več pogojih funkcija, ki vam omogoča vrnitev ustreznega rezultata na podlagi enega ali več pogojev z le nekaj kliki.

  1. klik Kutools > Super POGLED > Iskanje v več pogojih da omogočite to funkcijo.
  2. Nato določite operacije v pogovornem oknu na podlagi vaših podatkov.
Opombe: Če želite uporabiti to funkcijo, morate prenesti Kutools za Excel s 30-dnevno brezplačno preskusno različico najprej.


3.3 VLOOKUP za vrnitev več vrednosti z enim ali več kriteriji

V Excelu funkcija VLOOKUP išče vrednost in vrne samo prvo ujemajočo se vrednost, če je najdenih več ustreznih vrednosti. Včasih boste morda želeli vrniti vse ustrezne vrednosti v vrstici, stolpcu ali v eni celici. Ta razdelek govori o tem, kako vrniti več ujemajočih se vrednosti z enim ali več pogoji v delovnem zvezku.

 3.3.1 VLOOKUP vse ujemajoče se vrednosti na podlagi enega ali več pogojev vodoravno

Ob predpostavki, da imate tabelo podatkov, ki vsebuje državo, mesto in imena v obsegu A1:C14, in zdaj želite vodoravno vrniti vsa imena, ki so iz »ZDA«, kot je prikazano na spodnjem posnetku zaslona. Za rešitev te naloge prosim kliknite tukaj, da dobite rezultat korak za korakom.

 3.3.2 VLOOKUP vse ujemajoče se vrednosti na podlagi enega ali več pogojev navpično

Če morate Vlookup vrniti vse ujemajoče se vrednosti navpično na podlagi določenih meril, kot je prikazano na spodnjem posnetku zaslona, Prosimo, kliknite tukaj, če želite podrobno rešitev.

 3.3.3 VLOOKUP vse ujemajoče se vrednosti na podlagi enega ali več pogojev v eno celico

Če želite Vlookup in vrniti več ujemajočih se vrednosti v eno celico z določenim ločilom, nova funkcija TEXTJOIN vam lahko pomaga hitro in enostavno rešiti to nalogo.

Opombe:


3.4 VLOOKUP za vrnitev celotne vrstice ujemajoče se celice

V tem razdelku bom govoril o tem, kako s funkcijo VLOOKUP pridobiti celotno vrstico ujemajoče se vrednosti.

1. korak: Uporabite in izpolnite naslednjo formulo

Kopirajte ali vnesite spodnjo formulo v prazno celico, kjer želite izpisati rezultat, in pritisnite Vnesite ključ, da dobite prvo vrednost. Nato povlecite celico formule v desno, dokler se ne prikažejo podatki celotne vrstice.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Rezultat:

Zdaj lahko vidite, da so vrnjeni celotni podatki vrstice. Oglejte si posnetek zaslona:
funkcija vpogleda doc 50 1

Opomba: v zgornji formuli:

  • F2 je iskalna vrednost, na podlagi katere želite vrniti celotno vrstico;
  • A1: D12 je obseg podatkov, iz katerega želite iskati iskalno vrednost;
  • A1 označuje številko prvega stolpca v vašem obsegu podatkov;
  • FALSE označuje natančno iskanje.

Nasvet:

  • Če je na podlagi ujemajoče se vrednosti najdenih več vrstic, za vrnitev vseh ustreznih vrstic uporabite spodnjo formulo in pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvi rezultat. Nato povlecite ročico za polnjenje v desno. In nato nadaljujte z vlečenjem ročice za polnjenje navzdol čez celice, da dobite vse ujemajoče se vrstice. Oglejte si spodnjo predstavitev:
    =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")
    funkcija vpogleda doc 51 2

3.5 Ugnezdeni VLOOKUP v Excelu

Včasih boste morda morali poiskati vrednosti, ki so med seboj povezane v več tabelah. V tem primeru lahko več funkcij VLOOKUP ugnezdite skupaj, da dobite končno vrednost.

Na primer, imam delovni list, ki vsebuje dve ločeni tabeli. V prvi tabeli so navedena vsa imena izdelkov skupaj z njihovim ustreznim prodajalcem. V drugi tabeli je navedena skupna prodaja vsakega prodajalca. Zdaj, če želite poiskati prodajo vsakega izdelka, kot je prikazano na naslednjem posnetku zaslona, ​​lahko ugnezdite funkcijo VLOOKUP, da opravite to nalogo.
funkcija vpogleda doc 53 1

Generična formula za ugnezdeno funkcijo VLOOKUP je:

=VLOOKUP(VLOOKUP(lookup_value, table_array1, col_index_num1, 0), table_array2, col_index_num2, 0)

Opomba:

  • lookup_value je vrednost, ki jo iščete;
  • Tabela_matrika1, Tabela_matrika2 so tabele, v katerih obstajata vrednost iskanja in vrnjena vrednost;
  • col_index_num1 označuje številko stolpca v prvi tabeli za iskanje vmesnih skupnih podatkov;
  • col_index_num2 označuje številko stolpca v drugi tabeli, za katero želite vrniti ujemajočo se vrednost;
  • 0 se uporablja za natančno ujemanje.

1. korak: Uporabite in izpolnite naslednjo formulo

Uporabite naslednjo formulo v prazno celico in nato povlecite ročico za polnjenje navzdol do celic, za katere želite uporabiti to formulo.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Rezultat:

Zdaj boste dobili rezultat, kot je prikazan na naslednjem posnetku zaslona:

Opomba: v zgornji formuli:

  • G3 vsebuje vrednost, ki jo iščete;
  • A3: B7, D3: E7 so obsegi tabel, v katerih obstajata vrednost iskanja in vrnjena vrednost;
  • 2 je številka stolpca v obsegu, iz katerega naj se vrne ujemajoča se vrednost.
  • 0 označuje natančno matematiko VLOOKUP.

3.6 Preverite, ali vrednost obstaja na podlagi podatkov seznama v drugem stolpcu

Funkcija VLOOKUP vam lahko tudi pomaga preveriti, ali obstajajo vrednosti na podlagi seznama podatkov v drugem stolpcu. Na primer, če želite poiskati imena v stolpcu C in samo vrniti Da ali Ne, če je ime najdeno ali ne v stolpcu A, kot je prikazano na spodnjem posnetku zaslona.
funkcija vpogleda doc 56 1

1. korak: Uporabite in izpolnite naslednjo formulo

Uporabite naslednjo formulo v prazno celico, nato povlecite ročico za polnjenje navzdol do celic, ki jih želite zapolniti s to formulo.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Rezultat:

In dobili boste rezultat, kot ga potrebujete, glejte posnetek zaslona:

Opomba: v zgornji formuli:

  • C2 je vrednost iskanja, ki jo želite preveriti;
  • A2: A10 je seznam obsega, od koder preverite, ali bodo iskalne vrednosti najdene ali ne;
  • FALSE označuje, da dobite natančno ujemanje.

3.7 VLOOKUP in seštejte vse ujemajoče se vrednosti v vrsticah ali stolpcih

Pri delu s številskimi podatki boste morda morali izvleči ujemajoče se vrednosti iz tabele in sešteti števila v več stolpcih ali vrsticah. Ta razdelek bo predstavil nekaj formul, ki vam lahko pomagajo pri izpolnjevanju te naloge.

 3.7.1 VLOOKUP in seštejte vse ujemajoče se vrednosti v vrstici ali več vrsticah

Recimo, da imate seznam izdelkov s prodajo več mesecev, kot je prikazano na naslednjem posnetku zaslona. Zdaj morate sešteti vsa naročila v vseh mesecih glede na dane izdelke.

1. korak: Uporabite in izpolnite naslednjo formulo

Kopirajte ali vnesite naslednjo formulo v prazno celico in pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvi rezultat. Nato povlecite ročico za polnjenje navzdol, da kopirate to formulo v druge celice, ki jih potrebujete.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Rezultat:

Vse vrednosti v vrstici prve ujemajoče se vrednosti so bile seštete, glejte posnetek zaslona:

Opomba: v zgornji formuli:

  • H2 je celica, ki vsebuje vrednost, ki jo iščete;
  • A2: F9 je obseg podatkov (brez naslovov stolpcev), ki vključuje iskalno vrednost in ujemajoče se vrednosti;
  • 2,3,4,5,6 {} so številke stolpcev, ki se uporabljajo za izračun vsote obsega;
  • FALSE označuje natančno ujemanje.

Nasvet: Če želite sešteti vsa ujemanja v več vrsticah, uporabite naslednjo formulo:

  • =SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
 3.7.2 VLOOKUP in seštejte vse ujemajoče se vrednosti v stolpcu ali več stolpcih

Če želite sešteti skupno vrednost za določene mesece, kot je prikazano na spodnjem posnetku zaslona. Običajna funkcija VLOOKUP vam morda ne bo pomagala, tukaj morate uporabiti funkcije SUM, INDEX in MATCH skupaj, da ustvarite formulo.

1. korak: Uporabite naslednjo formulo

Uporabite spodnjo formulo v prazno celico in nato povlecite ročico za polnjenje navzdol, da kopirate to formulo v druge celice.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Rezultat:

Zdaj so bile prve ujemajoče se vrednosti glede na določen mesec v stolpcu seštete, glejte posnetek zaslona:

Opomba: v zgornji formuli:

  • H2 je celica, ki vsebuje vrednost, ki jo iščete;
  • B1: F1 so glave stolpcev, ki vsebujejo iskalno vrednost;
  • B2: F9 je obseg podatkov, ki vsebuje številske vrednosti, ki jih želite sešteti.

Nasvet: Za VLOOKUP in seštevanje vseh ujemajočih se vrednosti v več stolpcih uporabite naslednjo formulo:

  • =SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
 3.7.3 VLOOKUP in seštejte prve ali vse ujemajoče se vrednosti z zmogljivo funkcijo

Morda si zgornje formule težko zapomnite, v tem primeru vam priporočam zmogljivo funkcijo - Iskanje in vsota of Kutools za Excel, s to funkcijo lahko kar se da preprosto poiščete in seštejete prve ujemajoče se ali vse ujemajoče se vrednosti v vrsticah ali stolpcih.

  1. klik Kutools > Super POGLED > POGLED in vsota da omogočite to funkcijo.
  2. Nato določite operacije v pogovornem oknu glede na vaše potrebe.
Opombe: Če želite uporabiti to funkcijo, morate prenesti Kutools za Excel s 30-dnevno brezplačno preskusno različico najprej.
 3.7.4 VLOOKUP in seštejte vse ujemajoče se vrednosti v vrsticah in stolpcih

Če želite vrednosti strniti, ko se morate na primer ujemati tako v stolpcu kot v vrstici, če želite na primer dobiti skupno vrednost izdelka Pulover v mesecu marcu, kot je prikazano spodaj.

Tukaj lahko uporabite funkcijo SUMPRODCT za izvedbo te naloge.

Uporabite naslednjo formulo v celico in pritisnite Vnesite tipko, da dobite rezultat, glejte sliko zaslona:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

Opomba: V zgornji formuli:

  • B2: F9 je obseg podatkov, ki vsebuje številske vrednosti, ki jih želite sešteti;
  • B1: F1 so glave stolpcev, ki vsebujejo iskalno vrednost, na podlagi katere želite sešteti;
  • I2 je vrednost iskanja v iskanih glavah stolpcev;
  • A2: A9 so glave vrstic, ki vsebujejo iskalno vrednost, na podlagi katere želite sešteti;
  • H2 je vrednost iskanja v glavah vrstic, ki jih iščete.

3.8 VLOOKUP za združitev dveh tabel na podlagi ključnih stolpcev

Pri vsakodnevnem delu, ko analizirate podatke, boste morda morali zbrati vse potrebne informacije v eno samo tabelo na podlagi enega ali več ključnih stolpcev. Za izpolnitev te naloge lahko uporabite funkciji INDEX in MATCH namesto funkcije VLOOKUP.

 3.8.1 VLOOKUP za združitev dveh tabel na podlagi enega ključnega stolpca

Na primer, imate dve tabeli, prva tabela vsebuje podatke o izdelkih in imenih, druga tabela pa vsebuje podatke o izdelkih in naročilih, zdaj pa želite združiti ti dve tabeli tako, da povežete skupni stolpec izdelkov v eno tabelo.

1. korak: Uporabite in izpolnite naslednjo formulo

Uporabite naslednjo formulo v prazno celico. Nato ročico za polnjenje povlecite navzdol do celic, za katere želite uporabiti to formulo

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Rezultat:

Zdaj boste dobili združeno tabelo s stolpcem vrstnega reda, ki se pridružuje prvi tabeli na podlagi podatkov o ključnem stolpcu.

Opomba: V zgornji formuli:

  • A2 je iskalna vrednost, ki jo iščete;
  • F2: F8 je obseg podatkov, za katere želite vrniti ujemajoče se vrednosti;
  • E2: E8 je obseg iskanja, ki vsebuje iskalno vrednost.
 3.8.2 VLOOKUP za združitev dveh tabel na podlagi več ključnih stolpcev

Če imata tabeli, ki ju želite združiti, več ključnih stolpcev, sledite spodnjim korakom, da združite tabeli na podlagi teh skupnih stolpcev.

Splošna formula je:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

Opomba:

  • iskalna_tabela obseg podatkov vsebuje podatke iskanja in ustrezne zapise;
  • iskalna_vrednost1 je prvo merilo, ki ga iščete;
  • iskalni_obseg1 ali seznam podatkov vsebuje prvi kriterij;
  • iskalna_vrednost2 je drugo merilo, ki ga iščete;
  • iskalni_obseg2 ali seznam podatkov vsebuje drugi kriterij;
  • povratna_številka_stolpca označuje številko stolpca v lookup_table, za katero želite vrniti ujemajočo se vrednost.

1. korak: Uporabite naslednjo formulo

Uporabite spodnjo formulo v prazno celico, kamor želite vnesti rezultat, in nato pritisnite Ctrl + Shift + Enter tipke skupaj, da dobite prvo ujemajočo se vrednost, glejte posnetek zaslona:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

2. korak: Izpolnite formulo v druge celice

Nato izberite prvo celico s formulo in povlecite ročico za polnjenje, da kopirate to formulo v druge celice, kot jih potrebujete:

nasveti: V Excelu 2016 ali novejših različicah lahko uporabite tudi Power Query funkcija za združitev dveh ali več tabel v eno na podlagi ključnih stolpcev. Prosimo, kliknite za podrobnosti korak za korakom.

3.9 VLOOKUP ujemanje vrednosti na več delovnih listih

Ali ste kdaj morali izvesti VLOOKUP na več delovnih listih v Excelu? Če imate na primer tri delovne liste z obsegi podatkov in želite iz teh listov pridobiti določene vrednosti na podlagi meril, lahko sledite vadnici po korakih Vrednosti VLOOKUP na več delovnih listih za izvedbo te naloge.


Ustrezne vrednosti VLOOKUP ohranijo oblikovanje celic

Pri iskanju ujemajočih se vrednosti izvirno oblikovanje celice, kot je barva pisave, barva ozadja, oblika podatkov itd., ne bo ohranjeno. Če želite ohraniti oblikovanje celice ali podatkov, bo ta razdelek predstavil nekaj trikov za reševanje nalog.

4.1 VLOOKUP Ujemanje vrednosti in ohranite barvo celice, oblikovanje pisave

Kot vsi vemo, lahko običajna funkcija VLOOKUP pridobi samo ujemajočo se vrednost iz drugega obsega podatkov. Vendar pa lahko pride do primerov, ko želite dobiti ustrezno vrednost skupaj z oblikovanjem celice, na primer barvo polnila, barvo pisave in slog pisave. V tem razdelku bomo razpravljali o tem, kako pridobiti ujemajoče se vrednosti, pri tem pa ohraniti izvorno oblikovanje v Excelu.

Za iskanje in vrnitev ustrezne vrednosti skupaj s formatiranjem celic sledite naslednjim korakom:

1. korak: Kopirajte kodo 1 v modul kode lista

  1. Na delovnem listu so podatki, ki jih želite VLOOKUP, z desno miškino tipko kliknite zavihek lista in izberite Ogled kode iz kontekstnega menija. Oglejte si posnetek zaslona:
  2. V odprtem Microsoft Visual Basic za aplikacije okno, kopirajte spodnjo kodo VBA v okno Code.
  3. Koda VBA 1: VLOOKUP za pridobitev oblikovanja celice skupaj z iskalno vrednostjo
  4. Sub Worksheet_Change(ByVal Target As Range)
    'Updateby Extendoffice
        Dim I As Long
        Dim xKeys As Long
        Dim xDicStr As String
        On Error Resume Next
        Application.ScreenUpdating = False
        xKeys = UBound(xDic.Keys)
        If xKeys >= 0 Then
            For I = 0 To UBound(xDic.Keys)
                xDicStr = xDic.Items(I)
                If xDicStr <> "" Then
                    Range(xDic.Keys(I)).Interior.Color = _
                    Range(xDic.Items(I)).Interior.Color
                    Range(xDic.Keys(I)).Font.FontStyle = _
                    Range(xDic.Items(I)).Font.FontStyle
                    Range(xDic.Keys(I)).Font.Size = _
                    Range(xDic.Items(I)).Font.Size
                    Range(xDic.Keys(I)).Font.Color = _
                    Range(xDic.Items(I)).Font.Color
                    Range(xDic.Keys(I)).Font.Name = _
                    Range(xDic.Items(I)).Font.Name
                    Range(xDic.Keys(I)).Font.Underline = _
                    Range(xDic.Items(I)).Font.Underline
                Else
                    Range(xDic.Keys(I)).Interior.Color = xlNone
                End If
            Next
            Set xDic = Nothing
        End If
        Application.ScreenUpdating = True
    End Sub
    

2. korak: Kopirajte kodo 2 v okno modula

  1. Še vedno v Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Moduliin nato v okno modula kopirajte spodnjo kodo VBA 2.
  2. Koda VBA 2: VLOOKUP za pridobitev oblikovanja celice skupaj z iskalno vrednostjo
  3. Public xDic As New Dictionary
    Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
        Dim xFindCell As Range
        On Error Resume Next
        Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
        If xFindCell Is Nothing Then
            LookupKeepFormat = ""
            xDic.Add Application.Caller.Address, ""
        Else
            LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
            xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
        End If
    End Function
    

3. korak: Izberite možnost za VBAproject

  1. Ko vstavite zgornje kode, kliknite Orodja > Reference v Microsoft Visual Basic za aplikacije okno. Nato preverite Izvajanje Microsoftovih skriptov potrditveno polje v Reference - VBAProject pogovorno okno. Oglejte si posnetke zaslona:
  2. Nato kliknite OK da zaprete pogovorno okno, nato pa shranite in zaprite okno kode.

4. korak: Vnesite formulo za pridobitev rezultata

  1. Zdaj pa se vrnite na delovni list in uporabite naslednjo formulo. Nato povlecite ročico za polnjenje navzdol, da dobite vse rezultate skupaj z njihovim oblikovanjem. Oglejte si posnetek zaslona:
    =LookupKeepFormat(E2,$A$1:$C$10,3)

Opomba: v zgornji formuli:

  • E2 je vrednost, ki jo boste iskali;
  • A1: C10 je obseg tabele;
  • 3 je številka stolpca tabele, iz katere želite pridobiti ujemajočo se vrednost.

4.2 Ohranite obliko datuma iz vrnjene vrednosti VLOOKUP

Pri uporabi funkcije VLOOKUP za iskanje in vrnitev vrednosti z obliko zapisa datuma se lahko vrnjeni rezultat prikaže kot število. Če želite ohraniti obliko datuma v vrnjenem rezultatu, morate funkcijo VLOOKUP vključiti v funkcijo TEXT.

1. korak: Uporabite in izpolnite naslednjo formulo

Uporabite spodnjo formulo v prazno celico. Nato povlecite ročico za polnjenje, da kopirate to formulo v druge celice.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Rezultat:

Vsi ujemajoči se datumi so bili vrnjeni, kot je prikazano na spodnjem posnetku zaslona:

Opomba: V zgornji formuli:

  • E2 je iskalna vrednost;
  • A2: C9 je obseg iskanja;
  • 3 je številka stolpca, za katerega želite vrniti vrednost;
  • FALSE označuje, da dobite natančno ujemanje;
  • mm / dd / llll je oblika datuma, ki jo želite obdržati.

4.3 Vrnite komentar celice iz funkcije VLOOKUP

Ali ste kdaj morali pridobiti ujemajoče se podatke celice in z njimi povezan komentar z uporabo funkcije VLOOKUP v Excelu, kot je prikazano na naslednjem posnetku zaslona? Če je tako, vam lahko spodaj navedena uporabniško definirana funkcija pomaga pri izpolnitvi te naloge.

1. korak: Kopirajte kodo v modul

  1. Drži dol ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.
  2. klik Vstavi > Moduli, nato v okno modula kopirajte in prilepite naslednjo kodo.
    Koda VBA: Vlookup in vrnitev ujemajoče se vrednosti s komentarjem celice:
    Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
    'Updateby Extendoffice
        Application.Volatile
        Dim xRet As Variant 'could be an error
        Dim xCell As Range
        xRet = Application.Match(LookVal, FTable.Columns(1), FType)
        If IsError(xRet) Then
            VlookupComment = "Not Found"
        Else
            Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
            VlookupComment = xCell.Value
            With Application.Caller
                If Not .Comment Is Nothing Then
                    .Comment.Delete
                End If
                If Not xCell.Comment Is Nothing Then
                    .AddComment xCell.Comment.Text
                End If
            End With
        End If
    End Function
  3. Nato shranite in zaprite okno kode.

2. korak: Vnesite formulo, da dobite rezultat

  1. Zdaj vnesite naslednjo formulo in povlecite ročico za polnjenje, da kopirate to formulo v druge celice. Istočasno bo vrnil ujemajoče se vrednosti in komentarje, glejte posnetek zaslona:
    =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

Opomba: V zgornji formuli:

  • D2 je iskalna vrednost, za katero želite vrniti ustrezno vrednost;
  • A2: B9 je podatkovna tabela, ki jo želite uporabiti;
  • 2 je številka stolpca, ki vsebuje ujemajočo se vrednost, ki jo želite vrniti;
  • FALSE označuje, da dobite natančno ujemanje.

4.4 Številke VLOOKUP, shranjene kot besedilo

Na primer, imam vrsto podatkov, pri katerih je številka ID v izvirni tabeli v številski obliki in je številka ID v celicah za iskanje shranjena kot besedilo, pri uporabi običajne funkcije VLOOKUP lahko naletite na napako #N/A. V tem primeru lahko za pridobitev pravilnih informacij zavijete funkciji TEXT in VALUE v funkcijo VLOOKUP. Spodaj je formula, s katero to dosežete:

1. korak: Uporabite in izpolnite naslednjo formulo

Uporabite naslednjo formulo v prazno celico in nato povlecite ročico za polnjenje navzdol, da kopirate to formulo.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Rezultat:

Zdaj boste dobili pravilne rezultate, kot je prikazano na spodnjem posnetku zaslona:

Opombe:

  • V zgornji formuli:
    • D2 je iskalna vrednost, za katero želite vrniti ustrezno vrednost;
    • A2: B8 je podatkovna tabela, ki jo želite uporabiti;
    • 2 je številka stolpca, ki vsebuje ujemajočo se vrednost, ki jo želite vrniti;
    • 0 označuje, da dobite natančno ujemanje.
  • Ta formula dobro deluje tudi, če niste prepričani, kje imate številke in kje besedilo.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations