Tässä opetusohjelmassa tarkastelemme, miten IFERROR- ja VLOOKUP-funktioita voidaan käyttää yhdessä erilaisten virheiden pyydystämiseen ja käsittelyyn. Lisäksi opit, miten Excelissä voidaan tehdä peräkkäisiä vlookuppeja pesimällä useita IFERROR-funktioita päällekkäin.

Excel VLOOKUP ja IFERROR – näitä kahta funktiota voi olla aika vaikea ymmärtää erikseen, saati sitten kun ne yhdistetään. Tästä artikkelista löydät muutamia helppotajuisia esimerkkejä, jotka käsittelevät yleisiä käyttökohteita ja havainnollistavat selkeästi kaavojen logiikkaa.

Jos sinulla ei ole paljon kokemusta IFERROR- ja VLOOKUP-funktioista, voi olla hyvä ajatus kerrata ensin niiden perusteet yllä olevien linkkien avulla.

• IFERROR VLOOKUP:n kanssa virheiden käsittelyyn
  • VLOOKUP ja palauta oma tekstisi N/A-virheen sijasta
  • VLOOKUP ja palauta tyhjä solu tai nolla, jos mitään ei löydy
• Nest IFERROR. VLOOKUPin sisällä, jotta aina löytyy jotain
• Seuraavat Vlookupit, joissa on sisäkkäisiä IFERROReja

IFERROR VLOOKUP-kaava #N/A:n ja muiden virheiden käsittelyyn

Kun Excelin Vlookup ei löydä hakuarvoa, se heittää #N/A-virheen, esimerkiksi näin:

Toiminnallisista tarpeistasi riippuen saatat haluta naamioida virheen omalla tekstillä, nollalla tai tyhjällä solulla.

Esimerkki 1. Iferror Vlookup-kaava kaikkien virheiden korvaamiseksi omalla tekstilläsi

Jos haluat korvata vakiovirheiden merkintätavan omalla tekstilläsi, kiedo VLOOKUP-kaavaan IFERROR ja kirjoita 2. argumenttiin (value_if_error) mikä tahansa haluamasi teksti, esimerkiksi ”Ei löytynyt”:

Kun hakuarvo on päätaulukon B2:ssa ja hakualue A2:B4 hakutaulukossa, kaava saa seuraavan muodon:

=IFERROR(VLOOKUP(B2,'Lookup table'!$A:$B, 2, FALSE), "Not found")

Alla olevassa kuvakaappauksessa näkyy Excelin IFERROR VLOOKUP-kaavamme toiminnassa:

Tulos näyttää paljon ymmärrettävämmältä ja paljon vähemmän pelottavalta, eikö vain?

Voit käyttää samalla tavalla INDEX MATCH -kaavaa yhdessä IFERROR-kaavan kanssa:

=IFERROR(INDEX('Lookup table'!$B:$B,MATCH(B2,'Lookup table'!$A:$A,0)), "Not found")

IFERROR INDEX MATCH -kaava on erityisen hyödyllinen silloin, kun haluat vetää arvot sarakkeesta, joka sijaitsee hakusarakkeen vasemmalla puolella (vasen haku), ja palauttaa oman tekstisi, kun mitään ei löydy.

Esimerkki 2. IFERROR VLOOKUPin kanssa palauttaa tyhjän tai 0:n, jos mitään ei löydy

Jos et halua näyttää mitään, kun hakuarvoa ei löydy, anna IFERRORin näyttää tyhjän merkkijonon (””):

Esimerkissämme kaava menee seuraavasti:

=IFERROR(VLOOKUP(B2,'Lookup table'!$A:$B, 2, FALSE), "")

Kuten näet, se ei palauta mitään, kun hakuarvoa ei ole hakulistassa.

Jos haluat korvata virheen nolla-arvolla, laita viimeiseen argumenttiin 0:

=IFERROR(VLOOKUP(B2,'Lookup table'!$A:$B, 2, FALSE), 0)

Varoituksen sana! Excelin IFERROR-funktio nappaa kaikenlaisia virheitä, ei vain #N/A. Onko se hyvä vai huono? Kaikki riippuu tavoitteestasi. Jos haluat peittää kaikki mahdolliset virheet, IFERROR Vlookup on oikea tapa toimia. Mutta se voi olla epäviisas tekniikka monissa tilanteissa.

Jos olet esimerkiksi luonut taulukkotiedoillesi nimetyn alueen ja kirjoittanut nimen väärin Vlookup-kaavassasi, IFERROR nappaa #NIMI? -virheen ja korvaa sen sanalla ”Ei löytynyt” tai muulla antamallasi tekstillä. Tämän seurauksena et ehkä koskaan tiedä, että kaavasi tuottaa vääriä tuloksia, ellet huomaa kirjoitusvirhettä itse. Tällaisessa tapauksessa järkevämpi lähestymistapa olisi vangita vain #N/A-virheet. Käytä tätä varten IFNA Vlookup-kaavaa Excel for Office 365:ssä, Excel 209:ssä, Excel 2016:ssa ja Excel 2013:ssa, IF ISNA VLOOKUP:ia kaikissa Excel-versioissa.

Lopputulos on: ole hyvin varovainen, kun valitset VLOOKUP-kaavasi kumppanin 🙂

Pesäytä IFERROR VLOOKUP:in sisällä, jotta löydät aina jotakin

Kuvittele seuraavanlainen tilanne: Etsit tiettyä arvoa tietyllä listalla, mutta et löydä sitä. Mitä vaihtoehtoja sinulla on? Joko saat N/A-virheen tai näytät oman viestisi. Itse asiassa on olemassa kolmaskin vaihtoehto – jos ensisijainen vlookup-arvosi kompastuu, etsi jotain muuta, joka on varmasti olemassa!

Viemme esimerkkiämme pidemmälle ja luomme käyttäjillemme jonkinlaisen kojelaudan, joka näyttää heille tietyn toimiston lisänumeron. Jotain tällaista:

Miten siis vedetään B-sarakkeesta D2:ssa olevan toimistonumeron perusteella toimistonumero? Tällä tavallisella Vlookup-kaavalla:

=VLOOKUP($D,$A:$B,2,FALSE)

Ja se toimii hienosti, kunhan käyttäjät syöttävät kelvollisen numeron D2:een. Mutta entä jos käyttäjä syöttää jonkin numeron, jota ei ole olemassa? Tässä tapauksessa anna heidän soittaa keskustoimistoon! Tätä varten upotat yllä olevan kaavan IFERRORin value-argumenttiin ja laitat toisen Vlookupin value_if_error-argumenttiin.

Kokonaiskaava on hieman pitkä, mutta se toimii täydellisesti:

=IFERROR(VLOOKUP("office "&$D,$A:$B,2,FALSE),VLOOKUP("central office",$A:$B,2,FALSE))

Jos toimistonumero löytyy, käyttäjä saa vastaavan lisänumeron:

Jos toimistonumeroa ei löydy, näytetään keskustoimiston lisänumero:

Tehdäksesi kaavasta hieman tiiviimmän voit käyttää erilaista lähestymistapaa:

Ensin tarkistetaan, onko D2:ssa oleva numero olemassa hakusarakkeessa (huomaa, että asetimme col_index_num arvoksi 1, jotta kaava hakee ja palauttaa arvon sarakkeesta A): VLOOKUP(D2,$A$2:$B$7,1,FALSE)

Jos määriteltyä toimistonumeroa ei löydy, etsitään merkkijonoa ”keskustoimisto”, joka on varmasti hakuluettelossa. Tätä varten kääritään ensimmäinen VLOOKUP IFERRORiin ja pesitään tämä koko yhdistelmä toisen VLOOKUP-funktion sisälle:

=VLOOKUP(IFERROR(VLOOKUP(D2,$A:$B,1,FALSE),"central office"),$A:$B,2)

Hieman erilainen kaava, sama lopputulos:

Mutta miksi etsitään ”keskustoimistoa”, voit kysyä minulta. Miksei IFERRORissa anneta suoraan numeronumeroa? Koska liittymänumero voi muuttua jossain vaiheessa tulevaisuudessa. Jos näin tapahtuu, sinun on päivitettävä tiedot vain kerran lähdetaulukkoon, eikä sinun tarvitse huolehtia jokaisen VLOOKUP-kaavasi päivittämisestä.

Jatkuvien VLOOKUPien tekeminen Excelissä

Tilanteissa, joissa sinun on suoritettava niin sanottuja peräkkäisiä tai ketjutettuja VLOOKUPeja Excelissä sen mukaan, onnistuiko tai epäonnistuiko edellinen haku, yhdistä kaksi tai useampia IFERROR-funktioita suorittaaksesi VLOOKUPit yksi kerrallaan:

Kaava toimii seuraavalla logiikalla:

Jos ensimmäinen VLOOKUP ei löydä mitään, ensimmäinen IFERROR-toiminto loukkaa virheen ja suorittaa toisen VLOOKUPin. Jos toinen VLOOKUP epäonnistuu, toinen IFERROR nappaa virheen ja suorittaa kolmannen VLOOKUPin ja niin edelleen. Jos kaikki Vlookupit kompastuvat, viimeinen IFERROR palauttaa viestisi.

Tämä sisäkkäinen IFERROR-kaava on erityisen hyödyllinen silloin, kun sinun on tehtävä Vlookupia useiden arkkien välillä, kuten alla olevassa esimerkissä on esitetty.

Esitettäköön, että sinulla on kolme luetteloa homogeenista dataa kolmella eri laskentataulukkolomakkeella (tässä esimerkissä toimistonumerot), ja haluat saada tietyn numeron lisäyksen.

Edellyttäen, että hakuarvo on nykyisen arkin solussa A2 ja hakualue on A2:B5 kolmessa eri työarkissa (pohjoinen, etelä ja länsi), seuraava kaava toimii hienosti:

=IFERROR(VLOOKUP(A2,North!$A:$B,2,FALSE), IFERROR(VLOOKUP(A2,South!$A:$B,2,FALSE), IFERROR(VLOOKUP(A2,West!$A:$B,2,FALSE),"Not found")))

Siten ”ketjutettu Vlookup”-kaavamme hakee kaikilla kolmella arkeilla siinä järjestyksessä, jossa olemme sijoittaneet ne kaavaan, ja tuo ensimmäisen löytämänsä vastaavuuden:

Näin käytät IFERROR:ia VLOOKUPin kanssa Excelissä. Kiitän lukemisesta ja toivottavasti tapaamme blogissamme ensi viikolla!

Saatavilla olevat lataukset

Excel IFERROR VLOOKUP kaavaesimerkkejä

Voit olla myös kiinnostunut

• Excel IFERROR toiminto kaavaesimerkkeineen
• Käyttämällä IF:ää VLOOKUP:n kanssa Excelissä
• Excelin VLOOKUP-opas aloittelijoille

.