Pencocokan Indeks: Alternatif yang Lebih Baik untuk Vlookup

VLOOKUP adalah salah satu fungsi yang membuat Excel hebat. Meskipun banyak yang mungkin tidak menganggapnya intuitif pada awalnya, itu segera menjadi sifat kedua dan sangat diperlukan. Beberapa bahkan menemukan itu mengubah hidup. VLOOKUP memiliki masalah, misalnya, hanya membaca dari kiri ke kanan. Jika Anda perlu mencari nilai di sebelah kiri kolom referensi, Anda harus mengatur ulang lembar kerja Anda untuk menggunakan fungsi tersebut.

VLOOKUP juga membuat banyak kolom saat sebenarnya Anda hanya menggunakan dua di antaranya. sintaks standar hanya mereferensikan nilai kolom relatif, jika Anda memodifikasi tabel, Anda sekali lagi kurang beruntung dan perlu melakukan sedikit reorganisasi dan penulisan ulang fungsi, yang bisa sangat membosankan di buku kerja besar. Ini juga menimbulkan masalah dalam menyalin fungsi ke kolom lain.

Ada alternatifnya, INDEX + MATCH. Tidak hanya mereplikasi fungsionalitas tetapi memiliki fasilitas dan bonus sendiri. Ini adalah sesuatu yang mungkin belum pernah Anda dengar sebelumnya (terutama jika Anda tidak membaca tentang Excel untuk bersenang-senang) tetapi pada akhir artikel ini, Anda akan dapat mempraktikkannya dan menuai hasilnya.

Mengurai INDEX + MATCH

Sesuatu yang diabaikan oleh banyak pengguna adalah bahwa fungsi Excel dapat dirangkai bersama dan digabungkan untuk membuat sesuatu yang lebih kuat daripada bagian-bagian penyusunnya. Setiap fungsi membutuhkan argumen, jika Anda membutuhkan lebih banyak kekuatan atau fungsionalitas yang diperluas, argumen ini dapat dibuat dari fungsi lain.

INDEKS

=INDEX(array, nomor baris)

Fungsi INDEX mengambil larik pengguna, kumpulan nilai seperti kolom, dan mengembalikan nilai sel pada posisi tertentu. Misalnya, saya memiliki kolom data yang merekam posisi dari balapan yang diadakan di beberapa titik di masa lalu. Saya ingin tahu driver mana yang selesai di tempat ketiga, untuk menemukan ini saya cukup masuk,

=INDEX(B2:B6, 3)

dan ini akan mengembalikan Rusty Shackleford.

Sederhana tapi bermanfaat. Kami sekarang memiliki kemampuan untuk mengembalikan nilai dalam array.

COCOK

=MATCH(nilai pencarian, array pencarian, jenis pencocokan)

MATCH() adalah fungsi sederhana lainnya, terkait dengan fungsi INDEX yang baru saja kita bahas. Alih-alih mengembalikan nilai, itu malah mengembalikan angka yang menandakan posisi relatif dari suatu nilai dalam array. MATCH membutuhkan argumen berikut; nilai, larik yang akan dicari, dan jenis pencocokan. Jenis pencocokan biasanya akan disetel ke 0 tetapi tergantung pada penggunaan Anda. 0 menandakan Anda hanya menginginkan kecocokan persis, 1 atau -1 menandakan jika tidak ada kecocokan persis Anda menginginkan nilai terdekat berikutnya.

Dengan menggunakan contoh balapan kami di atas, saya tahu pembalap yang harus saya temukan tetapi saya tidak tahu posisinya di klasemen akhir balapan. Karena posisinya relatif, saya perlu memastikan bahwa saya menutupi seluruh hasil mulai dari posisi pertama.

=MATCH(“Kilgore Trout”, B2:B6, 0)

Ini akan mengembalikan nilai 4. Karena kita mulai dari atas, kita tahu Trout finis di posisi keempat dalam balapan khusus ini.

PERTANDINGAN INDEKS

Mungkin Anda melihat ke mana tujuan kita sekarang atau mungkin Anda melompat ke depan untuk mendapatkan hal-hal yang baik. Dengan menggunakan INDEX + MATCH, kita dapat menggabungkan dua fungsi sederhana ini untuk membuat sesuatu yang serupa tetapi lebih fleksibel daripada VLOOKUP tepercaya kita. Jangan khawatir jika Anda perlu membaca ini beberapa kali, ini bisa menjadi sedikit rumit pada awalnya. Setelah Anda memahami apa yang terjadi meskipun sangat mudah untuk membangunnya kembali dari komponen.

INDEX mengembalikan nilai tertentu. Ini adalah fungsi utama VLOOKUP, diberikan satu set parameter; Anda dapat dengan cepat menemukan nilai yang dibutuhkan. Sekarang satu-satunya hal yang kita lewatkan adalah cara untuk menemukan posisi baris. Jika Anda harus menemukan ini secara manual, Anda kehilangan utilitas suatu fungsi. Dengan menggunakan MATCH dalam argumen kedua, kita dapat mengambil outputnya, posisi relatif, dan memasukkannya ke dalam fungsi Indeks, memecahkan masalah itu. Sekarang kita memiliki sesuatu yang melakukan hal yang sama persis seperti VLOOKUP, hanya dengan mengetik lebih sedikit.

Mempraktikkan Fungsinya

Dengan menggunakan tabel di bawah, kami tertarik untuk mencari tahu jenis produk apa yang dikaitkan dengan ID produk tertentu.

Untuk menggunakan INDEX MATCH kita mulai dengan INDEX karena pada akhirnya kita menginginkan nilai tertentu yang dikembalikan daripada posisi. Di dalam INDEX kami menyertakan MATCH untuk mengembalikan posisi fungsi INDEX kami.

=INDEX(A2:A6,MATCH(8316,B2:B6,0))

Apa yang terjadi di sini adalah saya akan mencari kolom A untuk nilai baris di kolom B, yang disediakan oleh fungsi Match.

Di dalam fungsi pencocokan, saya menentukan bahwa saya perlu melihat kode ID Produk di B2:B6, dan mengirim kembali posisi nilai 8316. Dalam hal ini, berada di posisi 4 yang setara dengan penulisan

=INDEX(A2:A6, 4)

Excel menjalankan fungsi dan menemukan posisi dalam larik nomor ID yang ditentukan. Dibutuhkan posisi itu dan mencari baris yang sama di kolom A kami, mengembalikan jenis truk untuk ID spesifik. Jadi, pada akhirnya, kami mendapatkan hasil yang sama dengan sedikit lebih banyak fleksibilitas dalam input.

Manfaat Fungsi

Kekuatan INDEX + MATCH berasal dari pemilihan data yang lebih kecil. Jika Anda memiliki 15 kolom tetapi Anda hanya menggunakan angka 1 dan 15, mengapa menarik yang lainnya? Ini dapat membantu mempercepat waktu berjalan untuk kumpulan data besar.

Fungsionalitas kedua yang ditingkatkan yang dapat dibaca Indeks ke kiri atau ke kanan, di mana pun Anda meletakkan kolom, INDEX tidak peduli apakah itu di kiri atau kanan larik awal Anda, tidak seperti VLOOKUP. Jika Anda melihat kembali contoh di atas, itulah yang kami lakukan, bekerja dari kanan ke kiri. Fungsi ini juga dapat berfungsi sebagai pemeriksaan jaminan kualitas karena Anda harus secara manual menentukan dua kolom yang Anda butuhkan daripada memasukkan nomor kolom relatif di suatu tempat dalam pilihan Anda.

Butuh lebih banyak visual? Tonton rekaman layar dari contoh Pencocokan Indeks tempat kami memasukkan url final ke dalam template salinan iklan.

Kesimpulan

Silakan dan bermain-main dengan INDEX + MATCH. Ini adalah fungsi yang sangat kuat yang dapat meningkatkan kemampuan pencarian Anda. Setelah klik, saya yakin Anda akan dijual dan tidak pernah melihat ke belakang. Selalu ada sedikit kurva belajar ketika mencoba sesuatu yang baru, berkomitmen untuk menggunakan Pencocokan Indeks untuk sementara waktu. Ini akan menjadi kikuk pada awalnya tetapi Anda akan segera melihat manfaatnya.

———-
Posting diperbarui oleh Jenna Kelly (tanggal posting sebelumnya: 2/3/14)