Excel VLOOKUP -toiminto

VLOOKUP on Excel -toiminto, joka etsii pystysuunnassa järjestetyn taulukon tietoja. VLOOKUP tukee likimääräistä ja tarkkaa vastaavuutta ja yleismerkit (*?) osittaisissa otteluissa. Hakuarvojen on oltava ensimmäinen Taulukon sarake siirtyi VLOOKUP -hakuun.

• arvo - Arvo, jota etsitään taulukon ensimmäisestä sarakkeesta.
• pöytä - Taulukko, josta arvo haetaan.
• col_index - Taulukon sarake, josta haetaan arvo.
• range_lookup - [valinnainen] TOSI = likimääräinen vastaavuus (oletus). FALSE = tarkka vastaavuus.

VLOOKUP on Excel -toiminto, joka hakee tietoja taulukosta pystysuunnassa. Hakuarvojen on oltava ensimmäinen Taulukon sarake siirtyi VLOOKUP -hakuun. VLOOKUP tukee likimääräistä ja tarkkaa vastaavuutta ja yleismerkit (*?) osittaisissa otteluissa.

Pystysuuntaiset tiedot | Sarakkeiden numerot | Näyttää vain oikealta | Vastaavat tilat | Tarkka ottelu | Arvioitu ottelu | Ensimmäinen ottelu | Jokerimerkki | Kaksisuuntainen haku | Useita ehtoja | #N/A Virheet | Videot

V on pystysuora

VLOOKUPin tarkoitus on saada tietoa taulukosta, joka on järjestetty seuraavasti:

Käyttämällä sarakkeessa B olevaa tilausnumeroa hakuarvona VLOOKUP voi saada minkä tahansa tilauksen asiakastunnuksen, määrän, nimen ja tilan. Esimerkiksi saadaksesi asiakkaan nimen tilaukselle 1004 kaava on:

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

Vaakasuuntaisia ​​tietoja varten voit käyttää HLOOKUP , INDEKSI ja MATCH tai XLOOKUP .

miten luoda rivinvaihto Excelissä

VLOOKUP perustuu sarakkeiden numeroihin

Kun käytät VLOOKUPia, kuvittele, että jokainen sarake pöytä on numeroitu, alkaen vasemmalta. Jos haluat saada arvon tietystä sarakkeesta, anna sopiva numero sarakeindeksiksi. Esimerkiksi alla olevan etunimen hakeminen on 2:

Sukunimi ja sähköpostiosoite 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 katsoa vain oikealle. Haettavat tiedot (tulosarvot) voivat näkyä missä tahansa sarakkeessa oikealle hakuarvoista:

Jos haluat etsiä arvoja vasemmalta, katso INDEKSI ja MATCH tai XLOOKUP .

Tarkka ja likimääräinen vastaavuus

VLOOKUPissa on kaksi hakutilaa, tarkka ja likimääräinen. Vastaavuutta ohjaavan argumentin nimi on ' range_lookup ''. Tämä on hämmentävä nimi, koska sillä näyttää olevan jotain tekemistä solualueet kuten A1: A10. Itse asiassa sana 'alue' viittaa tässä tapauksessa 'arvoalueeseen' - milloin range_lookup on TOSI, VLOOKUP vastaa a arvoalue tarkan arvon sijaan. Hyvä esimerkki tästä on käyttö VLOOKUP arvosanojen laskemiseksi .

On tärkeää ymmärtää se range_lookup oletusarvo on TRUE , mikä tarkoittaa, että VLOOKUP käyttää oletuksena likimääräistä vastaavuutta, mikä voi olla vaarallista . Aseta range_lookup FALSE pakottaaksesi täsmällisen vastaavuuden:

 
= VLOOKUP (value, table, col_index) // approximate match (default) = VLOOKUP (value, table, col_index, TRUE) // approximate match = VLOOKUP (value, table, col_index, FALSE) // exact match

Huomautus: Voit myös antaa nolla (0) FALSE: n sijaan, jos haluat saada täsmällisen osuman.

Tarkka ottelu

Useimmissa tapauksissa haluat todennäköisesti käyttää VLOOKUPia tarkan haun tilassa. Tämä on järkevää, kun sinulla on ainutlaatuinen avain käytettäväksi hakuarvona, esimerkiksi elokuvan nimi näissä tiedoissa:

Kaava H6 löytää Vuosi , joka perustuu elokuvan nimen tarkkaan vastaavuuteen, on:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

Arvioitu ottelu

Tapauksissa, joissa haluat paras ottelu , ei välttämättä tarkka ottelu , haluat käyttää likimääräistä tilaa. Esimerkiksi alla haluamme etsiä provisioprosentin taulukosta G5: H10. Hakuarvot tulevat sarakkeesta C. Tässä esimerkissä meidän on käytettävä VLOOKUP in likimääräinen ottelu tilassa, koska useimmissa tapauksissa tarkkaa vastaavuutta ei koskaan löydy. D5: n VLOOKUP -kaava on määritetty suorittamaan likimääräinen vastaus asettamalla viimeisen argumentin arvoksi TOSI:

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

VLOOKUP etsii hakuarvon sarakkeen G arvoista. Jos tarkka vastaavuus löytyy, VLOOKUP käyttää sitä. Jos ei, VLOOKUP 'astuu taaksepäin' ja vastaa edellistä riviä.

Huomautus: tiedot on lajiteltava nousevaan järjestykseen hakuarvon mukaan, kun käytät likimääräistä hakutilaa VLOOKUP -toiminnon kanssa.

Ensimmäinen ottelu

Jos arvot ovat päällekkäisiä, VLOOKUP löytää ensimmäinen ottelu kun hakutila on tarkka. Alla olevassa näytössä VLOOKUP on määritetty etsimään vihreän värin hinta. On kolme merkintää, joiden väri on vihreä, ja VLOOKUP palauttaa ensimmäinen sisäänpääsy, 17 dollaria. Kaava solussa F5 on:

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

Jokerimerkki

VLOOKUP -toiminto tukee yleismerkit , joka mahdollistaa osittaisen haun suorittamisen hakuarvosta. Voit esimerkiksi hakea arvoja taulukosta VLOOKUPin avulla vain kirjoittamisen jälkeen osa hakuarvoa. Jos haluat käyttää jokerimerkkejä VLOOKUPin kanssa, sinun on määritettävä tarkan haun tila antamalla FALSE tai 0 viimeiselle argumentille, range_lookup . H7: n kaava hakee etunimen Michael, kun olet kirjoittanut Aya soluun H4:

 
= VLOOKUP ($H&'*',$B:$E4,2,FALSE)

Lue lisää yksityiskohtainen selitys täältä .

Kaksisuuntainen haku

VLOOKUP-toiminnon sisällä sarakehakemisto-argumentti on tavallisesti kovakoodattu staattiseksi numeroksi. Voit kuitenkin luoda myös dynaaminen sarakehakemisto käyttämällä MATCH -toimintoa oikean sarakkeen paikantamiseen. Tämän tekniikan avulla voit luoda dynaamisen kaksisuuntaisen haun, joka vastaa molempia rivejä ja sarakkeet. 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)

Lisätietoja, katso tämä esimerkki .

Huomautus: Yleensä INDEKSI ja MATCH on joustavampi tapa suorittaa kaksisuuntaisia ​​hakuja .

Useita kriteerejä

VLOOKUP -toiminto ei käsittele useita ehtoja luonnollisesti. Voit kuitenkin käyttää a auttajapylväs liittää useita kenttiä yhteen ja käyttää näitä kenttiä kuten useita ehtoja VLOOKUPissa. Alla olevassa esimerkissä sarake B on apusarake ketjutuksia 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)

Yksityiskohtia varten, katso tämä esimerkki .

Huomautus: INDEKSI ja MATCH ja XLOOKUP ovat tehokkaampia tapoja käsitellä hakuja useiden kriteerien perusteella.

VLOOKUP- ja #N/A -virheitä

Jos käytät VLOOKUPia, törmäät väistämättä #N/A -virheeseen. Virhe #N/A tarkoittaa vain 'ei löydy'. Esimerkiksi alla olevassa näytössä hakuarvoa 'Toy Story 2' ei ole hakutaulukossa, ja kaikki kolme VLOOKUP -kaavaa palauttavat numeron #N/A:

Yksi tapa 'sulkea' NA -virhe on käyttää IFNA -toiminto kuten tämä:

H6: n kaava on:

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

Viesti voidaan muokata halutulla tavalla. Jos haluat palauttaa mitään (eli 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

Virhe #N/A on hyödyllinen, koska se kertoo, että jokin on vialla. Käytännössä on monia syitä, miksi saatat nähdä tämän virheen, mukaan lukien:

• Hakuarvoa ei ole taulukossa
• Hakuarvo on kirjoitettu väärin tai sisältää ylimääräistä tilaa
• Vastaavuustila on tarkka, mutta sen pitäisi olla likimääräinen
• Taulukkoalue on syötetty väärin
• Kopioit VLOOKUPia ja taulukkoa viite ei ole lukittu

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

Lisätietoja VLOOKUPista

• Lisää VLOOKUP -esimerkkejä
• VLOOKUP -videoita
• 23 vinkkiä VLOOKUPin käyttöön

Muut muistiinpanot

• Alueen_näkymä ohjaa onko arvo täytyy täsmälleen vastata tai ei. Oletus on TRUE = salli ei-tarkka vastaavuus.
• Aseta range_lookup VÄÄRIN vaatia tarkka vastaavuus ja TOSI salli ei-tarkka vastaavuus .
• Jos range_lookup on TOSI (oletusasetus), epätarkka vastaavuus saa VLOOKUP-toiminnon vastaamaan taulukon lähimmän arvon vielä alle arvo .
• Kun range_lookup jätetään pois, VLOOKUP-toiminto sallii ei-tarkan haun, mutta se käyttää tarkkaa hakua, jos sellainen on olemassa.
• Jos range_lookup on TOSI (oletusasetus), varmista, että taulukon ensimmäisen rivin hakuarvot on lajiteltu nousevaan järjestykseen. Muussa tapauksessa VLOOKUP voi palauttaa väärän tai odottamattoman arvon.
• Jos range_lookup on EPÄTOSI (vaativat tarkan vastaavuuden), arvot ensimmäisessä sarakkeessa pöytä ei tarvitse lajitella.