Microsoft julkaisi hiljattain uuden funktion, joka hyvin pitkälti korvaa aikaisemman VLOOKUPin ja INDEX MATCHin.
Kaikessa yksinkertaisuudessaan kaava kirjoitetaan näin:
=XLOOKUP( haettava sana ; alue, josta haettava sana löytyy ; alue, josta haluat tuoda arvon )
Eli kirjoita kaava näin:
Mene soluun johon haluat kaavan.
Kirjoita sen jälkeen XLOOKUP (tai suomeksi XHAKU).
Seuraavaksi kirjoita sulun jälkeen ensimmäiseksi taulukkoja yhdistävä tekijä, tässä tapauksessa B3, josta löytyy tuotekoodi.
Sen jälkeen kirjoita puolipiste ja valitse vaikka hiirellä alue, josta tuotekoodin pitäisi löytyä, tässä tapauksessa E2:E4.
Lopuksi valitse se alue, josta haluat palauttaa arvon (siihen soluun, johon olet kirjoittamassa kaavaa). Tässä tapauksessa se on F2:F4.
Näin helppoa se on! Käydään seuraavaksi läpi hieman syvemmin läpi, mitä konepellin alla tapahtuu.
1: Alkutilanne
Tässä esimerkissä on vasemmalla taulukko, josta tahdomme hakea arvoja oikeanpuoleiseen taulukkoon.
2: XLOOKUP (tai suomeksi XHAKU)
Kirjoitetaan soluun XLOOKUP ja syötetään sille arvot:
- lookup_value
- lookup_array
- return_array
Lookup_value on tässä esimerkissä ensimmäisellä rivillä ”Anna”. Voimme tietenkin käyttää myös soluviittausta G2.
Lookup_array on alue, josta ”Anna” pitäisi löytyä. Syötämme siihen alueen, josta arvon pitäisi löytyä. Tässä tapauksessa arvot löytyvät alueelta B3:B7.
Kaavan seuraavaan kohtaan pääset kirjoittamalla ;-merkin.
Ammattilaisen vinkki eli pro tip!
Jos valitset alueen rivinumeron kanssa, sinun täytyy lukita alue F4-näppäintä painamalla, jolloin alue lukittuu. Tunnistat lukitun alueen dollarimerkeistä kaava-alueella.Voit kuitenkin useimmissa tapauksissa valita koko sarakkeen kaavaan, eli napauttaa B-sarakkeen kohdalta. Silloin kaava etsii koko sarakkeesta kaikilta riveiltä, mutta tämä ei kuitenkaan vaikuta suorituskykyyn. Pidä kuitenkin huoli, että taulun ala- tai yläpuolella ei ole mitään.
return_array on alue, josta arvo palautetaan. Tässä tapauksessa arvot on alueella C3:C7. Muista, että jos päätät käyttää koko sarakkeen hakua, niin sitä täytyy käyttää kaavan molemmissa kohdissa.
Lopullinen kaava näyttää siis tältä:
=XLOOKUP(G3;B:B;C:C)
Lisävalinnat
Nämä kohdat ovat valinnaisia, mutta joissain tapauksissa helpottavat työntekoa paljon.
[if_not_found]: Tähän kohtaan voit kirjoittaa tekstin, jonka kaava palauttaa jos vastaavuutta ei löydy. Eli esimerkiksi ”Arvoa ei löytynyt”
[match_mode]: Tähän voit valita arvot
- 0: Exact match. Oletusarvo, vaatii täydellistä vastaavuutta kenttien välillä.
- 1: Exact match or next smaller. Palauttaa täydellisen vastaavuuden tai siitä yhden pienemmän
- -1: Exact match or next larger. Sama kuin edellinen, mutta palauttaa suuremman arvon jos täydellistä vastaavuutta ei löydy.
- 2: Wildcard match. Jos asetat tähän kohtaan numeron kaksi, voit käyttää Excelin ”villejä kortteja”, eli ?, * ja ~ lookup_valuessa. Eli esimerkiksi ”*nna” palauttaa nyt tuloksen, joka oletusarvoisesti ei olisi toiminut.
[search_mode]: Tässä voit vaikuttaa siihen, missä järjestyksessä hakutaulukkoa ruvetaan käymään läpi. Mahdolliset arvot ovat.
- 1: Oletusarvo. Aloittaa ensimmäisestä rivistä.
- -1: Aloittaa viimeisestä rivistä.
- 2: Binäärihaku ensimmäisestä rivistä. Binäärihaku on erittäin nopea keino hakea tietoa, mutta vaatii että taulukko on järjestetty suuruusjärjestykseen.
- -2: Binäärihaku käänteisessä järjestyksessä.
Loppusanat
XLOOKUP on kauan odotettu lisäys Excelin funktioiden joukkoon, joka poistaa kaiken sähläämisen, joka VLOOKUPiin ja INDEX MATCHiin liittyi. Ensikokemusten jälkeen toimivuuskin vaikuttaa erittäin hyvältä, joten voin suositella korvaamaan tällä jatkossa kaikki VLOOKUPit ja INDEX MATCHit, kuitenkin sillä lisäyksellä, että XLOOKUPia ei kuitenkaan pysty käyttämään vanhoissa Excelin versioissa.
Oletko jo kokeillut XLOOKUPIa? Kerro kokemuksista!
Hei Excel-Guru!
Minulla on satoja ID:itä ja jokaiselle ID:lle sata aikapistettä muodossa pv.kk.vvvv tt:mm.
Voinko käyttää XHAKUa etsiäkseni tietyn aikapisteen tietylle ID:lle, eli käyttää kahden kriteerin hakua?
Kiitos avusta jo etukäteen.
Hei, laitoin sähköpostia!
Hei. Mulla on isompi ongelma.
2 tiedostoa.
Haku kriiteri on ”kiinteistötunnus numeroinen” monta kerta toistuva. Molemmissa tiedostossa saattaisi olla eri määrä samoja kiinteistötunnuksia.
Toiselta taululta pitäisi hakea X ja Y koordinaatteja.
Onnistuin tekemän sen mutta se haki vain ensimmäisen X tai Y sopivalle kiinteistötunnukselle.
Ongelma on, että paitsi tätä kiinteistötunnusta ei ole mitään muuta sama ominaisuutta.
Kiitos avusta jo etukäteen
Terve! Löytyykö Office 365 (Teamsin kautta käytetty selainversio) fuzzy lookup:ia tai vastaavaa? Tarkoitus mäpätä tekstikenttiä keskenään, jotka poikkeavat hieman toisistaan. Xlookupin wildcard ei ihan riitä ja ainakin vuosia sitten kun kokeilin niin tuo fuzzy lookup ajoi asian.
On erittän kätevä, jo senkin takia, että voi valita alueeksi koko sarakkeen A:A-muodossa.
Kuitenkin tuli eteen tilanne, jossa funktio ei anna oikeaa tulosta! Yritin jopa poistaa taulun ja tehdä uuden: ei muutosta. Paristakymmenestä taulusta kahdessa vika ilmeni. Miten asi olisi korjattavissa.
Yhtä harmittava kuin se, jossa kirjoitetun funktion paikallee tulee vain teksti (siis ilman =-merkkiä)
Kiitos selkeästä ohjeesta. XLOOKUP on osoittautunut erittäin toimivaksi datojen yhdistelemisessä.