Excel-ohjeet

INDEX ja MATCH: Hakuyhdistelmä, joka toimii jokaisessa Excelissä

Kaksi pientä funktiota, jotka yhdessä löytävät minkä tahansa arvon listasta. Opi, miten ne toimivat ja milloin ne kannattaa valita XLOOKUPin sijaan.

Lukuaika noin 9 minuuttia

Kaksi funktiota, yksi tehtävä

Kuvittele, että pyydät kirjastonhoitajalta kirjaa. Kirjastonhoitaja tekee kaksi asiaa. Ensin hän katsoo luetteloa ja sanoo: "Se on hyllyssä numero 3." Sitten hän kävelee hyllylle ja noutaa kirjan.

INDEX ja MATCH toimivat samalla tavalla, ja niitä käytetään aina pariksi:

MATCH on luettelo. Se kertoo, missä jokin on listassa ("se on kolmas kohde").
INDEX on kirjastonhoitaja, joka kävelee hyllylle. Se antaa sinulle valitsemassasi kohdassa olevan kohteen ("anna minulle kolmas kohde").

Yhdellä lauseella: MATCH löytää etsimäsi asian sijainnin, ja INDEX käyttää sijaintia hakeakseen vastauksen toisesta sarakkeesta.

Yhdessä ne tekevät saman työn kuin XLOOKUP: löytävät jotakin yhdestä sarakkeesta ja tuovat vastaavan arvon toisesta. INDEX ja MATCH ovat vain olleet olemassa paljon pidempään, joten näet ne lukemattomissa taulukoissa, ja ne toimivat kaikissa Excel-versioissa.

Hyvä tietää: Suomenkielisessä Excelissä funktioiden nimet ovat INDEKSI (INDEX) ja VASTINE (MATCH). XLOOKUP on XHAKU ja IFERROR on JOSVIRHE. Tässä artikkelissa käytämme tekstissä englanninkielisiä nimiä, mutta kaavoihin kirjoitat ne nimet, joita oma Excelisi käyttää.

Onko INDEX:stä ja MATCH:stä hyötyä minulle?

Todennäköisesti, jos joskus tarvitset:

Hakea hinnan, nimen tai puhelinnumeron listasta
Tehdä sen vanhemmassa Excelissä, jossa ei ole XLOOKUPia
Jakaa työkirjan kollegoille, joilla voi olla vanhempi Excel
Ymmärtää tai korjata jonkun toisen tekemä taulukko

Viimeinen kohta on tärkeämpi kuin luulisi. Monet olemassa olevat taulukot käyttävät INDEXiä ja MATCHia, joten kaavan lukemisen taito on erittäin hyödyllinen.

Katso, miten se toimii

Käytämme samaa pientä hinnastoa kuin XLOOKUP-artikkelissamme. Sarakkeessa A ovat tuotteet ja sarakkeessa B hinnat.

	A	B	D	E
1	Tuote	Hinta	Hae	Hinta
2	Muistikirja	4,50	Pöytävalaisin	24,90
3	Kynäsetti	7,20
4	Pöytävalaisin	24,90
5	Nitoja	9,00

Soluun E2 kirjoitamme yhden kaavan. Excel etsii sanaa "Pöytävalaisin" (oranssi) sarakkeesta A, löytää sen (sininen) ja tuo hinnan sarakkeesta B (vihreä). Se tapahtuu kahdessa pienessä vaiheessa.

Mitä etsit Mistä se löytyi Mitä tuodaan takaisin

Vaihe 1: MATCH löytää sijainnin

MATCH etsii sanaasi sarakkeesta ja kertoo, missä kohdassa se on: ensimmäinen, toinen, kolmas ja niin edelleen.

=VASTINE( D2; A2:A5; 0 )

Tämä tarkoittaa: "Etsi solun D2 sana alueelta A2:A5 ja kerro sen sijainti." Listassamme "Pöytävalaisin" on kolmas kohde, joten vastaus on 3.

Mihin nolla on tarkoitettu? Se tarkoittaa "etsi täsmällinen osuma". Ota se aina mukaan. Jos jätät sen pois, Excel voi hiljaa arvata ja antaa väärän vastauksen.

Vaihe 2: INDEX tuo arvon

INDEX ottaa sarakkeen ja sijainnin ja antaa sinulle sen, mikä on siinä kohdassa.

=INDEKSI( B2:B5; 3 )

Tämä tarkoittaa: "Anna hintasarakkeesta B2:B5 kolmas kohde." Kolmas hinta on 24,90.

Huomaa, että INDEX ei etsi mitään. Se vain laskee alaspäin sarakkeessa. Siksi se tarvitsee MATCHin kertomaan sijainnin.

Yhdistetään ne

Sen sijaan että kirjoittaisimme numeron 3 itse, annamme MATCHin tuottaa sen. Sijoitamme MATCH-kaavan siihen, missä kolmonen oli:

=INDEKSI( B2:B5; VASTINE( D2; A2:A5; 0 ) )

Mitä tuon takaisin?

B2:B5 on sarake, jossa ovat hinnat. Tämä on INDEX-osa.

Mitä etsin?

D2 on solu, johon kirjoitit "Pöytävalaisin".

Mistä Excelin pitää etsiä?

A2:A5 on sarake, jossa ovat tuotteiden nimet. Lopussa oleva 0 tarkoittaa täsmällistä osumaa.

Excel työskentelee sisältä ulospäin: MATCH löytää luvun 3, ja sitten INDEX poimii kolmannen hinnan. Vaihda D2:ksi "Nitoja", niin hinta muuttuu itsestään arvoon 9,00.

Hyvä tietää: sarake, josta tuot arvon, kirjoitetaan ensin, ja sarake, josta etsit, tulee MATCHin sisään. Se tuntuu aluksi väärinpäin olevalta, mutta siihen tottuu.

Puolipiste vai pilkku? Se riippuu asuinpaikastasi

Jos internetistä kopioitu kaava antaa virheen, vaikka se näyttää täydelliseltä, syynä on usein erotinmerkki.

Tyyli	Erotinmerkki	Tyypilliset alueet	Esimerkki
Eurooppalainen	Puolipiste ;	Suomi, Ruotsi, Saksa, Ranska, Espanja	`=INDEKSI(B2:B5; VASTINE(D2; A2:A5; 0))`
Amerikkalainen	Pilkku ,	Yhdysvallat, Britannia, Kanada, Australia	`=INDEX(B2:B5, MATCH(D2, A2:A5, 0))`

Kaava tekee saman asian kummallakin tyylillä. Monissa Euroopan maissa pilkku on desimaalimerkki (2,5 eikä 2.5), joten Excel käyttää osien välillä puolipistettä. Jos saat virheen, vaihda jokainen pilkku puolipisteeksi tai päinvastoin. Huomaa myös, että internetin esimerkeissä on yleensä englanninkieliset funktioiden nimet kuten MATCH, kun suomenkielinen Excel vaatii nimen VASTINE. Tämän artikkelin esimerkeissä käytetään puolipisteitä.

Näin käytät sitä itse

Napsauta tyhjää solua, johon haluat vastauksen.
Kirjoita =INDEKSI( ja valitse sarake, jossa ovat vastaukset, jotka haluat tuoda takaisin. Kirjoita puolipiste.
Kirjoita VASTINE(, napsauta solua, jossa on se, mitä etsit, ja kirjoita puolipiste.
Valitse sarake, josta haluat etsiä, kirjoita puolipiste ja sitten 0.
Sulje molemmat sulut )) ja paina Enter.

Vinkki: Jos kaikki tuntuu liian paljolta kerralla, rakenna kaava kahdessa vaiheessa. Kirjoita ensin VASTINE-kaava vapaaseen soluun ja tarkista, että se antaa järkevän luvun. Kääri sitten INDEKSI sen ympärille.

Entä jos tuotetta ei ole listalla?

Jos etsit sanaa "Laskin" eikä sitä ole listalla, Excel näyttää #PUUTTUU!. Jos haluat näyttää ystävällisen viestin sen sijaan, kääri koko kaava JOSVIRHE-funktion (IFERROR) sisään:

=JOSVIRHE(INDEKSI(B2:B5; VASTINE(D2; A2:A5; 0)); "Ei löytynyt")

JOSVIRHE tarkoittaa "yritä tätä, ja jos tulee virhe, näytä tämä". Nyt näet virheen sijaan sanat Ei löytynyt.

INDEX ja MATCH vs. XLOOKUP

Molemmat tekevät saman työn, joten kumpaa kannattaa käyttää? Tässä vertailu:

	INDEX ja MATCH	XLOOKUP
Miten se kirjoitetaan	Kaksi funktiota, toinen toisen sisällä	Yksi funktio, jossa on kolme osaa
Helppous aloittelijalle	Vaikeampi, pitää ymmärtää kaksi vaihetta	Helpompi lukea ja kirjoittaa
Täsmällinen osuma	Sinun pitää lisätä 0 itse	Täsmällinen osuma on oletus
Tuotetta ei löydy	Tarvitsee JOSVIRHE-funktion ympärilleen	Siinä on valmis "ei löytynyt" -viesti
Katsominen vasemmalle	Toimii	Toimii
Sarakkeen lisääminen myöhemmin	Toimii edelleen oikein	Toimii edelleen oikein
Excel-versiot	Kaikki versiot	Microsoft 365 ja Excel 2021 tai uudempi
Vanhemmat taulukot	Hyvin yleinen, hyvä osata	Uudempi, löytyy uudemmista tiedostoista

Kumpaa siis käytän?

Käytä XLOOKUPia, jos sinulla on se ja olet tiedoston ainoa käyttäjä. Se on yksinkertaisempi ja sen kanssa on vaikeampi tehdä virheitä. Lue XLOOKUP-artikkelimme oppiaksesi sen.
Käytä INDEXiä ja MATCHia, jos jaat tiedoston ihmisille, joilla voi olla vanhempi Excel, tai jos sinun pitää ymmärtää taulukko, joka jo käyttää niitä.

Jos et ole varma, voit tarkistaa, onko Excelissäsi XLOOKUP, kirjoittamalla soluun =XHAKU(. Jos Excel ehdottaa sitä, sinulla on se.

Edistynyt

Haku riveiltä ja sarakkeilta

Tähän asti etsimme yhteen suuntaan: alaspäin sarakkeessa. Mutta joskus vastaus on kohdassa, jossa rivi ja sarake risteävät, esimerkiksi hintataulukossa, jossa tuotteet ovat vasemmalla ja kuukaudet ylhäällä. INDEX ja MATCH sopivat tähän erinomaisesti, koska INDEX voi ottaa sekä rivin että sarakkeen.

	A	B	C	D
1	Tuote	Tammi	Helmi	Maalis
2	Muistikirja	120	135	150
3	Kynäsetti	80	95	90
4	Pöytävalaisin	30	25	40
5	Nitoja	60	70	55

Montako kynäsettiä myimme helmikuussa? Tarvitsemme rivin "Kynäsetti" (sininen) ja sarakkeen "Helmi" (sininen) risteyskohdan. Vastaus on 95 (vihreä).

=INDEKSI( B2:D5; VASTINE("Kynäsetti"; A2:A5; 0); VASTINE("Helmi"; B1:D1; 0) )

Lue se kolmessa osassa:

B2:D5 on koko lukulohko.
Ensimmäinen VASTINE selvittää, millä rivillä "Kynäsetti" on: toisella.
Toinen VASTINE selvittää, missä sarakkeessa "Helmi" on: toisessa.

INDEX antaa sitten lohkon toisen rivin ja toisen sarakkeen luvun: 95. Käytännössä korvaisit sanat "Kynäsetti" ja "Helmi" soluilla, jotta voit muuttaa kysymystä koskematta kaavaan.

Yleisiä ongelmia ja niiden ratkaisut

Näet #PUUTTUU!

MATCH ei löytänyt etsimääsi. Tarkista kirjoitusasu ja etsi ylimääräisiä välilyöntejä tekstin alusta tai lopusta. "Pöytävalaisin " (välilyönnillä) ei ole sama asia kuin "Pöytävalaisin". Tai käytä JOSVIRHE-funktiota, kuten yllä näytettiin.

Saat väärän vastauksen ilman virhettä

Olet todennäköisesti jättänyt 0:n pois VASTINE-funktion lopusta. Ilman sitä Excel olettaa, että listasi on lajiteltu, ja arvaa, mikä antaa vääriä vastauksia listassa, jota ei ole lajiteltu. Päätä VASTINE aina merkintään ; 0).

Saat vastauksen väärältä riviltä

Alue, josta tuot arvon, ja alue, josta etsit, pitää alkaa samalta riviltä. Jos INDEX käyttää aluetta B2:B5 mutta VASTINE aluetta A3:A6, jokainen vastaus siirtyy yhden rivin.

Excel sanoo, että kaavassa on ongelma

Tarkista erotinmerkit. Jos Excelisi käyttää eurooppalaisia asetuksia, käytä puolipisteitä (;) pilkkujen (,) sijaan, kuten yllä selitettiin. Laske myös sulut: tämä kaava tarvitsee kaksi loppusulkua lopussa.

Näet #VIITTAUS!

INDEXiltä pyydettiin sijaintia alueen ulkopuolelta, esimerkiksi kuudetta kohtaa neljän kohdan listasta. Tarkista, että alueet ovat oikean kokoisia.

Luvut näyttävät oikeilta, mutta mikään ei täsmää

Joskus luku on tallennettu tekstinä, jolloin "1001" ja 1001 näyttävät samalta, mutta Excel käsittelee ne eri asioina. Jos solussa on pieni vihreä kolmio tai luku on tasattu vasemmalle, muunna se oikeaksi luvuksi.

Lyhyt versio

MATCH löytää jonkin asian sijainnin listasta. INDEX tuo kohteen tietystä sijainnista. Yhdessä ne hakevat arvoja.
Kaava on =INDEKSI(tuo tämä; VASTINE(etsi tämä; täältä; 0)).
Päätä VASTINE aina merkintään 0 täsmällistä osumaa varten.
XLOOKUP on yksinkertaisempi. INDEX ja MATCH toimivat jokaisessa Excel-versiossa ja ovat kaikkialla vanhoissa tiedostoissa.
Kääri kaava JOSVIRHE-funktioon saadaksesi ystävällisen "Ei löytynyt" -viestin.
Käytä puolipisteitä eurooppalaisessa Excelissä ja pilkkuja amerikkalaisessa Excelissä.