Iskanje vrednosti s funkcijami VLOOKUP, INDEX ali MATCH

Velja za
Excel za Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

Namig

Poskusite uporabiti nove funkcije XLOOKUP in XMATCH , izboljšane različice funkcij, opisanih v tem članku. Te nove funkcije delujejo v kateri koli smeri in privzeto vrnejo natančne ujemanja, zaradi česar so preprostejše in priročnejše za uporabo kot njihove predhodnike.

Recimo, da imate seznam številk pisarn in morate vedeti, kateri zaposleni so v vsaki pisarni. Preglednica je ogromna, zato morda mislite, da je to zahtevna naloga. Pravzaprav je to precej enostavno narediti s funkcijo iskanja.

Funkciji VLOOKUP in HLOOKUP sta skupaj s funkcijama INDEX in MATCH ena najbolj uporabnih funkcij v Excelu.

Opomba

Funkcija čarovnika za iskanje ni več na voljo v Excelu.

Tukaj je primer uporabe funkcije VLOOKUP.

=VLOOKUP(B2,C2:E7,3,TRUE)

V tem primeru je B2 prvi argument – element podatkov, ki ga funkcija potrebuje za delovanje. Za VLOOKUP je ta prvi argument vrednost, ki jo želite najti. Ta argument je lahko sklic na celico ali fiksna vrednost, na primer »koval« ali 21.000. Drugi argument je obseg celic C2-:E7, v katerem želite poiskati vrednost, ki jo želite najti. Tretji argument je stolpec v obsegu celic, ki vsebuje vrednost, ki jo iščete.

Četrti argument je neobvezen. Vnesite TRUE ali FALSE. Če vnesete TRUE ali če pustite argument prazen, funkcija vrne približek vrednosti, ki ste jo navedli v prvem argumentu. Če vnesete FALSE, se bo funkcija ujemala z vrednostjo, ki jo je navedel prvi argument. Z drugimi besedami, če pustite četrti argument prazen – ali vnesete TRUE – vam daje več prilagodljivosti.

Ta primer kaže, kako funkcija deluje. Ko vnesete vrednost v celico B2 (prvi argument), funkcija VLOOKUP preišče celice v obsegu C2:E7 (2. argument) in vrne najbližje približno ujemanje iz tretjega stolpca v obsegu, stolpca E (3. argument).

Tipična raba funkcije VLOOKUP

Četrti argument je prazen, zato funkcija vrne približno ujemanje. Če temu ni tako, morate vnesti eno od vrednosti v stolpec C ali D, da dobite rezultat.

Če vam VLOOKUP ustreza, je funkcija HLOOKUP enako enostavna za uporabo. Vnesete iste argumente, vendar iščete v vrsticah namesto po stolpcih.

Uporaba funkcij INDEX in MATCH namesto funkcije VLOOKUP

Pri uporabi funkcije VLOOKUP obstajajo določene omejitve – funkcija VLOOKUP lahko poišče vrednost le od leve proti desni. To pomeni, da mora biti stolpec z vrednostjo, ki jo iščete, vedno levo od stolpca z vrnjeno vrednostjo. Če preglednica ni ustvarjena na ta način, ne uporabljajte funkcije VLOOKUP. Namesto tega uporabite kombinacijo funkcij INDEX in MATCH.

Ta primer prikazuje majhen seznam, kjer vrednost, ki jo želimo iskati, Chicago, ni v skrajnem levem stolpcu. Zato ne moremo uporabiti funkcije VLOOKUP. Namesto tega bomo uporabili funkcijo MATCH, da poiščemo Chicago v obsegu B1:B11. Najdemo ga v vrstici 4. Nato funkcija INDEX uporabi to vrednost kot argument za iskanje in v 4. stolpcu (stolpec D) poišče populacijo za Chicago. Uporabljena formula je prikazana v celici A14.

Iskanje vrednosti s funkcijo INDEX in MATCH

Poskusite

Če želite preizkusiti funkcije iskanja, preden jih preizkusite z lastnimi podatki, je tukaj nekaj vzorčnih podatkov.

Primer VLOOKUP v službi

Kopirajte naslednje podatke v prazno preglednico.

Namig

Preden prilepite podatke v Excel, nastavite širino stolpcev za stolpce od A do C na 250 slikovnih pik in kliknite Prelomi besedilo (zavihek Osnovno , skupina Poravnava ).

Gostota Viskoznost Temperatura
0,457 3,55 500
0,525 3,25 400
0,606 2,93 300
0,675 2,75 250
0,746 2,57 200
0,835 2,38 150
0,946 2,17 100
1,09 1,95 50
1,29 1,71 0
Formula Opis Rezultat
=VLOOKUP(1,A2:C10,2) Funkcija, ki išče približno ujemanje, poišče vrednost 1 v stolpcu A, poišče največjo vrednost v stolpcu A, ki je manjša ali enaka 1, in sicer 0,946, nato pa vrne ustrezno vrednost v isti vrstici iz stolpca B. 2,17
=VLOOKUP(1,A2:C10,3,TRUE) Funkcija, ki išče približno ujemanje, poišče vrednost 1 v stolpcu A, poišče največjo vrednost v stolpcu A, ki je manjša ali enaka 1, in sicer 0,946, nato pa vrne ustrezno vrednost v isti vrstici iz stolpca C. 100
=VLOOKUP(0.7,A2:C10,3,FALSE) Funkcija, ki išče vrednost, ki se popolnoma ujema, poišče vrednost 0,7 v stolpcu A. Ker v stolpcu ni vrednosti, ki bi se popolnoma ujemala, vrne funkcija napako. #N/V
=VLOOKUP(0.1,A2:C10,2,TRUE) Funkcija, ki išče približek vrednosti, poišče vrednost 0,1 v stolpcu A. Ker je 0,1 manj kot najmanjša vrednost v stolpcu A, funkcija vrne napako. #N/V
=VLOOKUP(2,A2:C10,2,TRUE) Funkcija, ki išče približno ujemanje, poišče vrednost 2 v stolpcu A, najde največjo vrednost v stolpcu A, ki je manjša ali enaka 2, in sicer 1,29, nato pa vrne ustrezno vrednost v isti vrstici iz stolpca B. 1,71

Primer HLOOKUP

Kopirajte vse celice v tej tabeli in jih prilepite v celico A1 na praznem Excelovem delovnem listu.

Namig

Preden prilepite podatke v Excel, nastavite širino stolpcev za stolpce A–C na 250 slikovnih pik in kliknite »Prelomi besedilo « (zavihek »Osnovno «, skupina »Poravnava «).

Gredi Ležaji Zapahi
4 4 9
5 7 10
6 8 11
Formula Opis Rezultat
=HLOOKUP("Gredi"; A1:C4; 2; TRUE) Poišče »Gredi« v 1. vrstici in vrne vrednost iz 2. vrstice istega stolpca (stolpec A). 4
=HLOOKUP("Ležaji"; A1:C4; 3; FALSE) Poišče »Ležaji« v 1. vrstici in vrne vrednost iz 3. vrstice istega stolpca (stolpec B). 7
=HLOOKUP("B", A1:C4, 3, TRUE) Poišče »B« v 1. vrstici in vrne vrednost iz 3. vrstice, ki je v istem stolpcu. Ker ni bilo najdeno natančno ujemanje za »B«, je uporabljena največja vrednost v 1. vrstici, ki je manjša kot »B«: »Gredi« v stolpcu A. 5
=HLOOKUP("Zapahi"; A1:C4; 4) Poišče »Zapahi« v 1. vrstici in vrne vrednost iz 4. vrstice istega stolpca (stolpec C). 11
=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE) Poišče število 3 v trivrstični konstanti v obliki polja in vrne vrednost iz 2. vrstice v istem stolpcu (v tem primeru tretjem). V konstanti v obliki polja so tri vrstice vrednosti, pri čemer je vsaka ločena s podpičjem (;). Ker je vrednost »c« najdena v 2. vrstici in v istem stolpcu kot število 3, je »c« vrnjena vrednost. c

Primera funkcij INDEX in MATCH

V zadnjem primeru sta funkciji INDEX in MATCH uporabljeni hkrati zaradi pridobivanja številke najnovejšega računa in ustreznega datuma za vsako od petih mest. Ker je datum vrnjen kot številka, uporabimo funkcijo TEXT, da ga oblikujemo kot datum. Funkcija INDEX dejansko uporabi rezultat funkcije MATCH kot argument. Kombinacija funkcij INDEX in MATCH je uporabljena dvakrat v vsaki formuli – prvič zaradi pridobivanja številke računa, drugič zaradi pridobivanja datuma.

Kopirajte vse celice v tej tabeli in jih prilepite v celico A1 na praznem Excelovem delovnem listu.

Namig

Preden prilepite podatke v Excel, nastavite širino stolpcev za stolpce A–D na 250 slikovnih pik in kliknite »Prelomi besedilo « (zavihek »Osnovno «, skupina »Poravnava «).

Račun Mesto Datum računa Najnovejši račun glede na mesto, z datumom
3115 Atlanta 7.4.2012 ="Atlanta = "&INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),1)& ", Datum računa: " & TEXT(INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),3),"m/d/ll")
3137 Atlanta 9.4.2012 ="Austin = "&INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),1)& ", Datum računa: " & TEXT(INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),3),"m/d/ll")
3154 Atlanta 11.4.2012 ="Dallas = "&INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),1)& ", Datum računa: " & TEXT(INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),3),"m/d/ll")
3191 Atlanta 21.4.2012 ="New Orleans = "&INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),1)& ", Datum računa: " & TEXT(INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),3),"m/d/ll")
3293 Atlanta 25.4.2012 ="Tampa = "&INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),1)& ", Datum računa: " & TEXT(INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),3),"m/d/yy")
3331 Atlanta 27.4.2012
3350 Atlanta 28.4.2012
3390 Atlanta 1.5.2012
3441 Atlanta 2.5.2012
3517 Atlanta 8.5.2012
3124 Austin 9.4.2012
3155 Austin 11.4.2012
3177 Austin 19.4.2012
3357 Austin 28.4.2012
3492 Austin 6.5.2012
3316 Dallas 25.4.2012
3346 Dallas 28.4.2012
3372 Dallas 1.5.2012
3414 Dallas 1.5.2012
3451 Dallas 2.5.2012
3467 Dallas 2.5.2012
3474 Dallas 4.5.2012
3490 Dallas 5.5.2012
3503 Dallas 8.5.2012
3151 New Orleans 9.4.2012
3438 New Orleans 2.5.2012
3471 New Orleans 4.5.2012
3160 Tampa 18.4.2012
3328 Tampa 26.4.2012
3368 Tampa 29.4.2012
3420 Tampa 1.5.2012
3501 Tampa 6.5.2012

Glejte tudi

Kartica za hitri sklic: Funkcija VLOOKUP

Funkcije za iskanje in sklicevanje (sklicevanje)

Uporaba argumenta »matrika_tabele« v funkciji VLOOKUP

Brezplačen uvod v Excel v spletu