Excel-klassikko VLOOKUP on saanut seuraajan: XLOOKUP

Excel-klassikko VLOOKUP on saanut seuraajan: XLOOKUP

PHAKU (engl. VLOOKUP) -funktio tuntuu yhä olevan koulutuksissa yksi kysytyimpiä aiheita. Tämän 35-vuotiaan, ikoniksikin kutsutun funktion käyttö on yleistä, vaikka Microsoft on jo julkaissutkin sille jo seuraajankin. Syksyllä 2019 julkaistu XHAKU -funktio (engl. XLOOKUP) on nyt saatavilla Office 365-käyttäjille ja tämä funktio toimii hakutilanteissa ketterämmin.

Miten PHAKU toimii?

PHAKU-funktion avulla voidaan hakea taulukosta haluttu tieto, kunhan sille kerrotaan:

  1. hakuarvo, jonka avulla tietoa haetaan,
  2. hakutaulukko, josta löytyy edellä mainittu hakuarvo ja sen perusteella vastaukseksi haluttu tieto,
  3. hakutaulukon sarakenumero, jossa oleva tieto halutaan vastaukseksi, ja
  4. hakutapa, eli haetaanko hakuarvon tarkkaa vastinetta vai ei.

Alla olevassa Tilaukset-taulukossa on tilausrivejä, joissa näkyvissä pelkästään asiakasnumero. Tilausriville halutaan näkyviin myös asiakkaan nimi, joka löytyy Asiakkaat-taulukosta. Molemmista taulukoista löytyy yhdistämiseen soveltuva, yksilöity asiakasnumero. Asiakkaat-taulukossa asiakasnumero sijaitsee alueen vasemmanpuoleisimmassa sarakkeessa.

  1. Valitaan solu B4, jossa olevan asiakasnumeron perusteella haetaan yrityksen nimeä.
  2. Valitaan alue F4:G21. Hakuarvo sijaitsee sarakkeessa F ja palautettava vastaus löytyy sarakkeesta G. Koska kaava tullaan kopioimaan alapuolisille riveille, käytetään hakutaulukossa suoria soluviittauksia ($F$4:$G$21).
  3. Yrityksen nimi löytyy hakutaulukon toisesta sarakkeesta, joten seuraavaksi funktiolle annetaan sarakkeen järjestysnumero vasemmalta lukien eli numero 2.
  4. Hakutaulukosta haetaan tarkkaa vastaavuutta hakuarvolle 1905, joten hakutavaksi valitaan EPÄTOSI (FALSE) tai käytetään numeroa nolla (0). Jos hakutaulukosta ei löydy arvoa 1905 eli täsmällistä vastinetta, funktio palauttaa virhearvon #PUUTTUU! (#N/A!).

Jos haettaisiin tarkkaa tai pienempää arvoa, hakutavaksi määritettäisiin TOSI (TRUE) ja hakutaulukon tulisi olla nousevasti lajiteltu.

Muistettavaa PHAKU-funktion käytössä

PHAKU-funktio hakee tietoa ainoastaan oikealta. Toisin sanoen se tieto, jota taulukosta haetaan vastaukseksi, on oltava hakuarvon oikealla puolella. Raporteissa näin ei aina ole ja usein joudutaan tilanteeseen, jossa  PHAKU-funktiota ei voikaan käyttää. Tällöin on turvauduttava INDEKSI ja VASTINE -funktioiden yhdistelmään (engl. INDEX ja MATCH). Sisäkkäisten funktioiden käyttö tekee laskennasta monimutkaisempaa ja virheiden mahdollisuus kasvaa.

Palautettavan tiedon sarake määritetään numeroarvolla ja tämä saattaa aiheuttaa ylimääräisiä päivitystarpeita. Jos hakutaulukkoon lisätään tai siitä poistetaan sarakkeita myöhemmin, ja poimittava tieto siirtyy toiseen sarakkeeseen, on kaikkiin PHAKU-funktioihin korjattava uusi sarakkeen järjestysnumero.

XHAKU-funktio

XHAKU (engl. XLOOKUP) -funktio kampittaa edeltäjänsä rajoitteita, sillä se päivittyy sarakkeita lisättäessä tai poistettaessa ja hakee tietoa myös vasemmalta. XHAKU-funktiota voi käyttää myös niissä tilanteissa, joissa aiemmin tuli käyttää INDEKSI/VASTINE-funktioiden yhdistelmää tai VHAKU (engl. HLOOKUP) -funktiota.

Miten XHAKU toimii?

Edellä olevassa yhdistelyesimerkissä riittää, että XHAKU -funktiolle kerrotaan ainoastaan:

  • hakuarvo, jonka avulla tietoa haetaan,
  • hakumatriisi eli rivi tai sarake, josta löytyy edellä mainittu hakuarvo
  • palautusmatriisi eli rivi, sarake tai alue, josta löytyy vastaukseksi haluttu tieto.

Haluamme siis yrityksen nimen Tilaukset-taulukon tilausriville.

  1. Valitaan solu B4, jossa olevan asiakasnumeron 1905 perusteella haetaan yrityksen nimeä.

2. Toisin kuin edeltäjässään, XHAKU-funktiossa valitaan hakumatriisi eli se hakutaulukon sarake, josta hakuarvo löytyy, tässä siis alue H4:H21. Tällä kertaa hakutaulukossa oleva asiakasnumero on hakutaulukon oikeanpuoleisimmassa sarakkeessa. Koska kaava tullaan kopioimaan alapuolisille riveille, käytetään hakumatriisissa suoria soluviittauksia ($H$4:$H$21).

3. Lopuksi valitaan palautusmatriisi eli se hakutaulukon sarake, josta löytyy yrityksen nimi eli palautettava vastaustieto, tässä alue F4:F21. Koska kaava tullaan kopioimaan alapuolisille riveille, käytetään palautusmatriisissa suoria soluviittauksia ($F$4:$F$21).

XHAKU-funktio hakee aina oletusarvoisesti tarkkaa vastinetta.

XHAKU-funktion hyödylliset lisätoiminnot

XHAKU-funktiolla on myös valinnaisia argumentteja, joita voi hyödyntää.

  • Voi syöttää haluamasi tiedon Jos_ei_löydy -argumentiksi. Mikäli tietoa ei määritetä, funktio palauttaa arvon #PUUTTUU! (#N/A!). Voit esimerkiksi syöttää tähän kenttään kaksi peräkkäistä lainausmerkkiä (“”), jolloin XHAKU-funktio jättää tyhjäksi ne solut, joihin haun perusteella tulisi virheilmoitus.
  • XHAKU-funktion Vastaavuustila-argumentilla voidaan hakea myös tarkkaa tai pienempää arvoa (-1) tai tarkkaa tai suurempaa arvoa (1). Jos haetaan tarkkaa vastinetta, vastaavuustila-argumentin voi funktiossa jättää tyhjäksi.
  • Hakutila-argumentin avulla voidaan määritellä, haetaanko ensimmäinen tulos ylhäältä (1) vai alhaalta (-1) alhaalta). Voimme siten hakea esimerkiksi viimeisimmän tiedon listasta, jonne olemme syöttäneet ajoneuvojen kilometrit kuukausittain.
  • Ja vielä tämäkin: XHAKU-funktio osaa palauttaa yksittäisen arvon sijaan usean sarakkeen ja/tai rivin.

Opi lisää