Naha INDEX sareng MATCH langkung saé tibatan VLOOKUP dina Excel

Kami saacanna parantos ngajelaskeun ka pemula kumaha ngagunakeun fungsi dasar VLOOKUP (Inggris VLOOKUP, singgetan kanggo "fungsi lookup vertikal"). Sareng pangguna anu ngalaman nunjukkeun sababaraha rumus anu langkung rumit.

Sareng dina tulisan ieu kami bakal nyobian masihan inpormasi ngeunaan metode anu sanés pikeun damel sareng milarian nangtung.

Anjeun bisa jadi wondering: "Naha ieu diperlukeun?". Sareng ieu diperyogikeun pikeun nunjukkeun sadaya padika milarian anu mungkin. Salaku tambahan, seueur larangan VLOOKUP sering nyegah hasil anu dipikahoyong. Dina hal ieu, INDEX () MATCH () leuwih fungsional jeung rupa-rupa, jeung maranéhna ogé boga pangsaeutikna larangan.

Dasar INDEKS cocog

Kusabab tujuan pituduh ieu nunjukkeun kumaha saé fitur ieu, kami Hayu urang nempo informasi dasar ngeunaan prinsip operasi na. Sarta kami bakal némbongkeun conto, sarta ogé mertimbangkeun naha, eta leuwih hade tinimbang VLOOKUP ().

Sintaksis Fungsi INDEX sareng Pamakéan

Pungsi ieu mantuan dina manggihan nilai nu dipikahoyong diantara wewengkon pilarian ditangtukeun dumasar kana kolom atawa nomer garis. Sintaksis:

=INDEX(array, nomer baris, nomer kolom):

• Asép Sunandar Sunarya - wewengkon dimana pilarian bakal lumangsung;
• nomer garis - jumlah garis nu bakal ditéang dina Asép Sunandar Sunarya dieusian. Lamun nomer baris teu kanyahoan, jumlah kolom kudu dieusian;
• Jumlah kolom - jumlah kolom anu bakal dipendakan dina susunan anu ditangtukeun. Upami nilaina teu dipikanyaho, peryogi nomer garis.

Conto rumus basajan:

=INDEKS(A1:S10,2,3)

Fungsina bakal milarian dina rentang ti A1 dugi ka C10. Angka nunjukkeun baris mana (2) sareng kolom (3) pikeun nunjukkeun nilai anu dipikahoyong. Hasilna bakal sél C2.

Geulis basajan, katuhu? Tapi nalika anjeun damel sareng dokumén nyata, anjeun henteu mungkin gaduh inpormasi ngeunaan nomer kolom atanapi sél. Éta naon fungsi MATCH () pikeun.

MATCH Fungsi Sintaksis sareng Pamakéan

Pungsi MATCH () neangan nilai nu dipikahoyong tur nembongkeun jumlah perkiraan na di wewengkon pilarian dieusian.

Sintaksis searchpos() siga kieu:

= MATCH(nilai pikeun lookup, array to lookup, tipe cocok)

• nilai pilarian - jumlah atawa téks bisa kapanggih;
• Asép Sunandar Sunarya searched - wewengkon mana pilarian bakal lumangsung;
• tipe cocok - nangtukeun naha néangan nilai pasti atawa nilai nu pangdeukeutna ka eta:
  • 1 (atawa euweuh nilai dieusian) - mulih nilai pangbadagna nu sarua atawa kirang ti nilai nu ieu dieusian;
  • 0 - nembongkeun hiji patandingan pasti jeung nilai searched. Dina kombinasi INDEX () cocog () anjeun bakal ampir sok butuh hiji patandingan pasti, sangkan nulis 0;
  • -1 - Némbongkeun nilai pangleutikna nu leuwih gede atawa sarua jeung nilai dieusian dina rumus. Asihan dilaksanakeun dina urutan turun.

Contona, dina rentang B1:B3 New York, Paris, London kadaptar. Rumus di handap bakal nunjukkeun nomer 3 sabab London katilu dina daptar:

=EXPOSE(London,B1:B3,0)

Kumaha carana dianggo kalayan fungsi INDEX MATCH 

Anjeun meureun geus mimiti ngarti prinsip nu gawé bareng fungsi ieu diwangun. Pondokna, lajeng INDEX () milarian nilai anu dipikahoyong diantara barisan sareng kolom anu ditangtukeun. Sareng MATCH () nunjukkeun jumlah nilai ieu:

=INDEX(kolom dimana nilaina dipulangkeun, MATCH(nilai pikeun milarian, kolom kanggo milarian, 0))

Masih sesah ngartos kumaha jalanna? Meureun conto bakal ngajelaskeun hadé. Anggap anjeun gaduh daptar ibukota dunya sareng populasina:

Pikeun manggihan ukuran populasi ibukota tangtu, contona, ibukota Jepang, urang ngagunakeun rumus ieu:

=INDEX(C2:C10, MATCH(Jepang, A2:A10,0))

kieu:

• MATCH () fungsi néangan nilai - "Jepang" dina Asép Sunandar Sunarya A2: A10 sarta mulih angka 3, sabab Jepang teh nilai katilu dina daptar. 
• inohong ieu mana kanomer garis” dina rumus INDEX () jeung ngabejaan fungsi pikeun print nilai tina baris ieu.

Jadi rumus di luhur jadi rumus baku INDEKS(C2:C10,3). Rumus maluruh ti sél C2 nepi ka C10 sarta mulangkeun data ti sél katilu dina rentang ieu, nyaeta, C4, sabab mundur dimimitian ti baris kadua.

Ulah rék nulis resep ngaran kota dina rumus? Teras nyerat dina sél mana waé, sebutkeun F1, sareng dianggo salaku rujukan dina rumus MATCH (). Jeung anjeun mungkas nepi ka rumus pilarian dinamis:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Penting! Jumlah garis dina aturan INDEX () kudu sarua jeung jumlah baris dina dianggap Asép Sunandar Sunarya dina MATCH (), disebutkeun anjeun bakal meunang hasil salah.

Antosan sakedap, naha henteu nganggo rumus VLOOKUP ()?

=VLOOKUP(F1, A2:C10, 3, Palsu)

 Naon gunana ngabuang-buang waktos pikeun ngémutan sadaya pajeulitna tina INDEKS MATCH?

Dina hal ieu, henteu masalah anu fungsina dianggo. Ieu ngan hiji conto ngartos kumaha INDEX () sarta cocog () fungsi gawé bareng. Conto-conto anu sanés bakal nunjukkeun naon fungsi ieu sanggup dina kaayaan dimana VLOOKUP teu aya kakuatanana. 

INDEX MATCH atanapi VLOOKUP

Nalika mutuskeun rumus milarian anu dianggo, seueur anu satuju yén INDEX () sareng MATCH () langkung unggul tibatan VLOOKUP. Sanajan kitu, loba jalma masih ngagunakeun VLOOKUP (). Firstly, VLOOKUP () leuwih basajan, jeung Bréh, pamaké teu pinuh ngartos sagala kaunggulan gawé bareng INDEX () jeung cocog (). Tanpa pangaweruh ieu, teu saurang ogé bakal satuju méakkeun waktu maranéhanana diajar sistem kompléks.

Ieu mangrupikeun kaunggulan konci INDEX () sareng MATCH () tina VLOOKUP ():

• Pilarian ti katuhu ka kenca. VLOOKUP () teu tiasa milarian ti katuhu ka kenca, janten nilai anu anjeun milarian kedah salawasna aya dina kolom paling kénca dina tabél. Tapi INDEX () sarta cocog () tiasa ngadamel ieu tanpa masalah. Artikel ieu bakal ngabejaan Anjeun naon eta Sigana mah dina prakna: kumaha carana manggihan nilai nu dipikahoyong dina sisi kénca.

1. Tambahan aman atawa ngaleupaskeun kolom. Rumus VLOOKUP () nunjukkeun hasil anu salah nalika ngahapus atanapi nambihan kolom sabab VLOOKUP () peryogi nomer kolom anu pasti pikeun suksés. Alami, nalika kolom ditambahkeun atawa dihapus, jumlah maranéhanana ogé robah. 

Sarta dina rumus INDEX () jeung MATCH () sauntuyan kolom dieusian, teu kolom individu. Hasilna, anjeun aman tiasa nambihan sareng ngahapus kolom tanpa kedah ngapdet rumus unggal waktos.

1. Taya wates dina volume pilarian. Lamun maké VLOOKUP (), jumlah total kriteria pilarian teu kudu ngaleuwihan 255 karakter atawa anjeun bakal meunang #VALUE! Ku kituna lamun data anjeun ngandung angka nu gede ngarupakeun karakter, INDEX () sarta cocog () mangrupa pilihan pangalusna.
2. speed processing tinggi. Lamun tabel anjeun relatif leutik, mangka anjeun saperti teu mirip perhatikeun bédana nanaon. Tapi, lamun tabél ngandung ratusan atawa rébuan baris, sarta, sasuai, aya ratusan jeung rébuan rumus, INDEX () jeung MATCH () bakal Cope leuwih gancang ti VLOOKUP (). Kanyataanna nyaéta Excel ngan ukur ngolah kolom anu dijelaskeun dina rumus, tibatan ngolah sadayana méja. 

Dampak kinerja VLOOKUP () bakal utamana noticeable lamun LKS anjeun ngandung angka nu gede ngarupakeun rumus kawas VLOOKUP () jeung SUM (). cék misah tina VLOOKUP () fungsi diperlukeun pikeun parse unggal nilai dina Asép Sunandar Sunarya. Janten Excel kedah ngolah seueur inpormasi, sareng ieu ngalambatkeun padamelan sacara signifikan.

Conto Rumus 

Kami parantos terang mangpaat tina fungsi ieu, ku kituna urang tiasa ngaléngkah ka bagian anu paling narik: aplikasi pangaweruh dina prakték.

Rumus pikeun milarian ti katuhu ka kenca

Sakumaha anu parantos disebatkeun, VLOOKUP teu tiasa ngalakukeun bentuk pamilarian ieu. Janten, upami nilai anu dipikahoyong henteu aya dina kolom paling kénca, VLOOKUP () moal ngahasilkeun hasil. Fungsi INDEX () sareng MATCH () langkung serbaguna, sareng lokasi nilai henteu maénkeun peran anu ageung pikeun aranjeunna damel.

Salaku conto, urang bakal nambihan kolom pangkat ka sisi kénca méja urang sareng nyobian terang naon pangkat dina hal populasi ibukota Nagara urang.

Dina sél G1, urang nulis nilai nu kapanggih, lajeng nganggo rumus di handap pikeun neangan dina rentang C1: C10 tur mulangkeun nilai pakait tina A2: A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Gancang. Upami anjeun badé nganggo rumus ieu pikeun sababaraha sél, pastikeun anjeun ngalereskeun rentang nganggo alamat mutlak (contona, $ A $ 2: $ A $ 10 jeung $ C $ 2: 4C $ 10).

Indéks beuki kakeunaan beuki kakeunaan  pikeun milarian dina kolom sareng baris

Dina conto di luhur, kami geus dipaké fungsi ieu salaku gaganti VLOOKUP () pikeun balik nilai tina rentang tos siap tina baris. Tapi kumaha upami anjeun kedah milarian matriks atanapi dua sisi?

Sigana pajeulit, tapi rumus pikeun itungan sapertos sami sareng rumus INDEX () MATCH () standar, sareng ngan hiji bédana: MATCH () rumus kudu dipaké dua kali. Kahiji kalina pikeun meunangkeun nomer baris, jeung kadua kalina pikeun meunangkeun nomer kolom:

=INDEX(array, MATCH(nilai pilarian nangtung, kolom pilarian, 0), MATCH(nilai pilarian horizontal, baris pilarian, 0))

Hayu urang tingali tabel di handap ieu sareng cobian ngadamel rumus INDEKS() EXPRESS() EXPRESS() pikeun mintonkeun demografi di nagara husus pikeun taun dipilih.

Nagara sasaran aya dina sél G1 (lookup vertikal) jeung taun target aya dina sél G2 (lookup horizontal). Rumusna bakal katingali sapertos kieu:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Kumaha rumus ieu jalan

Sapertos rumus kompleks anu sanés, aranjeunna langkung gampang kahartos ku cara ngabagi kana persamaan individu. Teras anjeun tiasa ngartos naon fungsi masing-masing individu:

• COCONGAN(G1,A2:A11,0) - milarian nilai (G1) dina kisaran A2: A11 sareng nunjukkeun jumlah nilai ieu, dina kasus urang nyaéta 2;
• Pilarian(G2,B1:D1,0) – néangan nilai (G2) dina rentang B1: D1. Dina hal ieu, hasilna éta 3.

Jumlah baris jeung kolom kapanggih dikirim ka nilai pakait dina rumus INDEX ():

=INDEKS(B2:D11,2,3)

Hasilna, urang boga nilai nu aya dina sél dina simpang 2 jajar jeung 3 kolom dina rentang B2: D11. Sareng rumus nunjukkeun nilai anu dipikahoyong, anu aya dina sél D3.

Pilarian ku sababaraha kaayaan sareng INDEX sareng MATCH

Lamun geus maca pituduh kami pikeun VLOOKUP (), Anjeun meureun geus diusahakeun sababaraha rumus pilarian. Tapi metoda pilarian ieu boga hiji watesan signifikan - kabutuhan pikeun nambahkeun hiji kolom bantu.

Tapi warta anu saé éta Kalayan INDEX () sareng MATCH () anjeun tiasa milarian sababaraha kaayaan tanpa kedah ngédit atanapi ngarobih lembar kerja anjeun.

Ieu rumus milarian multi-kaayaan umum pikeun INDEX () MATCH ():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Catetan: rumus ieu kedah dianggo sareng potong kompas keyboard CTRL+SHIFT+ENTER.

Anggap anjeun kedah milarian nilai anu anjeun milarian dumasar kana 2 kaayaan: nu meuli и Produk.

Ieu merlukeun rumus handap:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Dina rumus ieu, C2:C10 nyaéta rentang dimana panéangan bakal lumangsung, F1 - kaayaan ieu, A2:A10 — nyaeta rentang pikeun ngabandingkeun kaayaan, F2 - kaayaan 2, v2:v10 - rentang pikeun ngabandingkeun kaayaan 2.

Ulah hilap pencét kombinasi di ahir karya jeung rumus CTRL+SHIFT+ENTER - Excel bakal otomatis nutup rumus nganggo kurung keriting, sapertos anu dipidangkeun dina conto:

Upami anjeun henteu hoyong nganggo rumus Asép Sunandar Sunarya pikeun padamelan anjeun, teras tambahkeun INDEX () sanés kana rumus sareng pencét ENTER, éta bakal katingali sapertos conto:

Kumaha rumus ieu jalan

Rumus ieu jalan dina cara nu sarua salaku standar INDEX () MATCH () rumus. Pikeun milarian sababaraha kaayaan, anjeun cukup nyieun sababaraha kaayaan Palsu sareng Leres anu ngagambarkeun kaayaan individu anu leres sareng salah. Lajeng kaayaan ieu dilarapkeun ka sadaya elemen pakait tina Asép Sunandar Sunarya. Rumusna ngarobah argumen Palsu sareng Leres kana 0 sareng 1, masing-masing, sareng ngahasilkeun susunan dimana 1 mangrupikeun nilai anu cocog anu aya dina senar. MATCH () bakal manggihan nilai munggaran nu cocog 1 tur lulus ka rumus INDEX (). Sareng éta, kahareupna bakal uih deui nilai anu dipikahoyong dina garis anu ditangtukeun tina kolom anu dipikahoyong.

Rumus tanpa Asép Sunandar Sunarya gumantung kana kamampuh INDEX () pikeun nanganan aranjeunna sorangan. INDEX kadua () dina rumus cocog falsy (0), ku kituna ngalirkeun sakabéh Asép Sunandar Sunarya kalawan nilai maranéhanana kana rumus MATCH (). 

Ieu katerangan rada lengthy logika balik rumus ieu. Kanggo inpo nu leuwih lengkep baca artikel "INDEKS cocog sareng sababaraha kaayaan".

AVERAGE, MAX sareng MIN dina INDEX sareng MATCH

Excel gaduh fungsi khusus sorangan pikeun milarian rata-rata, maksimal, sareng minimum. Tapi kumaha upami anjeun hoyong kéngingkeun data tina sél anu aya hubunganana sareng nilai-nilai éta? Dina hal ieu AVERAGE, MAX sareng MIN kedah dianggo babarengan sareng INDEX sareng MATCH.

INDEKS cocog sareng MAX

Pikeun milarian nilai panggedéna dina kolom D sareng nampilkeunana dina kolom C, paké rumus: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEKS cocog jeung MIN

Pikeun milarian nilai pangleutikna dina kolom D sareng nampilkeunana dina kolom C, paké rumus ieu:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

SEARCH INDEX sareng SERPENT

Pikeun milarian nilai rata-rata dina kolom D sareng ningalikeun nilai ieu dina C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Gumantung kana kumaha data anjeun ditulis, argumen katilu mun MATCH () boh 1, 0, atawa -1:

• lamun kolom diurutkeun dina urutan naek, set 1 (mangka rumus bakal ngitung nilai maksimum, nu kirang ti atawa sarua jeung nilai rata);
• upami diurutkeun turun, teras -1 (rumusna bakal ngahasilkeun nilai minimum anu langkung ageung atanapi sami sareng rata-rata);
• lamun Asép Sunandar Sunarya lookup ngandung nilai nu persis sarua jeung rata, teras setel ka 0. 

 Dina conto urang, populasi diurutkeun dina urutan nurun, jadi urang nempatkeun -1. Hasilna nyaéta Tokyo, sabab nilai populasi (13,189) pangdeukeutna kana nilai rata-rata (000).

VLOOKUP () ogé tiasa ngalakukeun itungan sapertos kitu, tapi ngan ukur salaku rumus Asép Sunandar Sunarya: VLOOKUP kalayan AVERAGE, MIN sareng MAX..

INDEKS cocog jeung ESND / IFERROR

Anjeun meureun geus noticed nu lamun rumus teu bisa manggihan nilai nu dipikahoyong, eta throws kasalahan # N / A. Anjeun tiasa ngagentos pesen kasalahan standar ku anu langkung informatif. Contona, atur argumen dina rumus Dina XNUMX:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Kalayan rumus ieu, upami anjeun ngasupkeun data anu teu aya dina tabél, formulir bakal masihan anjeun pesen anu ditangtukeun.

Lamun hayang nyekel sagala kasalahan, lajeng iwal Dina XNUMX tiasa dianggo IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Aya nu salah!")

Tapi émut yén kasalahan masking ku cara ieu sanés ide anu saé, sabab kasalahan standar ngalaporkeun pelanggaran dina rumus.

Kami ngarepkeun anjeun mendakan pituduh kami pikeun ngagunakeun fungsi INDEX MATCH () ngabantosan.