Kuidas kasutada Exceli funktsiooni INDEX ja MATCH

Selles artiklis selgitatakse, kuidas kasutada funktsioone INDEX ja MATCH koos kõigis Exceli versioonides, sealhulgas Excel 2019 ja Microsoft 365.

Mis on funktsioonid INDEX ja MATCH?

INDEX ja MATCH on Exceli otsing funktsioonid. Kuigi need on kaks täiesti erinevat funktsiooni, mida saab kasutada eraldi, saab neid ka kombineerida, et luua täpsemaid valemeid.

Funktsioon INDEX tagastab väärtuse või viite väärtusele konkreetsest valikust. Näiteks saab seda kasutada väärtuse leidmiseks andmekogumi teisest reast või viiendast reast ja kolmandast veerust.

Kuigi INDEXit saab väga hästi kasutada üksi, muudab MATCHi pesastamine valemis selle pisut kasulikumaks. Funktsioon MATCH otsib lahtrivahemikus määratud üksust ja tagastab seejärel üksuse suhtelise asukoha vahemikus. Näiteks saab seda kasutada määramaks, et konkreetne nimi on nimede loendi kolmas üksus.

INDEX ja MATCH Süntaks ja argumendid

Nii tuleb mõlemad funktsioonid kirjutada, et Excel neist aru saaks:

=INDEKS(massiivi, rea_number, [veeru_number])

• massiivi on lahtrite vahemik, mida valem kasutab. See võib olla üks või mitu rida ja veergu, näiteks A1:D5. See on nõutav.
• rea_number on massiivi rida, millest tagastada väärtus, näiteks 2 või 18. See on nõutav, välja arvatud juhul veeru_number on kohal.
• veeru_number on massiivi veerg, millest tagastatakse väärtus, näiteks 1 või 9. See on valikuline.

=MATCH(otsingu_väärtus, otsingu_massiiv, [vaste_tüüp])

• otsingu_väärtus on väärtus, millega soovite sobitada otsingu_massiiv. See võib olla arv, tekst või loogiline väärtus, mis sisestatakse käsitsi või millele viidatakse lahtri viite kaudu. See on vajalik.
• otsingu_massiiv on rakkude vahemik, millest läbi vaadata. See võib olla üks rida või üks veerg, näiteks A2:D2 või G1:G45. See on vajalik.
• vaste_tüüp võib olla -1, 0, või 1. See täpsustab, kuidas otsingu_väärtus on vastavuses väärtustega otsingu_massiiv (vt allpool). 1 on vaikeväärtus, kui see argument on välja jäetud.

Millist vastetüüpi kasutada
Sobivuse tüüp	Mida see teeb	Reegel	Näide
1	Leiab suurima väärtuse, mis on väiksem või võrdne otsingu_väärtus.	The otsingu_massiiv väärtused tuleb paigutada kasvavas järjekorras (nt -2, -1, 0, 1, 2; või A-Z; või VÄÄR, TÕENE.	otsingu_väärtus on 25, kuid see puudub otsingu_massiiv, seega tagastatakse selle asemel järgmise väikseima arvu asukoht, näiteks 22.
0	Leiab esimese väärtuse, mis on täpselt võrdne otsingu_väärtus.	The otsingu_massiiv väärtused võivad olla mis tahes järjekorras.	otsingu_väärtus on 25, seega tagastab see positsiooni 25.
-1	Leiab väikseima väärtuse, mis on suurem või võrdne otsingu_väärtus.	The otsingu_massiiv väärtused tuleb paigutada kahanevas järjekorras (nt 2, 1, 0, -1, -2).	otsingu_väärtus on 25, kuid see puudub otsingu_massiiv, seega tagastatakse selle asemel suuruselt järgmise arvu asukoht, näiteks 34.

Näidisvalemid INDEX ja MATCH

Enne kui vaatame, kuidas ühendada INDEX ja MATCH üheks valemiks, peame mõistma, kuidas need funktsioonid iseseisvalt töötavad.

INDEX Näited

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

Selles esimeses näites on neli INDEXi valemit, mida saame erinevate väärtuste saamiseks kasutada:

• =INDEKS(A1:B2;2;2) vaatab läbi A1:B2, et leida väärtus teisest veerust ja teisest reast, mis on Stacy.
• =INDEKS(A1:B1,1) vaatab läbi A1:B1, et leida väärtus esimesest veerust, mis on Jon.
• =INDEKS(2:2,1) vaatab läbi kõik teises reas, et leida väärtus esimesest veerust, mis on Tim.
• =INDEKS(B1:B2,1) vaatab läbi B1:B2, et leida väärtus esimesest reast, mis on Amy.

MATCH Näited

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

Siin on neli lihtsat näidet funktsiooni MATCH kohta:

• =MATCH("Stacy",A2:D2,0) otsib Stacy vahemikus A2:D2 ja naaseb 3 tulemuseks.
• =MATCH(14;D1:D2) otsib 14 vahemikus D1:D2, kuid kuna seda tabelist ei leitud, leiab MATCH suuruselt järgmise väärtuse, mis on väiksem või võrdne 14, mis antud juhul on 13, mis on asendis 1 kohta otsingu_massiiv.
• =MATCH(14;D1:D2;-1) on identne selle kohal oleva valemiga, kuid kuna massiiv ei ole kahanevas järjekorras nagu -1 nõuab, saame vea.
• =VASTA(13;A1:D1;0) otsib 13 lehe esimeses reas, mis tagastab 4 kuna see on selle massiivi neljas üksus.

INDEX-MATCH Näited

Siin on kaks näidet, kus saame kombineerida INDEX ja MATCH ühes valemis:

Otsige tabelist lahtri viide

=INDEKS(B2:B5,VASTA(F1,A2:A5))

See näide pesastab valemi MATCH valemis INDEX. Eesmärk on kauba numbri abil tuvastada kauba värv.

Kui vaatate pilti, näete ridadel "Eraldatud", kuidas valemid kirjutatakse eraldi, kuid kuna me pesame need, siis toimub see järgmiselt:

• MATCH (F1,A2:A5) otsib F1 väärtus (8795) andmekogus A2:A5. Kui loendame veergu allapoole, näeme, et see on 2, nii et just selle leidis funktsioon MATCH.
• INDEX massiiv on B2:B5 kuna me lõpuks otsime väärtust selles veerus.
• Funktsiooni INDEX saab nüüd niimoodi ümber kirjutada 2 MATCH leidis: INDEKS(B2:B5, 2, [veeru_arv]).
• Alates veeru_number on valikuline, saame selle eemaldada, et jätta see: INDEKS(B2:B5,2).
• Nüüd on see nagu tavaline INDEXi valem, kus leiame teise üksuse väärtuse B2:B5, mis on punane.

Otsi ridade ja veergude pealkirjade järgi

=INDEKS(B2:E13,VASTA(G1,A2:A13,0),VASTA(G2,B1:E1,0))

Selles MATCH ja INDEX näites teeme kahesuunalist otsingut. Mõte on näha, kui palju raha me teenisime Roheline esemed sisse mai. See on tõesti sarnane ülaltoodud näitega, kuid INDEXis on pesastatud lisavalem MATCH.

• MATCH(G1,A2:A13,0) on esimene selles valemis lahendatud üksus. See otsib G1 (sõna "mai") ​​sisse A2:A13 konkreetse väärtuse saamiseks. Me ei näe seda siin, aga see on 5.
• MATCH(G2,B1:E1,0) on teine ​​MATCHi valem ja see on tõesti sarnane esimesega, kuid selle asemel otsitakse G2 (sõna "Roheline") veergude pealkirjades aadressil B1:E1. See otsustab 3.
• Nüüd saame toimuva INDEXi valemi ümber kirjutada järgmiselt, et visualiseerida, mis toimub: =INDEKS(B2:E13;5;3). See vaatab kogu tabelist, B2:E13, viienda rea ​​ja kolmanda veeru jaoks, mis tagastab $180.

MATCH ja INDEX reeglid

Nende funktsioonidega valemite kirjutamisel tuleb meeles pidada mitmeid asju:

• MATCH ei ole tõstutundlik, seega käsitletakse suuri ja väikeseid tähti tekstiväärtuste sobitamisel ühtemoodi.
• MATCH naaseb #N/A mitmel põhjusel: kui vaste_tüüp on 0 ja otsingu_väärtus ei leita, kui vaste_tüüp on -1 ja otsingu_massiiv ei ole kahanevas järjekorras, kui vaste_tüüp on 1 ja otsingu_massiiv ei ole kasvavas järjekorras ja kui otsingu_massiiv ei ole üks rida või veerg.
• Saate kasutada metamärki otsingu_väärtus argument, kui vaste_tüüp on 0 ja otsingu_väärtus on tekstistring. Küsimärk vastab mis tahes üksikule märgile ja tärn vastab mis tahes märgijadale (nt =MATCH("Jo*",1:1,0)). Kui soovite kasutada MATCH-i tegeliku küsimärgi või tärni leidmiseks, tippige esmalt ~.
• INDEX tagastab #REF! kui rea_number ja veeru_number ära osuta massiivi lahtrile.

Seotud Exceli funktsioonid

Funktsioon MATCH on sarnane funktsiooniga LOOKUP, kuid MATCH tagastab positsiooni üksuse enda asemel.

VLOOKUP on veel üks otsingufunktsioon, mida saate Excelis kasutada, kuid erinevalt MATCH-ist, mis nõuab täpsemate otsingute jaoks INDEXit, vajavad VLOOKUP-i valemid ainult seda ühte funktsiooni.