Väärtuste otsimine funktsiooniga VLOOKUP, INDEX või MATCH

Väärtuse otsimiseks pikast loendist saate kasutada otsingufunktsiooni. Funktsiooni VLOOKUP kasutatakse sageli, kuid abi võib ka olla funktsiooni HLOOKUP ning funktsioonide INDEX ja MATCH koos kasutamisest.

Funktsioon VLOOKUP ja selle argumendid on järgmised.

=VLOOKUP(<otsitav väärtus>;<otsinguvahemik>;<veerg>;<vastendustüüp>)

Näiteks: =VLOOKUP(21500;C2:E7;3;FALSE)

  • Esimene argument (osa, mida funktsioon töötamiseks vajab) on väärtus, mille järgi soovite otsida. See võib olla nii lahtriviide (nt B2) kui ka konkreetne väärtus (nt "soo" või 21500).

  • Teine argument on lahtrivahemik, mis teie arvates võiks teie otsitavat väärtust sisaldada.

    NB! : Funktsiooni VLOOKUP korral peab otsitavat väärtust või lahtriviidet sisaldav veerg olema vahemiku kõige vasakpoolsem.

  • Kolmas argument on lahtrite otsinguvahemiku see veerg, mis sisaldab väärtust, mida soovite leida.

Kuigi neljas argument on valikuline, sisestab enamik inimestest selleks FALSE (või 0). Miks? Kuna see sunnib funktsiooni leidma otsitava põhjal täpse vaste. Võite argumendi tühjaks jätta või sisestada väärtuse TRUE, kuid kui täpset vastet ei leita, siis tagastab funktsioon sel juhul lähima ligikaudse vaste. Tavaliselt ei soovita aga ligikaudset vastet.

Ligikaudne vaste võib põhjustada probleeme. Oletame, et otsite näiteks ID-ga 2345768 osa hinda, kuid kaks numbrit läheb vahetusse ja tipite selle valemis valesti: =VLOOKUP(2345678;A1:E7;5). Valem tagastab vale osa hinna, kuna VLOOKUP leidis lähima numbri, mis on teie määratud numbrist (2345678) väiksem või sellega võrdne. Selle vea tõttu võite kliendile esitada vale arve.

Kui määrata argumendiks vastendustüüp FALSE või 0 ja täpset vastet pole, siis tagastab valem lahtris vale väärtuse asemel väärtuse #N/A. See on palju parem lahendus. Antud juhul ei tähenda #N/A, et valem oleks vale (v.a valesti sisestatud number). See tähendab, et väärtust 2345678 ei leitud – te soovisite 2345768.

See näide illustreerib funktsiooni tööd. Kui sisestate väärtuse lahtrisse B2 (esimene argument), otsib VLOOKUP seda lahtrivahemikust C2–E7 (teine argument) ja tagastab lähima ligikaudse vaste selle vahemiku kolmandast veerust ehk veerust E (kolmas argument).

Funktsiooni VLOOKUP tüüpiline kasutusviis

Selles näites jäeti neljas argument tühjaks, seega tagastab funktsioon ligikaudse vaste.

Funktsiooni HLOOKUP kasutamine

Kui olete funktsiooni VLOOKUP kasutamisega juba tuttav, siis on lihtne kasutada ka funktsioon HLOOKUP. Argumendid, mis tuleb sisestada, on samad, kuid HLOOKUP otsib väärtusi veergude asemel ridadest.

Funktsioonide INDEX ja MATCH koos kasutamine

Kui te ei soovi otsida ainult kõige vasakpoolsemast veerust, siis saate kasutada funktsioonide INDEX ja MATCH kombinatsiooni. Neid kahte funktsiooni kasutav valem on veidi keerulisem kui ainult funktsiooni VLOOKUP valem, kuid see võib olla võimsam ja on inimesi, kes eelistavad funktsiooni VLOOKUP asemel alati kasutada funktsioonide INDEX ja MATCH kombinatsiooni.

See näide sisaldab lühikest loendit, kus väärtus, mille järgi soovime otsida – Chicago, ei asu vasakpoolseimas veerus. Seega ei saa me kasutada VLOOKUP-i. Selle asemel kasutame Chicago otsimiseks vahemikust B1:B11 funktsiooni MATCH. See asub 4. real. Seejärel kasutab INDEX seda väärtust otsinguargumendina ja otsib Chicago elanike arvu 4. veerust (veerg D). Kasutatav valem on kuvatud lahtris A14.

Funktsioonide INDEX ja MATCH kasutamine väärtuse otsimiseks

Lisateavet otsingufunktsioonide kohta

Lehe algusesse

Täiendage oma oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×