VLOOKUP ja MATCH ne yhteen soppii

VLOOKUP ja MATCH ne yhteen soppii

VLOOKUP (PHAKU) on yksi Excelin tunnetuimmista funktioista, mutta silti se kirjoitetaan usein muodossa, joka edellyttää kaavan toistuvaa muokkaamista ja voi pahimmillaan johtaa vääriin tuloksiin.

Seuraava funktio hakee solussa B1 olevaa koodia A4:D8-alueelta ja poimii yksikköhinnan alueen neljännestä sarakkeesta antaen tulokseksi luvun 12. Funktiohan antaa täysin oikean tuloksen, joten onko siinä jotain vikaa?

VLOOKUP perinteisesti

Yllä oleva kaava on kieliopillisesti oikein ja se sopii mainiosti tilanteisiin, joissa lähdeaineistoon ei tule uusia sarakkeita eikä niitä poisteta.

Mutta entäpä aineistot, joiden rakenteeseen tulee muutoksia ja entä jos työkirjassa on kymmenittäin erilaisia VLOOKUP-kaavoja? Aineiston muuttuessa kaikki VLOOKUP-kaavat pitää korjata, jotta kaavat hakevat tietoja oikeista sarakkeista. Virheellisiä sarakenumeroita on myös joskus vaikea havaita, jos numeerisia sarakkeita on kymmenittäin.

Sarakenumeron sijaan voi käyttää MATCH (VASTINE) -funktiota, joka etsii otsikon perusteella sarakkeen sijainnin. Seuraava kaava etsii alueelta A4:D4 merkkijonoa ”yksikköhinta” ja antaa tuloksena sijainnin. Kolmas argumentti (koodi 0) määrittää, että funktio hakee täsmälleen samaa merkkijonoa.

=MATCH(”yksikköhinta”;  A4:D4;   0)

Kun MATCH sijoitetaan VLOOKUP-funktioon sarakenumeron tilalle, VLOOKUP hakee oikean tiedon vaikka Yksikköhinta-sarake siirtyisi kunhan se sijaitsee koodi-sarakkeen oikealla puolella.

=VLOOKUP(B1; A4:D8;  MATCH(”yksikköhinta”;  A4:D4;   0);  FALSE) tai

=VLOOKUP(B1; A4:D8;  MATCH(A2;  A4:D4;   0);  FALSE)

Tämä funktio antaa myös tarkoituksenmukaisesti virheilmoituksen, jos kyseistä sarakeotsikkoa ei löydy.

Jos lähdeaineisto on muutettu Data-nimikseksi taulukoksi, funktion voisi kirjoittaa muodossa:

=VLOOKUP(B1; Data; MATCH(A2; Data[#Headers]; 0); FALSE)

… ja huomenna viedään pussauskoppii … match made in heaven …

… jonain päivänä tulen vielä kirjoittamaan, miksi INDEX ja MATCH olisivat vielä sopivampi pari …

[Edit 10/2016: Pitkään kesti eli viimeinkin: https://hexcelligent.fi/2016/10/14/vlookupin-tilalle-index-ja-match/]

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out / Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out / Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out / Muuta )

Google+ photo

Olet kommentoimassa Google+ -tilin nimissä. Log Out / Muuta )

Muodostetaan yhteyttä palveluun %s