VLOOKUP vs INDEX MATCH: Kupas Tuntas!

Pada kesempatan kali ini saya akan membahas tentang Vlookup vs index match.

Setelah menggunakan Excel selama bertahun-tahun, kekuatannya terus mengejutkan saya secara konstan. Salah satu formula terbaik dan favorit pribadi yang disertakan di hampir setiap artikel Excel 101 adalah VLOOKUP. Tetapi bagaimana jika saya memberi tahu Anda bahwa ada dua formula lain, ketika digunakan bersama, yang membuat VLOOKUP cukup usang. Meskipun VLOOKUP sangat baik untuk banyak aplikasi, menggunakan kumpulan rumus INDEX MATCH akan membawa keterampilan Excel Anda ke tingkat berikutnya.

VLOOKUP vs INDEX MATCH

Dasar-dasar VLOOKUP

Sebelum kita benar-benar memahami rumus INDEX MATCH, mari kita lihat apa yang membuat VLOOKUP menjadi alat favorit. Rumus ini digunakan untuk mencari dan mengambil data dalam tabel berdasarkan kriteria pencarian.

V dalam namanya adalah singkatan dari vertikal, yang berarti rumus akan mencari kriteria secara vertikal di kolom pertama data. (Catatan: Excel memiliki fungsi lain, HLOOKUP, yang melakukan hal yang sama, hanya secara horizontal.)

Rumus VLOOKUP berfungsi jika data Anda diatur secara vertikal dan kolom pencarian adalah kolom paling kiri. Mari kita lihat contoh apa yang masuk ke dalam rumus VLOOKUP.

=VLOOKUP (nilai_pencarian, susunan_tabel, jumlah_indeks_kol, [range_lookup])

    • nilai lookup adalah nilai yang akan dicari di kolom paling kiri tabel.
    • tabel_array adalah rentang sel yang berisi data yang sedang dicari.
    • col_index_number adalah nomor kolom dalam tabel relatif terhadap kolom pertama dalam tabel.
    • [range_lookup] menentukan apakah pencocokan tepat atau pencocokan terdekat diperlukan. Excel menggunakan pencocokan tepat secara default jika tidak ditentukan.

vlookup_index_match_basicsPada contoh di atas rumusnya adalah: =VLOOKUP(A4,D6:H10,5)

Itu nilai lookup dari “216” di sel A4 mengembalikan hasil “325i – Sedan” yang merupakan kolom ke-5 dari tabel D6:H10 untuk baris yang sesuai.

Kekurangan VLOOKUP

Sayangnya, berurusan dengan data tidak selalu mudah. Terkadang perubahan tata letak atau kolom tambahan ditambahkan dan memperbarui semua VLOOKUP Anda dapat memakan waktu tergantung pada berapa banyak yang Anda miliki. Mari kita lihat beberapa batasan yang Anda hadapi saat menggunakan VLOOKUP.

Menyisipkan baris atau kolom baru

Rumus VLOOKUP menggunakan apa yang dikenal sebagai referensi kolom statis. Ini adalah cara yang bagus untuk mengatakan bahwa spesifikasi rumus Anda tidak diperbarui saat Anda membuat perubahan tabel. Mengambil contoh yang sama dari atas, jika kita menambahkan kolom untuk Horsepower antara kolom Tahun dan Trim, rumus VLOOKUP sekarang akan menarik hasil dari kolom horsepower, bukan kolom Trim.

Baca Juga:  Cara menggunakan TEXTBEFORE, TEXTAFTER dan TEXTSPLIT di Excel

vlookup_index_match_inserting_new_rows_columnsBukan masalah besar dengan hanya beberapa formula, tetapi ketika memperbarui dalam jumlah besar, Anda akan berisiko data Anda tidak akurat.

Organisasi data

Ketika datang ke rumus VLOOKUP, ada dua titik sakit yang terkait dengan organisasi data. Pertama, data Anda harus disiapkan dengan kolom pencarian di kolom paling kiri tabel Anda. Kedua, jika Anda menggunakan perkiraan kecocokan untuk range_lookup, data perlu diatur dalam urutan menaik. Meskipun kedua aspek ini tampak kecil ketika diterapkan pada tabel dengan jumlah data yang besar, mereka dapat menyebabkan sakit kepala.

Kurangnya fleksibilitas

Rumus VLOOKUP bekerja dengan cara tertentu untuk mencari data. Jika Anda perlu mencari secara horizontal, ada rumus yang sama sekali berbeda untuk itu, yaitu HLOOKUP. Meskipun keduanya berguna, jika Anda harus mempelajari banyak rumus, lebih baik mempelajari rumus yang memberikan manfaat paling banyak secara keseluruhan.

Menghitung kolom

Karena referensi VLOOKUP bersifat statis, Anda biasanya harus menghitung jumlah kolom di tabel Anda untuk mendapatkan hasil yang Anda cari. Ini adalah versi dewasa menghitung dengan jari Anda. Mari kita hadapi itu, kami menggunakan rumus ini untuk mempercepat pekerjaan pencarian melalui data dan waktu itu harus dihabiskan untuk menganalisis hasil dan tidak menghitung kolom.

Mengapa PERTANDINGAN INDEKS?

Di mana VLOOKUP kurang, INDEX MATCH mengambil alih. Semua batasan VLOOKUP diselesaikan dengan menggunakan kumpulan rumus baru ini. Jadi apa sebenarnya rumus INDEX MATCH dan bagaimana cara membuat pencarian melalui data menjadi lebih mudah? Pertama, mari kita lihat bagian berbeda yang membentuk formula.

Bersarang dengan formula bersarang

Saat mempelajari Excel, kebanyakan orang memulai dengan rumus fungsi tunggal yang sederhana. Ini adalah bagian lain dari apa yang membuat VLOOKUP begitu populer adalah kesederhanaannya. Tapi begitu roda latihan lepas, Anda mulai menggunakan formula yang berbeda bersama-sama untuk membuat fungsi yang lebih kompleks menggunakan konsep bersarang.

Baca Juga:  Fungsi Excel AND di Microsoft Excel: Kupas Tuntas

Bersarang berarti menggunakan beberapa rumus yang berdiri sendiri dalam satu rumus. Sebagian besar waktu bersarang digunakan untuk pemformatan bersyarat seperti beberapa rumus IF. Tapi kemungkinan bersarang tidak berhenti di situ. Rumus INDEX MATCH adalah istilah umum untuk menyusun rumus MATCH dalam rumus INDEX, untuk mencari seperti VLOOKUP. Mari kita lihat lebih dalam kedua formula secara terpisah untuk mengurangi kebingungan.

INDEKS

Fungsi INDEX mengembalikan nilai dalam tabel berdasarkan sekumpulan koordinat untuk kolom dan baris. Pikirkan ini seperti koordinat global bujur dan lintang di mana koordinat bujur adalah kolom, dan koordinat lintang adalah baris. Baris pertama pada tabel adalah baris 1, dan kolom pertama pada tabel adalah kolom 1. Berikut ini adalah komponen dari rumus INDEX:

=INDEX(referensi, [row], [column])

    • referensi adalah tabel datanya. Ini bisa berupa rentang tunggal atau seluruh kumpulan data.
    • [row] adalah koordinat baris dalam referensi.
    • [column] adalah koordinat kolom dalam referensi.
    • Catatan: Variabel ke rumus dengan [brackets] adalah opsional. Dalam contoh ini, jika tidak ada baris atau kolom yang ditentukan, rumus INDEX akan mengembalikan seluruh referensi sebagai tabel baru.

Menggunakan contoh berikut, inilah cara kerjanya.

vlookup_index_matchPada contoh di atas rumusnya adalah: =INDEX(I7:I10,2)

    • Itu referensi adalah kisaran I7:I10.
    • Itu [row] value akan mengembalikan baris ke-2 dalam tabel.

Tidak ada koordinat kolom yang dipilih karena hanya ada satu kolom yang dapat dipilih. Seandainya ada dua kolom, rumus INDEX akan mengembalikan semua nilai untuk semua kolom untuk baris yang ditentukan.

Sekarang setelah INDEX diatur, mari kita beralih ke fungsi MATCH untuk menentukan bagaimana kita menentukan nilai baris.

COCOK

Rumus pencocokan mencari nilai tertentu dalam rentang sel, lalu mengembalikan posisi relatif dalam rentang tersebut dalam bentuk koordinat. Pada dasarnya, MATCH adalah versi yang lebih halus dari VLOOKUP atau HLOOKUP yang hanya mengembalikan informasi lokasi dan bukan data sebenarnya. Berikut ini adalah komponen dari rumus MATCH:

=MATCH(nilai_pencarian, array_pencarian, [match_type])

    • nilai lookup adalah nilai yang dicari di lookup_array.
    • lookup_array adalah rentang sel untuk mencari nilai_pencarian.
    • [match_type] adalah kualifikasi. 1 adalah mencari nilai yang lebih besar dari nilai_pencarian, 0 mengembalikan kecocokan yang tepat, dan -1 mengembalikan nilai yang lebih kecil dari nilai_pencarian.
    • Catatan: match_type default ke pencocokan tepat jika dikecualikan.
Baca Juga:  Pengertian XLOOKUP di Microsoft Excel: Kupas Tuntas

Melanjutkan contoh kita, inilah yang MATCH kembalikan.

vlookup_index_matchPada contoh di atas rumusnya adalah: =MATCH(A4,D6:D10,0)
Nilai_pencarian “216” di sel A4 mengembalikan kecocokan persis (dilambangkan dengan nol dalam rumus) dari larik pencarian D6:D10.

Kita melihat bahwa nilai Trim sekarang adalah 3, yang merupakan lokasi 216 dalam array D6:D10.

Menyatukan semuanya

Jadi INDEX digunakan untuk mengembalikan nilai berdasarkan koordinat, yang pada dasarnya adalah variabel. Rumus MATCH mengembalikan variabel berdasarkan di mana kriteria pencarian berada dalam jangkauannya. Ini kemudian mengisi koordinat untuk INDEX dan voila!

Sekarang ketika Anda menambahkan kolom atau memanipulasi data Anda, Anda tidak perlu memperbarui referensi yang merupakan versi Excel dari tongkat di lumpur. Untuk memantapkan pemahaman Anda di sini adalah tampilan rumus dan nilai yang dikembalikan.

vlookup_index_match_formula_viewTampilan rumus dari INDEX MATCH

Pada contoh di atas rumusnya adalah: =INDEX(I6:I10,MATCH(A4,D6:D10,0))

Itu referensi adalah I6:I10

Itu [row] adalah MATCH(A4,D6:D10,0) yang mengembalikan “3” karena nilai lookup adalah nilai ke-3 dalam lookup_array.

Ada dua rentang berbeda yang harus Anda tentukan, satu untuk MATCH dan satu untuk INDEX. Rentang pertama akan menjadi nilai hasil yang ingin Anda kembalikan. Rentang kedua akan menjadi kolom pencarian Anda.
Kedua rentang harus berisi jumlah baris atau kolom yang sama agar berfungsi dengan benar bergantung pada apakah Anda mencari secara vertikal atau horizontal. Jika tidak, rumus akan mengembalikan pesan kesalahan.

vlookup_index_match_regular_viewTampilan reguler INDEX MATCH

Jadi katakan Anda ingin mencari ID, tetapi yang Anda tahu hanyalah Trim? Sepuluh menit yang lalu Anda harus mengatur ulang meja Anda agar VLOOKUP berfungsi. Tapi sekarang Anda lebih tua dan lebih bijaksana Anda tahu INDEX MATCH dapat menangani ini tidak ada masalah.

vlookup_index_match_tutorial

INDEX MATCH tidak tertandingi

Semoga setelah membaca ini, Anda sekarang mengerti mengapa INDEX MATCH jauh lebih unggul daripada VLOOKUP. Anda tidak hanya dapat mengubah data tanpa memperbarui referensi, tetapi Anda juga tidak perlu khawatir mengatur ulang data agar sesuai dengan batasan VLOOKUP.

Tinggalkan komentar