INDEX dan MATCH dengan beberapa array
Katakanlah Anda memiliki beberapa tabel dengan keterangan yang sama seperti yang ditunjukkan di bawah ini, untuk mencari nilai yang cocok dengan kriteria pemberian dari tabel ini mungkin sulit bagi Anda. Dalam tutorial ini, kita akan berbicara tentang cara mencari nilai di beberapa larik, rentang, atau grup dengan mencocokkan kriteria tertentu dengan INDEKS, MATCH dan MEMILIH fungsi.
Bagaimana cara mencari nilai di beberapa array?
Untuk mengetahui pemimpin dari kelompok yang berbeda yang termasuk dalam departemen yang berbeda, Anda dapat menggunakan fungsi CHOOSE terlebih dahulu untuk menargetkan tabel untuk mengembalikan nama pemimpin. Fungsi MATCH kemudian akan mengetahui posisi leader dalam tabel dimana dia berada. Terakhir, fungsi INDEX akan mengambil pemimpin berdasarkan informasi posisi ditambah kolom khusus di mana nama pemimpin terdaftar.
Sintaks umum
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- array_number: Nomor CHOOSE digunakan untuk menunjukkan array dari daftar larik1, larik2,… untuk mengembalikan hasil dari.
- larik1, larik2,…: Array untuk mengembalikan hasil. Berikut mengacu pada tiga tabel.
- nilai lookup: Nilai rumus kombinasi yang digunakan untuk mencari posisi pemimpin yang sesuai. Di sini mengacu pada kelompok yang diberikan.
- pencarian_array: Rentang sel di mana nilai lookup terdaftar. Di sini mengacu pada rentang grup. Catatan: Anda dapat menggunakan rentang grup dari departemen mana pun karena semuanya sama dan kami hanya perlu mendapatkan nomor posisi.
- kolom_num: Kolom yang Anda tunjukkan dari mana Anda ingin mengambil data.
Untuk mengetahui pemimpin Grup D milik Departemen A, silakan salin atau masukkan rumus di bawah ini di sel G5, dan tekan Enter untuk mendapatkan hasil:
=INDEX(PILIH(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),COCOK(F5,$ B $ 5: $ B $ 8,0),2)
Catatan: Tanda dolar ($) di atas menunjukkan referensi absolut, yang berarti nama dan rentang kelas dalam rumus tidak akan berubah saat Anda memindahkan atau menyalin rumus ke sel lain. Setelah Anda memasukkan rumus, seret gagang isian ke bawah untuk menerapkan rumus ke sel di bawah ini, lalu ubah array_number demikian.
Penjelasan rumus
=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): Fungsi CHOOSE mengembalikan 1st array dari tiga array yang tercantum dalam rumus. Jadi itu akan kembali $B$5:$C$8, yaitu rentang data Departemen A.
- COCOK(F5,$B$5:$B$8,0): kecocokan_type 0 memaksa fungsi MATCH untuk mengembalikan posisi kecocokan pertama dari Grup D, nilai dalam sel F5, dalam larik $ B $ 5: $ B $ 8, Yang 4.
- INDEKS(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),COCOK(F5,$B$5:$B$8,0),2) = INDEKS($B$5:$C$8,4,2): Fungsi INDEX mengambil nilai di persimpangan 4baris ke dan 2kolom ke-n dari jangkauan $B$5:$C$8, Yang Emily.
Untuk menghindari perubahan array_number dalam rumus setiap kali Anda menyalinnya, Anda dapat menggunakan kolom pembantu, kolom D. Rumusnya akan seperti ini:
=INDEX(PILIH(D5,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),COCOK(F5,$ B $ 5: $ B $ 8,0),2)
Catatan: Angka-angka 1, 2, 3 di kolom pembantu menunjukkan array1, array2, array3 di dalam fungsi PILIH.
Fungsi terkait
Fungsi INDEX Excel mengembalikan nilai yang ditampilkan berdasarkan posisi tertentu dari rentang atau larik.
Fungsi Excel MATCH mencari nilai tertentu dalam rentang sel, dan mengembalikan posisi relatif dari nilai tersebut.
Fungsi CHOOSE mengembalikan nilai dari daftar argumen nilai berdasarkan nomor indeks yang diberikan. Misalnya, CHOOSE(3,”Apple”,”Peach”,”Orange”) mengembalikan Oranye, nomor indeksnya adalah 3, dan Oranye adalah nilai ketiga setelah nomor indeks dalam fungsi tersebut.
Rumus Terkait
Nilai pencarian dari lembar kerja atau buku kerja lain
Jika Anda tahu cara menggunakan fungsi VLOOKUP untuk mencari nilai di lembar kerja, nilai vlookup dari lembar kerja atau buku kerja lain tidak akan menjadi masalah bagi Anda.
Vlookup dengan nama sheet dymanic
Dalam banyak kasus, Anda mungkin perlu mengumpulkan data di beberapa lembar kerja untuk ringkasan. Dengan kombinasi fungsi VLOOKUP dan fungsi INDIRECT, Anda bisa membuat rumus untuk mencari nilai tertentu di seluruh lembar kerja dengan nama lembar dinamis.
Pencarian multi-kriteria dengan INDEX dan MATCH
Saat berhadapan dengan database besar dalam spreadsheet Excel dengan beberapa kolom dan keterangan baris, selalu sulit untuk menemukan sesuatu yang memenuhi beberapa kriteria. Dalam hal ini, Anda dapat menggunakan rumus array dengan fungsi INDEX dan MATCH.
Alat Produktivitas Kantor Terbaik
Kutools for Excel - Membantu Anda Menonjol Dari Kerumunan
Kutools for Excel Membanggakan Lebih dari 300 Fitur, Memastikan Apa yang Anda Butuhkan Hanya Dengan Sekali Klik...
Tab Office - Aktifkan Pembacaan dan Pengeditan dengan Tab di Microsoft Office (termasuk Excel)
- Satu detik untuk beralih di antara lusinan dokumen terbuka!
- Kurangi ratusan klik mouse untuk Anda setiap hari, ucapkan selamat tinggal pada tangan mouse.
- Meningkatkan produktivitas Anda sebesar 50% saat melihat dan mengedit banyak dokumen.
- Menghadirkan Tab Efisien ke Office (termasuk Excel), Sama Seperti Chrome, Edge, dan Firefox.
