Kuinka käyttää INDEX- ja MATCH-funktioita Excelissä?

Mitä tietää

• INDEX-funktiota voidaan käyttää yksinään, mutta MATCH-funktion sisällyttäminen siihen luo tarkennetun haun.
• Tämä sisäkkäinen funktio on joustavampi kuin VLOOKUP ja voi palauttaa tulokset nopeammin.

Tässä artikkelissa kerrotaan, kuinka INDEX- ja MATCH-funktioita käytetään yhdessä kaikissa Excelin versioissa, mukaan lukien Excel 2019 ja Microsoft 365.

Mitä ovat INDEX- ja MATCH-funktiot?

INDEX ja MATCH ovat Excelin hakutoimintoja. Vaikka ne ovat kaksi täysin erillistä toimintoa, joita voidaan käyttää erikseen, ne voidaan myös yhdistää kehittyneiden kaavojen luomiseksi. INDEKSI-funktio palauttaa arvon tai viittauksen arvoon tietystä valinnasta. Sitä voidaan käyttää esimerkiksi arvon etsimiseen tietojoukon toiselta riviltä tai viidenneltä riviltä ja kolmannelta sarakkeelta. Vaikka INDEXiä voidaan käyttää erittäin hyvin yksinään, MATCHin lisääminen kaavaan tekee siitä hieman hyödyllisemmän. MATCH-funktio etsii tietyn kohteen solualueelta ja palauttaa sitten kohteen suhteellisen sijainnin alueella. Sitä voidaan käyttää esimerkiksi määrittämään, että tietty nimi on nimiluettelon kolmas kohta.

INDEX ja MATCH syntaksi ja argumentit

Näin molemmat funktiot tulee kirjoittaa, jotta Excel ymmärtää:

=INDEKSI†sarja† rivin_numero† [column_num]†

• sarja on solualue, jota kaava käyttää. Tämä voi olla yksi tai useampi rivi ja sarake, kuten A1:D5. Sitä tarvitaan.
• rivin_numero on taulukon rivi, josta palautetaan arvo, kuten 2 tai 18. Se vaaditaan, ellei sarakkeen_numero on läsnä.
• sarakkeen_numero on taulukon sarake, josta palautetaan arvo, kuten 1 tai 9. Se on valinnainen.

= MATCH†hakuarvo† lookup_array† [match_type]†

• hakuarvo onko arvo, jonka haluat vastata? lookup_array† Se voi olla numero, teksti tai looginen arvo, joka on kirjoitettu manuaalisesti tai johon viitataan soluviittauksella. Tämä on pakollinen.
• lookup_array on solualue, jonka läpi katsotaan. Se voi olla yksi rivi tai yksi sarake, kuten A2:D2 tai G1:G45. Tämä on pakollinen.
• match_type voisi olla -1† 0tai 1† Siinä määritellään, miten: hakuarvo vastaa arvoja lookup_array (Katso alempaa). 1 on oletusarvo, jos tämä argumentti jätetään pois.

Käyttö 1 tai -1 aikoina, jolloin sinun on suoritettava likiarvo asteikkoa pitkin, kuten käsiteltäessä numeroita ja kun likiarvot ovat kunnossa. Mutta muista, jos et määritä match_type† 1 on oletusarvo, mikä voi vääristää tuloksia, jos todella haluat tarkan vastaavuuden.

Esimerkki INDEX- ja MATCH-kaavat

Ennen kuin tarkastelemme kuinka yhdistää INDEX ja MATCH yhdeksi kaavaksi, ymmärrämme, kuinka nämä funktiot toimivat yksinään.

INDEX Esimerkkejä

=INDEKSI(A1:B2;2;2)
=INDEKSI(A1:B1,1)
=INDEKSI(2:2,1)
=INDEKSI(B1:B2,1)

Tässä ensimmäisessä esimerkissä on neljä INDEX-kaavaa, joita voimme käyttää erilaisten arvojen saamiseksi:

• =INDEKSI(A1:B2;2;2) etsii läpi A1:B2 löytääkseen arvon toisesta sarakkeesta ja toisesta rivistä Stacy†
• =INDEKSI(A1:B1,1) etsii läpi A1:B1 löytääkseen arvon ensimmäisestä sarakkeesta Jon†
• =INDEKSI(2:2,1) etsii kaiken toisella rivillä löytääkseen arvon ensimmäisestä sarakkeesta, joka on: Tim.
• =INDEKSI(B1:B2,1) etsii kautta B1:B2 löytääkseen arvon ensimmäiseltä riviltä, ​​joka on Amy†

MATCH Esimerkkejä

=MATCH(«Stacy»,A2:D2;0)
=MATCH (14,D1:D2)
=MATCH (14,D1:D2,-1)
=MATCH(13;A1:D1;0)

Tässä on neljä yksinkertaista esimerkkiä MATCH-funktiosta:

• =MATCH(«Stacy»,A2:D2;0) etsii Stacy ulottuvilla A2:D2 ja palaa 3 tuloksena.
• =MATCH (14,D1:D2) etsii 14 ulottuvilla D1:D2mutta koska sitä ei löydy taulukosta, MATCH löytää seuraavan suurin arvo, joka on pienempi tai yhtä suuri kuin 14mikä tässä tapauksessa on 13kuka on asemassa 1 kirjoittaja lookup_array†
• =MATCH (14,D1:D2,-1) on identtinen yllä olevan kaavan kanssa, mutta koska taulukko ei ole laskevassa järjestyksessä, kuten -1 vaaditaan, saamme virheilmoituksen.
• =MATCH(13;A1:D1;0) etsii 13 taulukon ensimmäisellä rivillä, joka palauttaa 4 koska se on tämän taulukon neljäs kohde.

INDEX MATCH Esimerkkejä

Tässä on kaksi esimerkkiä, joissa voimme yhdistää INDEXin ja MATCHin yhteen kaavaan:

Etsi soluviittaus taulukosta

=INDEKSI(B2:B5,MATCH(F1,A2:A5))

Tässä esimerkissä MATCH-kaava on sisäkkäin INDEX-kaavan sisällä. Tarkoituksena on tunnistaa tuotteen väri tuotenumeron perusteella. Jos katsot kuvaa, näet «Delimited»-riveillä, kuinka kaavat kirjoitettaisiin yksinään, mutta koska me upotamme ne sisäkkäin, tapahtuu seuraavaa:

• SOPIMUS (F1,A2:A5) etsii F1 arvo (8795) tietojoukossa A2:A5† Jos laskemme saraketta alaspäin, voimme nähdä, että se on 2niin MATCH-funktio juuri keksi.
• INDEX-taulukko on B2:B5 koska lopulta etsimme arvoa kyseisestä sarakkeesta.
• INDEX-funktio voidaan nyt kirjoittaa uudelleen tällä tavalla siitä lähtien 2 mitä MATCH löysi: INDEKSI(B2:B5, 2, [column_num]††
• Siitä lähtien sarakkeen_numero on valinnainen, voimme poistaa sen jättääksemme tämän: INDEKSI(B2:B5;2)†
• Joten nyt tämä on kuin normaali INDEKSI-kaava, josta löydämme toisen kohteen arvon B2:B5tuo on punainen†

Haku rivi- ja sarakeotsikoiden mukaan

=HAKEMISTO(B2:E13,OSUMA(G1,A2:A13,0),VASTAA(G2,B1:E1,0))

Tässä esimerkissä MATCH ja INDEX teemme kaksisuuntaisen haun. Ajatuksena on nähdä, kuinka paljon rahaa teimme Kasvis artikkelit sisään Voi† Tämä on hyvin samanlainen kuin yllä oleva esimerkki, mutta INDEX-hakemistoon on sisäkkäinen MATCH-kaava.

• SOPIMUS(G1,A2:A13,0) on ensimmäinen tässä kaavassa ratkaistu erä. Se etsii G1 (sana «toukokuu») sisään A2:A13 saadakseen tietyn arvon. Emme näe sitä täällä, mutta se on 5†
• SOPIMUS(G2,B1:E1,0) on toinen MATCH-kaava, ja se on hyvin samanlainen kuin ensimmäinen, mutta etsii sen sijaan G2 (sana «vihreä») sarakkeiden otsikoissa osoitteessa B1:E1† Tämä ratkaisee 3†
• Voimme nyt kirjoittaa INDEX-kaavan uudelleen seuraavasti visualisoidaksemme, mitä tapahtuu: =INDEKSI(B2:E13;5.3)† Tämä etsii koko pöytää, B2:E13viidennelle riville ja kolmannelle sarakkeelle, joka palauttaa 180 dollaria†

MATCH ja INDEX säännöt

On useita asioita, jotka on pidettävä mielessä kirjoittaessasi kaavoja näillä funktioilla:

• MATCH ei erota isot ja pienet kirjaimet, joten isoja ja pieniä kirjaimia käsitellään samalla tavalla tekstiarvoja sovitettaessa.
• MATCH palaa #JÄLKEEN useista syistä: jos match_type On 0 ja hakuarvo ei löydy jos match_type On -1 ja lookup_array ei ole alenevassa järjestyksessä, jos match_type On 1 ja lookup_array ei ole nousevassa järjestyksessä, ja jos lookup_array ei ole yksi rivi tai sarake.
• Voit käyttää jokerimerkkiä hakuarvo argumentti jos match_type On 0 ja hakuarvo on tekstimerkkijono. Kysymysmerkki vastaa mitä tahansa yksittäistä merkkiä ja tähti mitä tahansa merkkijonoa (esim. =MATCH(«Yo*»,1:1,0)† Jos haluat käyttää MATCHia oikean kysymysmerkin tai tähden löytämiseen, kirjoita ensin ~.
• INDEX palauttaa #VIITE! jos rivin_numero ja sarakkeen_numero älä osoita taulukon solua.

Liittyvät Excel-funktiot

MATCH-funktio on samanlainen kuin HAKU, mutta MATCH palauttaa funktion asemaa kohteen sijaan itse esineestä. VLOOKUP on toinen hakutoiminto, jota voit käyttää Excelissä, mutta toisin kuin MATCH, joka vaatii INDEXin tarkennetuille hauille, VLOOKUP-kaavat tarvitsevat vain tämän yhden funktion.