Excel VLOOKUP -funktio

VLOOKUP on Excel-funktio, jolla saadaan tietoja pystysuoraan järjestetystä taulukosta. Haun arvojen on oltava VLOOKUP: iin välitetyn taulukon ensimmäisessä sarakkeessa. VLOOKUP tukee likimääräistä ja tarkkaa hakua ja jokerimerkkejä (*?) Osittaisille otteluille.

Pystytiedot | Sarakkeiden numerot | Näyttää vain oikealta | Vastaavat tilat | Tarkka ottelu | Arvioitu ottelu | Ensimmäinen ottelu | Jokerimerkki | Kaksisuuntainen haku | Useita ehtoja # Ei sovelleta virheitä Videot

V on tarkoitettu pystysuoraan

VLOOKUPin tarkoituksena on saada tietoja seuraavasta taulukosta:

Käyttäen sarakkeen B tilausnumeroa hakuarvona VLOOKUP voi saada minkä tahansa tilauksen asiakastunnuksen, määrän, nimen ja tilan. Esimerkiksi tilauksen 1004 asiakkaan nimen saamiseksi kaava on:

=VLOOKUP(1004,B5:F9,4,FALSE) // returns "Sue Martin"

 Vaakasuoraan dataan voit käyttää HLOOKUP, INDEX ja MATCH tai XLOOKUP. 
 VLOOKUP perustuu sarakkeiden numeroihin 
 Kun käytät VLOOKUPia, kuvittele, että taulukon jokainen sarake on numeroitu vasemmalta alkaen. Saadaksesi arvon tietystä sarakkeesta, anna sopiva numero sarakeindeksinä. Esimerkiksi alla olevan etunimen hakemiseen tarkoitettu sarakeindeksi on 2: 
  
 Sukunimi ja sähköposti voidaan hakea sarakkeilla 3 ja 4: 

=VLOOKUP(H3,B4:E13,2,FALSE) // first name=VLOOKUP(H3,B4:E13,3,FALSE) // last name=VLOOKUP(H3,B4:E13,4,FALSE) // email address

 VLOOKUP näyttää vain oikealta 
 VLOOKUP voi näyttää vain oikealle. Haettavat tiedot (tulosarvot) voivat näkyä missä tahansa sarakkeessa hakuarvojen oikealla puolella: 
  
 Jos sinun on haettava arvoja vasemmalta, katso HAKEMISTO ja MATCH tai XLOOKUP. 
 Tarkka ja likimääräinen sovitus 
 VLOOKUPilla on kaksi sovitustilaa, tarkka ja likimääräinen. Hakua hallitsevan argumentin nimi on ”range_lookup”. Tämä on sekava nimi, koska sillä näyttää olevan jotain tekemistä solualueiden kanssa, kuten A1: A10. Itse asiassa sana ”alue” viittaa tässä tapauksessa ”arvojen alueeseen” – kun alue_haku on TOSI, VLOOKUP vastaa arvoaluetta tarkan arvon sijasta. Hyvä esimerkki tästä on VLOOKUPin käyttäminen arvosanojen laskemiseen. 
 On tärkeää ymmärtää, että range_lookup on oletusarvoisesti TOSI, mikä tarkoittaa, että VLOOKUP käyttää oletuksena likimääräistä hakua, mikä voi olla vaarallista. Aseta range_lookup arvoksi FALSE pakottaaksesi tarkan haun: 
 Huomaa: Voit myös antaa nollan (0) arvon FALSE sijasta tarkalle haulle. 
 Tarkka haku 
 Useimmissa tapauksissa haluat todennäköisesti käyttää VLOOKUPia tarkan haun tilassa. Tämä on järkevää, kun sinulla on yksilöllinen avain, jota haluat käyttää hakuarvona, esimerkiksi elokuvan nimi näissä tiedoissa: 

  
 H6: n kaava löytää vuosi, joka perustuu elokuvan nimen täsmälliseen vastaavuuteen, on: 
=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact match

 Likimääräinen vastaavuus 
 Jos haluat parhaan vastaavuuden, ei välttämättä tarkan vastaavuuden, haluat käyttää likimääräistä tilaa. Esimerkiksi alla haluamme etsiä palkkiotasoa taulukosta G5: H10. Hakuhinnat tulevat sarakkeesta C. Tässä esimerkissä meidän on käytettävä VLOOKUPia likimääräisessä vastaavuustilassa, koska useimmissa tapauksissa tarkkaa vastaavuutta ei koskaan löydetä. D5: n VLOOKUP-kaava on määritetty suorittamaan likimääräinen ottelu asettamalla viimeiseksi argumentiksi TOSI: 
  

=VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = approximate match

 VLOOKUP skannaa sarakkeen G arvot hakuarvolle. Jos tarkka haku löytyy, VLOOKUP käyttää sitä. Jos ei, VLOOKUP ”palaa” ja vastaa edellistä riviä. 
 Huomaa: tiedot on lajiteltava nousevassa järjestyksessä hakuarvon mukaan, kun käytät likimääräistä vastaavuustilaa VLOOKUPin kanssa. 
 Ensimmäinen ottelu 
 Jos kyseessä on päällekkäinen arvo, VLOOKUP löytää ensimmäisen vastaavuuden, kun vastaavuustila on tarkka. Alla olevassa näytössä VLOOKUP on määritetty etsimään vihreän värin hinta. Vihreällä on kolme merkintää, ja VLOOKUP palauttaa ensimmäisen merkinnän hinnan, 17 dollaria. Kaavan solussa F5 on: 

=VLOOKUP(E5,B5:C11,2,FALSE) // returns 17

  
 Jokerimerkkien ottelu 
 VLOOKUP-toiminto tukee jokerimerkkejä, mikä mahdollistaa osittaisen haun suorittamisen hakuarvolle. Voit esimerkiksi hakea arvoja taulukosta VLOOKUPin avulla, kun olet kirjoittanut vain osan hakuarvosta. Jos haluat käyttää jokerimerkkejä VLOOKUPin kanssa, sinun on määritettävä tarkka hakutila antamalla FALSE tai 0 viimeiselle argumentille range_lookup. H7: n kaava hakee etunimen ”Michael” kirjoitettuaan ”Aya” soluun H4: 

=VLOOKUP($H$4&"*",$B$5:$E$104,2,FALSE)

  
 Lue tarkempi selitys täältä. 
 Kaksisuuntainen haku 
 VLOOKUP-toiminnon sisällä oleva sarake index-argumentti on yleensä kovakoodattu staattisena numerona.Voit kuitenkin luoda myös dynaamisen sarakeindeksin MATCH-toiminnon avulla oikean sarakkeen löytämiseksi. Tämän tekniikan avulla voit luoda dynaamisen kaksisuuntaisen haun, joka vastaa sekä rivejä että sarakkeita. Alla olevassa näytössä VLOOKUP on määritetty suorittamaan haku nimen ja kuukauden perusteella. H6: n kaava on: 

=VLOOKUP(H4,B5:E13,MATCH(H5,B4:E4,0),0)

  
 Katso lisätietoja tästä esimerkistä. 
 Huomaa: Yleensä INDEX ja MATCH ovat joustavampi tapa suorittaa kaksisuuntaisia hakuja. 

 Useita ehtoja 
 VLOOKUP-toiminto ei käsittele useita ehtoja natiivisti. Voit kuitenkin yhdistää useita kenttiä auttajasarakkeella ja käyttää näitä kenttiä kuten useita ehtoja VLOOKUPin sisällä. Alla olevassa esimerkissä sarake B on apusarake, joka yhdistää etu- ja sukunimet yhdessä tämän kaavan kanssa: 

=C5&D5 // helper column

 VLOOKUP on määritetty tekemään sama asia hakuarvon luomiseksi. H6: n kaava on: 

=VLOOKUP(H4&H5,B5:E13,4,0)

  
 Katso lisätietoja tästä esimerkistä. 
 Huomaa: INDEX ja MATCH ja XLOOKUP ovat tehokkaampia tapoja käsitellä hakuja useiden ehtojen perusteella. 
 VLOOKUP ja # N / A -virheet 
 Jos käytät VLOOKUPia, kohtaat väistämättä # N / A -virheen. # N / A -virhe tarkoittaa vain ”ei löydy”. Esimerkiksi alla olevassa näytössä hakuarvoa ”Toy Story 2” ei ole hakutaulukossa, ja kaikki kolme VLOOKUP-kaavaa palauttavat # N / A: 
  
 Yksi tapa ”ansaita” NA-virhe on käyttää IFNA-toimintoa näin: 
  
 H6: n kaava on: 

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"Not found")

 Viesti voidaan mukauttaa haluamallasi tavalla. Jos et palauta mitään (ts. Näyttää tyhjän tuloksen), kun VLOOKUP palauttaa # N / A, voit käyttää tyhjää merkkijonoa seuraavasti: 

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"") // no message

 # N / A-virhe on hyödyllinen, koska se kertoo sinulle jotain vikaa. Käytännössä on monia syitä, miksi saatat nähdä tämän virheen, mukaan lukien: 

 Hakearvoa ei ole taulukossa 
 Hakuarvo on kirjoitettu väärin tai sisältää lisätila 
 Hakutila on tarkka, mutta sen on oltava likimääräinen 
 Taulukkoaluetta ei ole syötetty oikein 
 Kopioit VLOOKUP-tiedostoa eikä taulukon viite ole lukittu 

 Lue lisää: VLOOKUP ilman # N / A-virheitä 
 Lisätietoja VLOOKUPista 

 Lisää VLOOKUP-esimerkkejä 
 VLOOKUP-videot 
 23 vinkkiä VLOOKUP-sovelluksen käyttöön 

 Muut huomautukset 

 Range_lookup määrittää, onko arvon oltava täsmälleen vai ei. Oletusarvo on TOSI = salli epätarkka haku. 
 Määritä range_lookup-arvoksi FALSE, jotta se vaatii tarkan haun, ja TRUE, jos haluat sallia ei-tarkan haun. 
 Jos range_lookup on TOSI oletusasetus), epätarkka haku saa VLOOKUP-funktion vastaamaan lähintä arvoa taulukossa, joka on edelleen pienempi kuin arvo. 
 Kun range_lookup jätetään pois, VLOOKUP-toiminto sallii ei-tarkan ottelu, mutta se käyttää tarkkaa vastaavuutta, jos sellainen on. 
 Jos range_lookup on TOSI (oletusasetus), varmista, että taulukon ensimmäisen rivin hakuarvot on lajiteltu nousevassa järjestyksessä. Muussa tapauksessa VLOOKUP saattaa palauttaa väärän tai odottamattoman arvon. 
 Jos range_lookup on EPÄTOSI (vaatii tarkan vastaavuuden), taulukon ensimmäisen sarakkeen arvoja ei tarvitse lajitella.