Cara menggunakan index di excel

Misalnya untuk mengetahui nilai ujian akhir Jeff, siswa ke-6 dalam daftar, Anda dapat menggunakan fungsi INDEX seperti ini:

Catatan: Kisaran E2: E11 adalah tempat ujian akhir terdaftar, sedangkan nomor 6 menemukan nilai ujian dari 6siswa ke.

Di sini mari kita lakukan tes kecil. Untuk rumusnya =INDEKS(B2:E2,3), nilai apa yang akan dikembalikan? --- Ya, itu akan kembali Cina, yang 3rd nilai dalam rentang yang diberikan.

Sekarang kita harus tahu bahwa fungsi INDEX dapat bekerja sempurna dengan rentang horizontal atau vertikal. Tetapi bagaimana jika kita membutuhkannya untuk mengembalikan nilai dalam rentang yang lebih besar dengan beberapa baris dan kolom? Nah, dalam hal ini, kita harus menerapkan nomor baris dan nomor kolom. Misalnya untuk mengetahui negara asal Emily dengan INDEX, kita dapat menemukan nilai dengan nomor baris 8 dan nomor kolom 3 di sel melalui B2 hingga E11 seperti ini:

Sesuai dengan contoh di atas, tentang fungsi INDEX di Excel, Anda harus tahu bahwa:

Namun, untuk database yang sangat besar dengan banyak baris dan kolom, tentu tidak nyaman bagi kita untuk menerapkan rumus dengan nomor baris dan nomor kolom yang tepat. Dan inilah saatnya kita harus menggabungkan penggunaan fungsi MATCH.

Sekarang, mari kita pelajari tentang dasar-dasar fungsi MATCH terlebih dahulu.

Fungsi MATCH di Excel mengembalikan nilai numerik, lokasi item tertentu dalam rentang yang diberikan. Sintaks fungsi MATCH adalah sebagai berikut:

• pencarian_array (wajib) mengacu pada rentang sel tempat Anda ingin MATCH mencari.
• kecocokan_type (opsional), 1, 0 or -1:
• 1(default), MATCH akan menemukan nilai terbesar yang kurang dari atau sama dengan nilai lookup. Nilai-nilai dalam pencarian_array harus ditempatkan dalam urutan menaik.
• 0, MATCH akan menemukan nilai pertama yang persis sama dengan nilai lookup. Nilai-nilai dalam pencarian_array bisa dalam urutan apa pun. (Untuk kasus di mana jenis pencocokan diatur ke 0, Anda dapat menggunakan karakter wildcard.)
• -1, MATCH akan menemukan nilai terkecil yang lebih besar dari atau sama dengan nilai lookup. Nilai-nilai dalam pencarian_array harus ditempatkan dalam urutan menurun.

Misalnya untuk mengetahui posisi Vera dalam daftar nama, Anda dapat menggunakan rumus MATCH seperti ini:

=MATCH("vera",C2:C11,0) >>> kembali 4

Catatan: Fungsi MATCH tidak peka huruf besar/kecil. Hasil "4" menunjukkan bahwa nama "Vera" berada di posisi ke-4 daftar. "0" dalam rumus adalah jenis pencocokan yang akan menemukan nilai pertama dalam larik pencarian yang sama persis dengan nilai pencarian "Vera".

Untuk mengetahui posisi skor “96” berturut-turut dari B2 sampai E2, Anda dapat menggunakan MATCH seperti ini:

=MATCH(96,B2:E2,0) >>> kembali 4

Hal-hal yang harus kita ketahui tentang fungsi MATCH di Excel:

• Fungsi MATCH mengembalikan posisi nilai pencarian dalam larik pencarian, bukan nilai itu sendiri.
• Fungsi MATCH mengembalikan kecocokan pertama jika terjadi duplikat.
• Sama seperti fungsi INDEX, fungsi MATCH juga dapat bekerja dengan rentang vertikal dan horizontal.
• MATCH juga tidak peka huruf besar-kecil.
• Jika nilai pencarian rumus MATCH dalam bentuk teks, sertakan dalam tanda kutip.

Sekarang setelah kita mengetahui tentang penggunaan dasar fungsi INDEX dan MATCH di Excel, mari menyingsingkan lengan baju kita dan bersiap untuk menggabungkan kedua fungsi tersebut.

---

Cara menggunakan fungsi INDEX dan MATCH secara bersamaan

Pada bagian ini, kita akan berbicara tentang keadaan yang berbeda untuk menggunakan fungsi INDEX dan MATCH untuk memenuhi kebutuhan yang berbeda.

Contoh untuk menggabungkan INDEX dan MATCH

Silakan lihat contoh di bawah ini untuk mengetahui bagaimana kita dapat menggabungkan fungsi INDEX dan MATCH:

Misalnya untuk mengetahui Nilai ujian akhir Evelyn, kita harus menggunakan rumus:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> kembali 90

Nah, karena rumusnya mungkin terlihat rumit, mari kita bahas setiap bagiannya.

Seperti yang Anda lihat di atas, yang besar INDEKS rumus berisi tiga argumen:

• susunan: A2: D11 memberitahu INDEX untuk mengembalikan nilai yang cocok dari sel melalui A2 sampai D11.
• baris_num: MATCH("evelyn",B2:B11,0) memberi tahu INDEX baris nilai yang tepat.
• Tentang rumus MATCH, kita dapat menjelaskannya sebagai: untuk mengembalikan posisi nilai pertama yang persis sama dengan "evelyn" di sel dari B2 ke B11 dalam nilai numerik, yaitu 5.
• kolom_num: MATCH("Ujian Akhir",A1:D1,0) memberitahu INDEX kolom nilai yang tepat.
• Tentang rumus MATCH, kita dapat menjelaskannya sebagai: untuk mengembalikan posisi nilai pertama yang persis sama dengan "ujian akhir" di sel dari A1 ke D1 dalam nilai numerik, yaitu 4.

Jadi, Anda dapat melihat rumus besarnya sesederhana yang kami tunjukkan di bawah ini:

=INDEKS(A2: D11,5,4)

Dalam contoh, kami menggunakan nilai hardcode, "evelyn" dan "ujian akhir". Namun, dalam formula yang begitu besar, kami tidak ingin nilai hardcoded karena kami harus mengubahnya setiap kali kami akan mencari sesuatu yang baru. Dalam keadaan seperti itu, kita dapat menggunakan referensi sel untuk membuat rumus menjadi dinamis seperti ini:

=INDEKS(A2: D11,COCOK(G2,B2:B11,0),COCOK(F3,A1:D1,0))

---

INDEX dan MATCH untuk menerapkan pencarian kiri

Sekarang, katakanlah Anda perlu mengetahui kelas Evelyn, bagaimana kita bisa menggunakan INDEX dan MATCH untuk mengetahui jawabannya? Jika Anda memperhatikan, Anda harus memperhatikan bahwa kolom kelas berada di sisi kiri kolom nama, dan yang berada di luar kemampuan fungsi pencarian Excel lainnya, VLOOKUP.

Faktanya, kemampuan pencarian kiri menjadi salah satu aspek di mana kombinasi INDEX dan MATCH lebih unggul daripada VLOOKUP.

Untuk mengetahui Kelas Evelyn, yang perlu Anda lakukan adalah mengubah nilai di sel F3 menjadi "Kelas", dan menggunakan rumus yang sama seperti yang ditunjukkan di atas, fungsi INDEX dan MATCH kemudian akan memberi tahu Anda jawabannya segera:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> kembali A

Jika Anda telah menginstaldipimpin Kutools untuk Excel, addin Excel profesional yang dikembangkan oleh tim kami, Anda juga dapat menerapkan pencarian kiri untuk nilai yang ditentukan dengannya LOOKUP dari Kanan ke Kiri fitur dengan beberapa klik. Untuk menerapkan fitur ini, silakan kunjungi Kutools tab di excel Anda, temukan Rumus grup, dan klik LOOKUP dari Kanan ke Kiri pada daftar drop-down dari PENCARIAN Super. Anda akan melihat kotak dialog pop-up seperti ini:

Klik di sini untuk langkah nyata untuk menerapkan fitur pencarian kiri dengan Kutools for Excel.

---

INDEX dan MATCH untuk menerapkan pencarian dua arah

Sekarang, apakah Anda dapat membuat rumus kombinasi INDEX dan MATCH dengan nilai pencarian dinamis untuk menerapkan pencarian dua arah? Mari kita berlatih membuat rumus pada sel G3, G4, dan G5 seperti gambar di bawah ini:

Berikut adalah jawabannya:

Sel G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Sel G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Sel G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

Catatan: Setelah menerapkan rumus, Anda dapat dengan mudah mendapatkan informasi siswa mana pun dengan mengubah nama di sel G2.

---

INDEX dan MATCH untuk menerapkan pencarian peka huruf besar-kecil

Dari contoh di atas, kita tahu bahwa fungsi INDEX dan MATCH tidak peka huruf besar-kecil. Namun, jika Anda memerlukan rumus untuk membedakan karakter huruf besar dan kecil, Anda dapat menambahkan TEPAT fungsi ke rumus Anda seperti ini:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
Catatan: Ini adalah rumus array yang mengharuskan Anda memasukkan dengan Ctrl + Shift + Enter. Sepasang kurung kurawal kemudian akan muncul di bilah rumus.

Misalnya untuk mengetahui Nilai ujian JIMMY, gunakan fungsi seperti ini:

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> kembali 86

Atau Anda dapat menggunakan referensi sel:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> kembali 86
√Catatan: Jangan lupa masuk dengan Ctrl + Shift + Enter.

---

INDEX dan MATCH untuk menerapkan pencarian dengan beberapa kriteria

Ketika berhadapan dengan database besar dengan beberapa kolom dan keterangan baris, selalu sulit untuk menemukan sesuatu yang memenuhi beberapa kondisi. Dalam hal ini, silakan lihat rumus di bawah ini untuk mencari beberapa kriteria:

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
√ Catatan: Ini adalah rumus array yang mengharuskan Anda memasukkan dengan Ctrl + Shift + Enter. Sepasang kurung kurawal kemudian akan muncul di bilah rumus.

Misalnya untuk mencari nilai ujian akhir Coco Kelas A yang berasal dari India, rumusnya sebagai berikut:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> kembali 88
Catatan: Jangan lupa masuk dengan Ctrl + Shift + Enter.

Nah, bagaimana jika Anda terus-menerus lupa menggunakan Ctrl + Shift + Enter untuk melengkapi rumus sehingga rumus mengembalikan hasil yang salah? Di sini kami memiliki formula yang lebih kompleks, yang dapat Anda selesaikan dengan satu rumus sederhana Enter kunci:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

Untuk contoh yang sama di atas untuk menemukan nilai ujian akhir Coco Kelas A yang berasal dari India, rumus yang hanya membutuhkan yang biasa Enter memukul adalah sebagai berikut:

=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0)) >>> kembali 88

Di sini kita tidak akan menggunakan nilai hardcode, karena kita menginginkan formula universal dalam kasus dengan banyak kriteria. Hanya dengan cara ini, kita dapat dengan mudah mendapatkan hasil yang kita inginkan dengan mengubah nilai pada sel G2, G3, G4 pada contoh di atas.

Dengan Kutools untuk Excel's Fitur Pencarian Multikondisi, Anda dapat mencari nilai tertentu dengan beberapa kriteria dalam beberapa klik. Untuk menerapkan fitur ini, silakan kunjungi Kutools tab di excel Anda, temukan Rumus grup, dan klik Pencarian multi-kondisi pada daftar drop-down dari PENCARIAN Super. Anda kemudian akan melihat kotak dialog pop-up seperti yang ditunjukkan di bawah ini:

Klik di sini untuk langkah-langkah konkret untuk menerapkan fitur pencarian multi-kondisi dengan Kutools for Excel.

---

INDEX dan MATCH untuk menerapkan pencarian di beberapa kolom

Jika kita memiliki spreadsheet Excel dengan kolom berbeda yang berbagi satu keterangan seperti yang ditunjukkan di bawah ini, bagaimana kita bisa mencocokkan nama setiap siswa dengan kelasnya dengan INDEX dan MATCH?

Di sini, izinkan saya menunjukkan cara untuk menyelesaikan tugas dengan alat profesional kami Kutools untuk Excel. Dengan Pembantu Formula, Anda dapat dengan cepat mencocokkan siswa dengan kelas mereka seperti langkah-langkah yang ditunjukkan di bawah ini:

1. Pilih sel tujuan tempat Anda ingin menerapkan fungsi.

2. Di bawah Kutools tab, pergi ke Pembantu Formulaklik Pembantu Formula pada daftar drop-down.

3. Memilih Cari dari Jenis Rumus, lalu klik Buat indeks dan cocokkan di beberapa kolom.

4. a. Klik tanggal 1

tombol di sisi kanan cari_kol untuk memilih sel yang ingin Anda kembalikan nilainya, yaitu nama kelas. (Anda hanya dapat memilih satu kolom atau baris di sini.)
    B. Klik yang ke-2 tombol di sisi kanan Tabel_rng untuk memilih sel agar sesuai dengan nilai yang dipilih cari_kol, yaitu, nama siswa.
    C. Klik yang ke-3 tombol di sisi kanan Nilai lookup untuk memilih sel yang akan dicari, yaitu nama siswa yang ingin Anda cocokkan dengan kelasnya.

5. Klik Ok, Anda akan melihat nama kelas Jimmy muncul di sel tujuan.

6. Sekarang Anda dapat menyeret gagang isian ke bawah untuk mengisi kelas siswa lain.

Klik untuk mengunduh Kutools for Excel untuk uji coba gratis 30 hari.

---

Alat Produktivitas Kantor Terbaik

Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%

• Bilah Formula Super (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
• Gabungkan Sel / Baris / Kolom dan Menyimpan Data; Pisahkan Konten Sel; Gabungkan Baris Duplikat dan Jumlah / Rata-rata... Mencegah Sel Duplikat; Bandingkan Rentang...
• Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
• Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
• Favorit dan Sisipkan Rumus dengan Cepat, Rentang, Grafik dan Gambar; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
• Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
• Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
• Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
• Pengelompokan Tabel Pivot menurut nomor minggu, hari dalam seminggu dan banyak lagi ... Tampilkan Sel yang Tidak Terkunci dan Terkunci dengan warna berbeda; Sorot Sel Yang Memiliki Rumus / Nama...

---

Office Tab - menghadirkan antarmuka tab ke Office, dan membuat pekerjaan Anda jauh lebih mudah

• Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
• Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
• Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!

Index di Excel untuk apa?

Rumus Index itu apa?

Apa fungsi Index dan Match?

Rumus match Excel untuk apa?