INDEX & MATCH funktsioonide kombinatsioon Excelis (10 lihtsat näidet)

Excelil on palju funktsioone - umbes 450 neist.

Ja paljud neist on lihtsalt suurepärased. Mõne valemiga tehtud töö hulk üllatab mind endiselt (isegi pärast seda, kui olen Excelit kasutanud üle 10 aasta).

Ja kõigi nende hämmastavate funktsioonide hulgas paistab silma funktsioonide kombinatsioon INDEX MATCH.

Olen INDEX MATCH kombinatsiooni tohutu fänn ja olen selle mitu korda päris selgeks teinud.

Kirjutasin isegi artikli Index Match vs VLOOKUP kohta, mis tekitas natuke arutelu (ilutulestiku leiate kommentaaride sektsioonist).

Ja täna kirjutan seda artiklit, mis keskendub ainult Index Matchile, et näidata teile lihtsaid ja täiustatud stsenaariume, kus saate seda võimsat valemikombinatsiooni kasutada ja tööd teha.

Märge: Excelis on ka teisi otsimisvalemeid - näiteks VLOOKUP ja HLOOKUP ning need on suurepärased. Paljud inimesed leiavad, et VLOOKUPi on lihtsam kasutada (ja see on ka tõsi). Usun, et INDEX MATCH on paljudel juhtudel parem valik. Aga kuna inimestel on raske, siis kasutatakse seda vähem. Nii et proovin seda õpetust kasutades lihtsustada.

Enne kui näitan teile, kuidas INDEX MATCHi kombinatsioon muudab analüütikute ja andmeteadlaste maailma, lubage mul kõigepealt tutvustada teile üksikuid osi - INDEX ja MATCH.

Funktsioon INDEX: leiab väärtustel põhineva koordinaadi

Lihtsaim viis indeksi funktsiooni toimimise mõistmiseks on mõelda sellele kui GPS -satelliidile.

Niipea kui ütlete satelliidile laius- ja pikkuskraadid, teab see täpselt, kuhu minna ja selle asukoha leida.

Nii et vaatamata sellele, et sellel on meeletult palju latipikkuseid kombinatsioone, teaks satelliit täpselt, kust otsida.

Otsisin kiiresti oma töökoha ja sain selle.

Igatahes piisab geograafiast.

Nii nagu satelliit vajab laius- ja pikkuskraate, vajab Exceli funktsioon INDEX rea ja veeru numbrit, et teada saada, millisele lahtrile viitate.

Ja see on Excel INDEX-funktsioon pähklikoorega.

Nii et lubage mul seda teie jaoks lihtsate sõnadega määratleda.

Funktsioon INDEX kasutab rea ja veeru numbrit, et leida antud vahemikus lahter ja tagastada selles olev väärtus.

Indeks on iseenesest väga lihtne funktsioon, millel pole kasulikkust. Lõppude lõpuks ei tea te enamikul juhtudel tõenäoliselt ridade ja veergude numbreid.

Aga…

Asjaolu, et saate seda kasutada koos muude funktsioonidega (vihje: MATCH), mis leiavad rea numbri ja veeru numbri, muudab INDEXi äärmiselt võimsa Exceli funktsiooni.

Allpool on funktsiooni INDEX süntaks:

= INDEX (massiiv, rea number, [veerg]] = INDEX (massiiv, rea number, [veerg], [pindala number])

• massiiv - a lahtrite vahemik või massiivikonstant.
• rea_number - rea number, millest väärtus tuleb hankida.
• [col_num] - veeru number, millest väärtus tuleb hankida. Kuigi see on valikuline argument, kuid kui rea_numbrit pole esitatud, tuleb see esitada.
• [ala_number] - (Valikuline) Kui massiivi argument koosneb mitmest vahemikust, kasutatakse seda numbrit viite valimiseks kõigist vahemikest.

Funktsioonil INDEX on 2 süntaksit (lihtsalt FYI).

Esimest kasutatakse enamikul juhtudel. Teist kasutatakse ainult täiustatud juhtudel (näiteks kolmepoolse otsingu tegemisel), mida käsitleme ühes õpetuse hilisemas näites.

Aga kui te pole selle funktsiooni jaoks uus, pidage meeles esimest süntaksi.

Allpool on video, mis selgitab funktsiooni INDEX kasutamist

Funktsioon MATCH: otsib positsiooni otsimisväärtuse alusel

Tulles tagasi oma eelmise näite juurde pikkus- ja laiuskraadidest, siis MATCH on funktsioon, mis suudab need positsioonid leida (Exceli arvutustabelimaailmas).

Lihtsas keeles võib Excel MATCH funktsioon leida lahtri asukoha vahemikus.

Ja mis alusel see raku positsiooni leiaks?

Otsingu väärtuse põhjal.

Näiteks kui teil on allpool näidatud loend ja soovite selles leida nime „Märgi” positsiooni, saate kasutada funktsiooni MATCH.

Funktsioon tagastab 3, kuna see on lahtri asukoht nimega Mark.

Funktsioon MATCH hakkab otsima ülalt alla otsimisväärtust (mis on „Märgi”) määratud vahemikus (mis on selles näites A1: A9). Niipea kui nimi on leitud, tagastab see positsiooni selles konkreetses vahemikus.

Allpool on Exceli funktsiooni MATCH süntaks.

= MATCH (otsinguväärtus, otsimismassiiv, [vastetüüp])

• lookup_value - Väärtus, millele otsingut vaste otsimismassiivist otsite.
• lookup_array - Lahtrite vahemik, milles otsingu_väärtust otsite.
• [vastetüüp] - (Valikuline) See määrab, kuidas Excel peaks sobivat väärtust otsima. See võib võtta kolm väärtust -1, 0 või 1.

Vaste tüübi argumendi mõistmine funktsioonis MATCH

On veel üks asi, mida peate teadma funktsiooni MATCH kohta, ja see puudutab seda, kuidas see andmeid läbib ja lahtri asukoha leiab.

Funktsiooni MATCH kolmas argument võib olla 0, 1 või -1.

Allpool on selgitus, kuidas need argumendid toimivad:

• 0 - see otsib väärtuse täpse vaste. Kui leitakse täpne vaste, tagastab funktsioon MATCH lahtri asukoha. Muidu tagastab see vea.
• 1 - see leiab suurima väärtuse, mis on väiksem või võrdne otsinguväärtusega. Selle toimimiseks tuleb teie andmevahemik sortida kasvavas järjekorras.
• -1 - see leiab väikseima väärtuse, mis on suurem või võrdne otsinguväärtusega. Selle toimimiseks tuleb teie andmevahemik sortida kahanevas järjekorras.

Allpool on video, mis selgitab funktsiooni MATCH kasutamist (koos vaste tüübi argumendiga)

Kokkuvõtteks ja lihtsate sõnadega:

• INDEX vajab lahtri asukohta (rea ja veeru number) ning annab lahtri väärtuse.
• MATCH leiab asukoha, kasutades otsinguväärtust.

Ühendame need jõujaama loomiseks (INDEX + MATCH)

Nüüd, kui teil on põhiteadmised INDEX ja MATCH funktsioonide individuaalsest toimimisest, ühendage need kaks ja õppige tundma kõiki imelisi asju, mida see saab teha.

Selle paremaks mõistmiseks on mul mõned näited, mis kasutavad kombinatsiooni INDEX MATCH.

Alustan lihtsast näitest ja näitan teile ka mõningaid täiustatud kasutusjuhtumeid.

Näidisfaili allalaadimiseks klõpsake siin

Näide 1: lihtne otsing INDEX MATCH Combo abil

Teeme lihtsa otsingu INDEX/MATCH abil.

Allpool on tabel, kus mul on kümne õpilase hinded.

Sellest tabelist tahan leida Jimi märke.

Allpool on valem, mille abil saate seda hõlpsalt teha:

= INDEX ($ A $ 2: $ B $ 11, MATCH ("Jim", $ A $ 2: $ A $ 11,0), 2)

Nüüd, kui arvate, et seda saab hõlpsasti teha funktsiooni VLOOKUP abil, on teil õigus! See ei ole parim INDEX MATCH -i suurepärasuse kasutamine. Hoolimata asjaolust, et olen INDEX MATCHi fänn, on see pisut raskem kui VLOOKUP. Kui paremal veerus andmete toomine on kõik, mida soovite teha, soovitan teil kasutada funktsiooni VLOOKUP.

Põhjus, miks ma seda näidet näitasin, mida saab hõlpsasti teha ka VLOOKUP -iga, on näidata teile, kuidas INDEX MATCH töötab lihtsas seadistuses.

Nüüd lubage mul näidata INDEX MATCH eeliseid.

Oletame, et teil on samad andmed, kuid veergude asemel on need ridades (nagu allpool näidatud).

Tead, mida kasutada, saate siiski kasutada INDEX MATCH kombinatsiooni Jimi märkide saamiseks.

Allpool on valem, mis annab teile tulemuse:

= INDEX ($ B $ 1: $ K $ 2,2, MATCH ("Jim", $ B $ 1: $ K $ 1,0))

Pange tähele, et peate muutma vahemikku ja vahetama rea/veeru osi, et see valem töötaks ka horisontaalsete andmete puhul.

Seda ei saa teha VLOOKUP -iga, kuid saate seda siiski hõlpsalt teha HLOOKUP -iga.

INDEX MATCH kombinatsioon saab hõlpsasti hakkama nii horisontaalsete kui ka vertikaalsete andmetega.

Näidisfaili allalaadimiseks klõpsake siin

Näide 2: Otsige vasakule

See on tavalisem kui arvate.

Paljudel juhtudel peate võib -olla tooma andmed veerust, mis asub otsinguväärtusega veerust vasakul.

Midagi, nagu allpool näidatud:

Michaeli müügi leidmiseks peate otsima vasakult.

Kui mõtlete VLOOKUP -ile, lubage mul teie peatada.

VLOOKUP ei ole loodud vasakul olevate väärtuste otsimiseks ja toomiseks.

Kas saate seda ikkagi VLOOKUPi kasutades teha?

Jah, sa saad!

Kuid see võib muutuda pikaks ja inetuks valemiks.

Nii et kui soovite otsida ja tuua andmeid vasakpoolsetest veergudest, on parem kasutada kombinatsiooni INDEX MATCH.

Allpool on valem, mis annab Michaeli müüginumbri:

= INDEX ($ A $ 2: $ C $ 11, MATCH ("Michael", C2: C11,0), 2)

Siin on veel üks punkt INDEX MATCH. VLOOKUP saab andmeid hankida ainult veergudest, mis asuvad otsinguväärtusega veeru paremal pool.

Näide 3: kahesuunaline otsing

Siiani oleme näinud näiteid, kus tahtsime andmeid tuua otsimisväärtusega veeru kõrval olevast veerust.

Kuid tegelikus elus hõlmavad andmed sageli mitut veergu.

INDEX MATCH saab hõlpsasti hakkama kahesuunalise otsimisega.

Allpool on andmestik õpilase hinnete kohta kolmes erinevas aines.

Kui soovite kiiresti saada õpilase hindeid kõigis kolmes aines, saate seda teha INDEX MATCH abil.

Allolev valem annab teile kõigi kolme teema jaoks Jimi hinded (kopeerige ja kleepige ühte lahtrisse ja lohistage, et täita teisi lahtreid või kopeerida ja kleepida teistele lahtritele).

= INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))

Las ma selgitan kiiresti ka seda valemit.

INDEX -valem kasutab vahemikuna B2: D11.

Esimene MATCH kasutab nime (Jim lahtris F3) ja tõmbab selle asukoha nimede veerust (A2: A11). Sellest saab rea number, kust andmed tuleb hankida.

Teine MATCH -valem kasutab subjekti nime (lahtris G2), et saada konkreetse subjekti nime asukoht B1: D1 -s. Näiteks matemaatika on 1, füüsika on 2 ja keemia on 3.

Kuna need MATCH positsioonid sisestatakse funktsiooni INDEX, tagastab see tulemuse õpilase nime ja aine nime alusel.

See valem on dünaamiline, mis tähendab, et kui muudate õpilase nime või aine nimesid, siis see töötab ja toob õiged andmed.

Üks tore asi INDEX/MATCH -i kasutamisel on see, et isegi kui vahetate subjektide nimesid, annab see teile jätkuvalt õige tulemuse.

Näide 4: otsinguväärtus mitmest veerust/kriteeriumist

Oletame, et teil on allpool näidatud andmekogum ja soovite märke „Mark Long” alla laadida.

Kuna andmed on kahes veerus, ei saa ma Markit otsida ja andmeid hankida.

Kui ma seda teen, saan märke andmed Mark Frost ja mitte Mark Long kohta (sest funktsioon MATCH annab mulle vastava märgi tulemuse).

Üks võimalus seda teha on luua abistajaveerg ja ühendada nimed. Kui teil on abistaja veerg, saate kasutada funktsiooni VLOOKUP ja hankida märkide andmed.

Kui olete huvitatud sellest, kuidas seda teha, lugege seda õpetust VLOOKUPi kasutamise kohta mitme kriteeriumiga.

Kuid kombinatsiooni INDEX/MATCH abil ei vaja te abistajaveergu. Saate luua valemi, mis käsitleb valemis mitmeid kriteeriume.

Allpool toodud valem annab tulemuse.

= INDEKS ($ C $ 2: $ C $ 11, MATCH ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))

Las ma selgitan kiiresti, mida see valem teeb.

Valemi MATCH osa ühendab otsinguväärtuse (Mark ja Long) ning kogu otsingumassiivi. Kui $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 kasutatakse otsimismassiivina, kontrollib see tegelikult otsinguväärtust ees- ja perekonnanime kombineeritud stringi alusel (eraldatud toru sümboliga).

See tagab, et saate õige tulemuse ilma abistajate veerge kasutamata.

Sellist otsingut (kus on mitu veergu/kriteeriumi) saate teha ka funktsiooni VLOOKUP abil, kuid peate kasutama abiveergu. INDEX MATCH kombinatsioon muudab selle hõlpsaks tegemiseks ilma abistajate veergudeta.

Näide 5: hankige väärtused kogu realt/veerult

Ülaltoodud näidetes oleme konkreetse lahtri väärtuse saamiseks kasutanud funktsiooni INDEX. Sisestate rea ja veeru numbri ning see tagastab selle lahtri väärtuse.

Kuid saate teha rohkem.

Funktsiooni INDEX saate kasutada ka terve rea või veeru väärtuste saamiseks.

Ja kuidas see võib teile kasulik olla!

Oletame, et soovite teada Jimi kogusummat kõigis kolmes õppeaines.

Funktsiooni INDEX abil saate kõigepealt saada kõik Jimi märgid ja seejärel kasutada summat SUM.

Vaatame, kuidas seda teha.

Allpool on mul kõigi õpilaste hinded kolmes aines.

Allolev valem annab mulle Jimi kogutulemuse kõigis kolmes õppeaines.

= SUMMA (INDEKS ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))

Las ma selgitan, kuidas see valem töötab.

Siin on trikk kasutada veeru numbrina 0.

Kui kasutate funktsiooni INDEX veeru numbrina 0, tagastab see kõik rea väärtused. Samamoodi, kui kasutate rea numbrina 0, tagastab see kõik veeru väärtused.

Seega tagastab valemi alumine osa väärtuste massiivi - {97, 70, 73}

INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)

Kui sisestate selle ülaltoodud valemi Exceli lahtrisse ja vajutate sisestusklahvi, näete #VALUE! viga. Seda seetõttu, et see ei tagasta mitte ühte väärtust, vaid väärtuste massiivi.

Kuid ärge muretsege, väärtuste hulk on endiselt olemas. Seda saate kontrollida, valides valemi ja vajutades klahvi F9. See näitab teile valemi tulemust, mis on antud juhul kolmest väärtusest koosnev massiiv - {97, 70, 73}

Kui te nüüd selle INDEX -valemi SUM -funktsioonis mähkite, saate selle kõigi Jimi poolt saadud punktide summa.

Sama saate kasutada ka Jimi kõrgeima, madalaima ja keskmise hinde saamiseks.

Nii nagu oleme seda teinud õpilase jaoks, saate seda teha ka mõne aine jaoks. Näiteks kui soovite teema keskmist skoori, võite jätta INDEX valemis rea numbri 0 ja see annab teile kõik selle teema veeruväärtused.

Näidisfaili allalaadimiseks klõpsake siin

Näide 6: leidke õpilase hinne (ligikaudne vastetehnika)

Siiani oleme otsinguväärtuse täpse vaste saamiseks kasutanud valemit MATCH.

Kuid saate seda kasutada ka ligikaudse vaste tegemiseks.

Mis kurat on ligikaudne vaste?

Las ma selgitan.

Kui otsite selliseid asju nagu nimed või ID -d, otsite täpset vastet. Kuid mõnikord peate teadma, millises vahemikus teie otsinguväärtused asuvad. Tavaliselt on see nii numbritega.

Näiteks võiksite klassijuhatajana teada saada, milline on iga õpilase hinne aines ja hinne otsustatakse punktisumma alusel.

Allpool on näide, kus ma tahan kõigi õpilaste hindeid ja hindamine otsustatakse paremal oleva tabeli alusel.

Nii et kui õpilane saab alla 33, on hinne F ja kui ta saab vähem kui 50, kuid rohkem kui 33, on see E jne.

Allpool on valem, mis seda teeb.

= INDEKS ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8,1), 1)

Las ma selgitan, kuidas see valem töötab.

Funktsioonis MATCH kasutasime argumendina [match_type] 1. See argument tagastab suurima väärtuse, mis on väiksem või võrdne otsinguväärtusega.

See tähendab, et MATCH -valem läbib märkide vahemiku ja niipea, kui leiab märkide vahemiku, mis on võrdne või väiksem kui otsingumärkide väärtus, peatub see seal ja tagastab oma positsiooni.

Nii et kui otsingumärgi väärtus on 20, tagastab funktsioon MATCH 1 ja kui see on 85, siis 5.

Funktsioon INDEX kasutab seda positsiooni väärtust hinde saamiseks.

TÄHTIS: selle toimimiseks tuleb teie andmed sortida kasvavas järjekorras. Kui ei, siis võite saada valesid tulemusi.

Pange tähele, et ülaltoodut saab teha ka allpool oleva VLOOKUP valemi abil:

= VLOOKUP (B2, $ E $ 3: $ F $ 8,2, TRUE)

Kuid MATCH -funktsioon võib ligikaudse vaste osas astuda sammu edasi.

Teil võib olla ka kahanevaid andmeid ja saate tulemuse leidmiseks kasutada kombinatsiooni INDEX MATCH. Näiteks kui muudan hinnetabeli järjekorda (nagu allpool näidatud), leian ikkagi õpilaste hinded.

Selleks pean vaid muutma argumendi [match_type] väärtuseks -1.

Allpool on valem, mida olen kasutanud:

= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8, -1), 1)

VLOOKUP saab teha ka ligikaudse vaste, kuid ainult siis, kui andmed on sorteeritud kasvavas järjekorras (kuid see ei tööta, kui andmed on sortitud kahanevas järjekorras).

Näide 7: tõstutundlikud otsingud

Siiani on kõik meie tehtud otsingud olnud tõstutundlikud.

See tähendab, et see, kas otsingu väärtus oli Jim, JIM või jim, polnud oluline. Saate sama tulemuse.

Aga mis siis, kui soovite, et otsing oleks tõstutundlik.

See on tavaliselt nii, kui teil on suured andmekogumid ja võimalus korduda või erinevad nimed/ID -d (ainus erinevus on see)

Oletame näiteks, et mul on järgmine õpilaste andmekogum, kus on kaks õpilast nimega Jim (ainus erinevus on see, et üks on sisestatud Jimina ja teine ​​jimina).

Pange tähele, et sama nimega õpilasi on kaks - Jim (lahter A2 ja A5).

Kuna tavaline otsing ei tööta, peate tegema tõstutundliku otsingu.

Allpool on valem, mis annab teile õige tulemuse. Kuna see on massiivivalem, peate kasutama klahve Control + Shift + Enter.

= INDEX ($ B $ 2: $ B $ 11, MATCH (TRUE, EXACT (D3, A2: A11), 0), 1)

Las ma selgitan, kuidas see valem töötab.

Funktsioon EXACT kontrollib, kas otsinguväärtus (mis antud juhul on „jim”) vastab täpselt. See läbib kõik nimed ja tagastab vale, kui see pole vaste, ja tõsi, kui see on vaste.

Seega on selle näite funktsiooni TÄPSEL väljund - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

Pange tähele, et on ainult üks tõde, mis on siis, kui funktsioon EXACT leidis ideaalse sobituse.

Funktsioon MATCH leiab seejärel funktsiooni TRAKS tagastatud massiivist tõelise positsiooni, mis on selles näites 4.

Kui meil on positsioon olemas, kasutab funktsioon INDEX seda märkide leidmiseks.

Näide 8: leidke lähim vaste

Liigume nüüd natuke edasi.

Oletame, et teil on andmestik, kust soovite leida inimese, kellel on töökogemus kõige lähemal nõutavale kogemusele (mainitud lahtris D2).

Kuigi otsinguvalemid pole selleks loodud, saate selle tegemiseks kombineerida teiste funktsioonidega (nt MIN ja ABS).

Allpool on valem, mis otsib nõutavale kõige lähema kogemusega inimese ja tagastab inimese nime. Pange tähele, et kogemus peab olema kõige lähemal (see võib olla kas vähem või rohkem).

= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15))), ABS (D2- $ B $ 2: $ B $ 15), 0))

Kuna see on massiivivalem, peate kasutama klahve Control + Shift + Enter.

Selle valemi nipp on muuta otsinguväärtust ja otsingumassiivi, et leida nõutavate ja tegelike väärtuste minimaalne kogemuserinevus.

Enne valemi selgitamist mõistame, kuidas seda käsitsi teha.

Te vaatate läbi veeru B iga lahtri ja leiate erinevuse nõutava kogemuse ja selle vahel, mis inimesel on. Kui teil on kõik erinevused, leiate minimaalse ja leidke selle inimese nimi.

Selle valemiga teeme täpselt seda.

Las ma selgitan.

Otsinguväärtus MATCH valemis on MIN (ABS (D2-B2: B15)).

See osa annab teile minimaalse erinevuse antud kogemuse (mis on 2,5 aastat) ja kõigi teiste kogemuste vahel. Selles näites tagastab see väärtuse 0,3

Pange tähele, et olen kasutanud ABS -i veendumaks, et otsin lähimat (mis võib olla rohkem või vähem kui antud kogemus).

Nüüd saab sellest minimaalsest väärtusest meie otsinguväärtus.

Funktsiooni MATCH otsimismassiiv on ABS (D2- $ B $ 2: $ B $ 15).

See annab meile hulga numbreid, millest on lahutatud 2,5 (nõutav kogemus).

Nüüd on meil otsinguväärtus (0,3) ja otsingumassiiv ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})

Funktsioon MATCH leiab selles massiivis positsiooni 0,3, mis on ka lähima kogemusega isiku nimi.

Seda positsiooni numbrit kasutab funktsioon INDEX isiku nime tagastamiseks.

Seotud lugemine: Otsige Excelist lähim vaste (näited otsimisvalemite abil)

Näidisfaili allalaadimiseks klõpsake siin

Näide 9: kasutage INDEX MATCH -i koos metamärkidega

Kui soovite osalise vaste korral väärtust otsida, peate kasutama metamärke.

Näiteks allpool on ettevõtte nime ja nende turukapitalisatsiooni andmekogum ning soovite saada turupiiri. andmed kolme parema ettevõtte kohta.

Kuna need pole täpsed vasted, ei saa te sel juhul regulaarset otsingut teha.

Õigeid andmeid saate siiski tärniga (*), mis on metamärk.

Allpool on valem, mis annab teile andmed, sobitades peamises veerus olevad ettevõtete nimed ja hankides selle jaoks turupiiri näitaja.

= INDEX ($ B $ 2: $ B $ 10, MATCH (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)

Las ma selgitan, kuidas see valem töötab.

Kuna otsinguväärtuste täpne vaste puudub, olen kasutanud D2 ja "*" otsimisväärtusena funktsioonis MATCH.

Tärn on metamärk, mis tähistab suvalist arvu tähemärke. See tähendab, et Apple* valemis tähendaks mis tahes tekstistringi, mis algab sõnaga Apple ja mille järel võib olla suvaline arv tähemärke.

Nii et millal Apple* kasutatakse otsinguväärtusena ja MATCH valem otsib seda veerust A, tagastab see positsiooni „Apple Inc.”, kuna see algab sõnaga Apple.

Samuti võite kasutada metamärke, et leida tekstistringe, mille otsinguväärtus jääb nende vahele. Näiteks kui kasutate otsinguväärtusena * Apple *, leiab see suvalise stringi, kus on sõna õun.

Märkus. See tehnika töötab hästi, kui teil on ainult üks sobitamisjuhtum. Aga kui teil on mitu sobitamisjuhtumit (näiteks Apple Inc ja Apple Corporation), tagastab funktsioon MATCH ainult esimese sobiva eksemplari asukoha.

Näide 10: Kolmesuunaline otsing

See on INDEX MATCHi täiustatud kasutus, kuid katan selle siiski, et näidata teile selle kombinatsiooni jõudu.

Pidage meeles, et ütlesin, et funktsioonil INDEX on kaks süntaksi:

= INDEX (massiiv, rea number, [veerg]] = INDEX (massiiv, rea number, [veerg], [pindala number])

Siiani oleme kõigis oma näidetes kasutanud ainult esimest.

Kuid kolmepoolse otsingu jaoks peate kasutama teist süntaksit.

Lubage mul kõigepealt selgitada, mida tähendab kolmepoolne välimus.

Kahesuunalises otsingus kasutame hinde saamiseks INDEX MATCH valemit, kui meil on õpilase nimi ja aine nimi. Näiteks Jimi märkide toomine matemaatikas on kahepoolne otsing.

Kolmepoolne välimus annaks sellele veel ühe mõõtme. Oletame näiteks, et teil on allpool näidatud andmekogum ja soovite teada Jimi tulemust matemaatikas keskastmeeksamil, siis oleks see kolmepoolne otsing.

Allpool on valem, mis annab tulemuse.

= INDEX (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH (G $ 4 , $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "Unit Test", 1, IF (G $ 3 = "Mid term", 2,3))))

Ülaltoodud valemiga kontrolliti kolme asja - õpilase nime, ainet ja eksamit. Pärast õige väärtuse leidmist tagastab see selle lahtrisse.

Lubage mul selgitada, kuidas see valem töötab, jagades valemi osadeks.

• massiiv - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): Ühe massiivi kasutamise asemel olen sel juhul kasutanud sulgudes kolme massiivi.
• rea_number - MATCH ($ 5 $, $ A $ 3: $ A $ 7,0): Funktsiooni MATCH kasutatakse õpilase nime asukoha leidmiseks lahtris $ F $ 5 õpilase nime loendis.
• col_num - MATCH (4 dollarit, 2 dollarit: 2 dollarit: 2,0 dollarit): Funktsiooni MATCH kasutatakse subjekti nime asukoha leidmiseks lahtris $ B $ 2 subjekti nime loendis.
• [area_num] - IF (G $ 3 = ”Unit Test”, 1, IF (G $ 3 = ”Mid Term”, 2,3)): Pindala numbri väärtus ütleb funktsioonile INDEX, millist kolmest massiivist väärtuse toomiseks kasutada. Kui eksam on ühikutermin, tagastab IF funktsioon 1 ja funktsioon INDEX kasutab väärtuse toomiseks esimest massiivi. Kui eksam on vahepealne, tagastab IF-valem 2, vastasel juhul 3.

See on täiustatud näide INDEX MATCHi kasutamisest ja tõenäoliselt ei leia te olukorda, kui peate seda kasutama. Kuid ikkagi on hea teada, mida Exceli valemid suudavad.

Näidisfaili allalaadimiseks klõpsake siin

Miks on INDEX/MATCH parem kui VLOOKUP?

VÕI on?

Jah, see on - enamikul juhtudel.

Esitan mõne aja pärast oma juhtumi.

Aga enne kui ma seda teen, lubage mul seda öelda - VLOOKUP on äärmiselt kasulik funktsioon ja mulle meeldib see. See suudab Excelis palju asju teha ja ma kasutan seda aeg -ajalt ka ise. Seda öeldes ei tähenda see, et midagi paremat ei saaks olla ja INDEX/MATCH (suurema paindlikkuse ja funktsionaalsusega) on parem.

Nii et kui soovite teha põhiotsingu, on parem kasutada VLOOKUP -i.

INDEX/MATCH on steroidide VLOOKUP. Ja kui olete INDEX/MATCH -i õppinud, eelistate seda alati kasutada (eriti selle paindlikkuse tõttu).

Ilma seda liiga kaugele venitamata lubage mul kiiresti põhjendada, miks INDEX/MATCH on parem kui VLOOKUP.

INDEX/MATCH saab vaadata otsinguväärtuse vasakule (ja ka paremale)

Ma käsitlesin seda ühes ülaltoodud näites.

Kui teil on väärtus, mis asub otsinguväärtusest vasakul, ei saa te seda funktsiooni VLOOKUP abil teha

Vähemalt mitte ainult VLOOKUP -iga.

Jah, saate VLOOKUPi kombineerida teiste valemitega ja teha, kuid see muutub keeruliseks ja segaseks.

INDEX/MATCH seevastu on otsitud kõikjalt (olgu see siis vasakule, paremale, üles või alla)

INDEX/MATCH võib töötada vertikaalsete ja horisontaalsete vahemikega

Jällegi, täieliku austusega VLOOKUP -ile, pole seda tehtud.

Lõppude lõpuks tähistab V in VLOOKUP vertikaali.

VLOOKUP saab läbida ainult vertikaalseid andmeid, samas kui INDEX/MATCH saab andmeid läbida nii vertikaalselt kui ka horisontaalselt.

Loomulikult on horisontaalse otsingu eest hoolitsemiseks funktsioon HLOOKUP, kuid see pole siis VLOOKUP … eks?

Mulle meeldib asjaolu, et INDEX MATCH kombinatsioon on piisavalt paindlik, et töötada nii vertikaalsete kui ka horisontaalsete andmetega.

VLOOKUP ei saa töötada kahanevate andmetega

Ligikaudse vaste osas on VLOOKUP ja INDEX/MATCH samal tasemel.

Kuid INDEX MATCH võtab selle punkti, kuna see saab hakkama ka kahanevas järjekorras olevate andmetega.

Näitan seda selle õpetuse ühes näites, kus peame hindamistabeli põhjal leidma õpilaste hinde. Kui tabel on sorteeritud kahanevas järjekorras, ei tööta VLOOKUP (kuid INDEX MATCH).

INDEX/MATCH võib olla veidi kiirem

Jään tõeks. Ma ei teinud seda testi ise.

Ma loodan Exceli meistri - Charley Kydi - tarkusele.

Kiiruste erinevus VLOOKUP ja INDEX/MATCH puhul on väikeste andmekogumite korral vaevalt märgatav. Aga kui teil on tuhandeid ridu ja palju veerge, võib see olla otsustav tegur.

Charley Kyd märgib oma artiklis:

„Halvimal juhul on INDEX-MATCH meetod umbes sama kiire kui VLOOKUP; parimal juhul on see palju kiirem. ”

INDEX/MATCH ei sõltu tegelikust veerupositsioonist

Kui teil on allpool näidatud andmekogum, kui otsite füüsika Jimi tulemust, saate seda teha funktsiooni VLOOKUP abil.

Ja selleks saate VLOOKUPis määrata veeru numbri 3.

Kõik on hästi.

Aga mis siis, kui ma kustutan veeru Matemaatika.

Sellisel juhul puruneb VLOOKUP valem.

Miks? - Kuna kolmanda veeru kasutamine oli kõvasti kodeeritud ja kui vahepealse veeru kustutan, saab kolmandast veerust teine ​​veerg.

Sel juhul on INDEX/MATCH kasutamine parem, kuna saate veeru numbri dünaamiliseks muuta, kasutades funktsiooni MATCH. Seega kontrollib see veeru numbri asemel teema nime ja kasutab seda veeru numbri tagastamiseks.

Kindlasti saate seda teha, ühendades VLOOKUPi ja MATCH -i, kuid kui te seda ikkagi kombineerite, siis miks mitte teha seda INDEX -iga, mis on palju paindlikum.

INDEX/MATCH -i kasutamisel saate oma andmestikku veerge turvaliselt sisestada/kustutada.

Vaatamata kõigile neile teguritele on põhjus, miks VLOOKUP on nii populaarne.

Ja see on suur põhjus.

VLOOKUPi on lihtsam kasutada

VLOOKUP võtab maksimaalselt neli argumenti. Kui suudate oma pea nende nelja ümber pöörata, on teil hea minna.

Ja kuna enamiku põhiliste otsingujuhtumitega tegeleb ka VLOOKUP, on sellest kiiresti saanud kõige populaarsem Exceli funktsioon.

Ma nimetan seda Exceli funktsioonide kuningaks.

INDEX/MATCH on seevastu veidi raskem kasutada. Kui hakkate seda kasutama, võite selle kasutamise ajal nurja minna, kuid algaja jaoks on VLOOKUPi palju lihtsam seletada ja õppida.

Ja see pole nullsumma mäng.

Niisiis, kui olete otsingumaailmas uus ja ei tea, kuidas VLOOKUPi kasutada, õppige seda paremini.

Mul on üksikasjalik juhend VLOOKUPi kasutamiseks Excelis (palju näiteid)

Minu eesmärk selles artiklis ei ole kahe hämmastava funktsiooni vastandamine üksteisele. Tahtsin teile näidata INDEX MATCH kombinatsiooni jõudu ja kõiki suurepäraseid asju, mida see saab teha.

Loodetavasti leidsite selle artikli kasulikuks.

Andke mulle oma mõtted kommentaaride jaotises teada ja kui leiate selles õpetuses vigu, andke mulle sellest teada.