5 Fakta Tentang Vlookup Excel Yang Mungkin Belum Anda Ketahui

Rumus VLOOKUP Excel saya rasa banyak yang sudah mengetahui dan menggunakan fitur ini untuk keperluan perhitungan atau analisis data. Namun, ada beberapa hal yang mungkin belum banyak diketahui oleh pengguna Excel tentang VLOOKUP yang bisa dibilang “gila” namun tetap benar. Berikut ini adalah 5 hal gila tentang VLOOKUP Excel yang perlu Anda ketahui:

FAKTA 1 : INDEX MATCH ternyata lebih baik dari VLOOKUP

Meskipun VLOOKUP sangat berguna dalam menemukan data dari tabel besar, namun fungsi INDEX MATCH bisa dikatakan lebih canggih. Gabungan rumus INDEX dan MATCH bisa mengambil nilai dari data yang lebih cepat daripada VLOOKUP. Fungsi INDEX MATCH juga lebih fleksibel, karena dapat digunakan pada data yang tidak terurut dan pada data yang memiliki banyak nilai yang sama.

Berikut beberapa alasan mengapa INDEX MATCH lebih baik daripada VLOOKUP:

• Kemampuan fleksibilitas: Dalam VLOOKUP, Anda harus selalu mencari nilai di kolom yang ada di sebelah kanan dari kolom referensi, sedangkan dalam INDEX MATCH, Anda dapat mencari nilai di kolom mana pun dalam jangkauan data yang telah ditentukan. Hal ini memberikan fleksibilitas yang lebih besar saat melakukan pencarian nilai.
• Konsistensi: Dalam VLOOKUP, jika kolom referensi dipindahkan ke posisi lain, maka formula akan rusak dan tidak akan mengembalikan nilai yang diinginkan. Namun, dalam INDEX MATCH, selama rentang data tetap sama, Anda dapat memindahkan kolom referensi ke posisi lain tanpa merusak formula.
• Kecepatan: INDEX MATCH dapat lebih cepat dalam melakukan pencarian nilai dalam rentang data yang besar karena teknik pencariannya lebih efisien dibandingkan dengan VLOOKUP.
• Kemampuan multi-kriteria: Dalam VLOOKUP, Anda hanya dapat mencari nilai dengan satu kriteria. Namun, dalam INDEX MATCH, Anda dapat mencari nilai dengan lebih dari satu kriteria dengan mudah.
• Memperbaiki data yang rusak: Dalam VLOOKUP, jika data referensi berubah atau rusak, maka formula akan merusak atau menghasilkan nilai yang salah. Namun, dalam INDEX MATCH, Anda dapat menangani masalah data yang rusak dengan lebih mudah karena teknik pencariannya lebih fleksibel.

Misalnya, kita memiliki tabel data produk seperti di bawah ini:

No	Nama Produk	Harga
1	Produk A	5000
2	Produk B	7500
3	Produk C	10000

Kita ingin mencari harga produk dengan nomor urut 2 menggunakan INDEX MATCH. Pertama-tama, kita buat nama untuk kolom-kolom di tabel tersebut. Klik seluruh tabel, kemudian di tab “Design” di menu Ribbon, klik “Table Tools” > “Properties” > “Table Name”, lalu beri nama untuk tabel tersebut, misalnya “tabel_produk”.

Selanjutnya, kita buat juga nama untuk kolom-kolom di tabel tersebut. Klik pada header kolom “No”, lalu di kotak nama di sebelah kiri atas, ketik “no_produk” dan tekan Enter. Lakukan hal yang sama untuk kolom “Nama Produk” dan “Harga” dengan memberi nama “nama_produk” dan “harga_produk”.

Setelah itu, kita bisa menggunakan rumus INDEX MATCH untuk mencari harga produk nomor urut 2. Rumusnya adalah:

=INDEX(tabel_produk[harga_produk],MATCH(2,tabel_produk[no_produk],0))

Penjelasan rumus di atas:

• tabel_produk[harga_produk] akan mengambil seluruh isi kolom “Harga” di tabel “tabel_produk”.
• MATCH(2,tabel_produk[no_produk],0) akan mencari baris di mana isi kolom “No” adalah 2 di tabel “tabel_produk”.
• INDEX(...) akan mengambil nilai di kolom “Harga” yang sesuai dengan baris yang ditemukan di atas.

Hasilnya akan menampilkan 7500, yang merupakan harga produk nomor urut 2. Dengan INDEX MATCH, kita bisa mencari nilai di dalam tabel dengan lebih fleksibel, tidak terbatas pada kolom pertama saja seperti di VLOOKUP.

FAKTA 2 : VLOOKUP ternyata dapat menggunakan lebih dari satu kolom kunci

VLOOKUP hanya bisa digunakan pada data yang memiliki satu kolom kunci. Katanya. Namun tahukan anda, ada trik yang bisa digunakan untuk mengambil data dari tabel yang memiliki lebih dari satu kolom kunci. Cara ini melibatkan penggunaan fungsi CONCATENATE atau operator ampersand (&) untuk menggabungkan beberapa kolom kunci menjadi satu kolom, dan kemudian menggunakan VLOOKUP pada kolom gabungan tersebut.

Berikut ini adalah contoh penggunaan VLOOKUP pada tabel dengan dua kolom kunci, menggunakan fungsi CONCATENATE atau operator ampersand (&):

A	B	C
ID 1	Apple	10
ID 2	Orange	15
ID 3	Banana	20
ID 4	Mango	30

Misalkan kita ingin mencari nilai dari kolom C berdasarkan dua kolom kunci, yaitu ID dan jenis buah. Kita bisa menggunakan CONCATENATE atau operator & untuk menggabungkan kedua kolom tersebut menjadi satu kolom:

A	B	C	D
ID 1	Apple	10	ID 1Apple
ID 2	Orange	15	ID 2Orange
ID 3	Banana	20	ID 3Banana
ID 4	Mango	30	ID 4Mango

Kita bisa menggunakan rumus CONCATENATE atau operator & untuk menggabungkan kolom A dan B menjadi kolom D seperti berikut:

• D2: =CONCATENATE(A2,B2)
• D3: =CONCATENATE(A3,B3)
• D4: =CONCATENATE(A4,B4)
• D5: =CONCATENATE(A5,B5)

Kemudian, kita dapat menggunakan VLOOKUP untuk mencari nilai pada kolom C berdasarkan nilai yang ada pada kolom gabungan D, dengan rumus sebagai berikut:

• Misalkan kita ingin mencari nilai untuk ID 2 dan jenis buah Orange pada kolom C, maka kita dapat menggunakan rumus: =VLOOKUP(CONCATENATE(“ID 2″,”Orange”),A2:C5,3,FALSE)
• Hasil yang dihasilkan adalah nilai 15, yang merupakan nilai pada kolom C untuk ID 2 dan jenis buah Orange.

Dengan menggunakan teknik ini, kita dapat menggunakan VLOOKUP untuk mencari nilai pada tabel dengan lebih dari satu kolom kunci.

Fakta 3 :VLOOKUP dapat di gunakan pada data yang tidak berurutan

VLOOKUP biasanya hanya bisa digunakan pada data yang terurut berdasarkan kolom kunci. Namun, jika Anda ingin menggunakan VLOOKUP pada data yang tidak terurut, ada trik yang bisa digunakan. Trik ini melibatkan penggunaan fungsi SMALL dan ROW untuk membuat indeks data yang terurut, dan kemudian menggunakan VLOOKUP pada indeks tersebut.

Berikut adalah contoh penggunaan VLOOKUP pada data yang tidak terurut dengan menggunakan fungsi SMALL dan ROW:

Misalkan kita memiliki data penjualan berikut ini:

Nama Barang	Tanggal	Jumlah
Produk A	01/01/2022	10
Produk C	03/01/2022	20
Produk B	02/01/2022	15
Produk A	05/01/2022	25
Produk B	04/01/2022	30

Kita ingin mencari total penjualan dari setiap produk, namun data tidak terurut berdasarkan Nama Barang. Berikut adalah langkah-langkah yang dapat diikuti:

1. Buat tabel baru yang berisi nama produk dan total penjualan:

Nama Barang	Total Penjualan
Produk A
Produk B
Produk C

2. Masukkan rumus VLOOKUP pada kolom Total Penjualan untuk mencari total penjualan setiap produk. Rumusnya adalah:

=SUMIF(A:B, F2, C:C)

Keterangan:

• A:B adalah range Nama Barang dan Tanggal di tabel data penjualan.
• F2 adalah sel pada kolom Nama Barang pada tabel baru yang sedang dianalisis.
• C:C adalah range Jumlah pada tabel data penjualan.

Namun, karena data tidak terurut berdasarkan Nama Barang, rumus tersebut akan memberikan hasil yang salah. Oleh karena itu, kita perlu menggunakan fungsi SMALL dan ROW untuk membuat indeks data yang terurut berdasarkan Nama Barang.

3. Buat tabel baru untuk membuat indeks data yang terurut berdasarkan Nama Barang:

No.	Nama Barang	Baris Data
1	Produk A
2	Produk B
3	Produk C

4. Masukkan rumus pada kolom Baris Data untuk membuat indeks data yang terurut. Rumusnya adalah:

=SMALL(IF(A:A=F2, ROW(A:A)-ROW(A2)+1), ROW()-ROW(F$3)+1)

Keterangan:

• A:A adalah range Nama Barang pada tabel data penjualan.
• F2 adalah sel pada kolom Nama Barang pada tabel baru yang sedang dianalisis.
• ROW()-ROW(F$3)+1 digunakan untuk membuat indeks nomor pada tabel baru, dimulai dari angka 1.

5. Masukkan rumus VLOOKUP pada kolom Total Penjualan di tabel baru untuk mencari total penjualan setiap produk. Rumusnya adalah:

=SUMIF($A:$A, G2, $C:$C)

Keterangan:

• $A:$A adalah range Nama Barang pada tabel data penjualan.
• G2 adalah sel pada kolom Nama Barang pada tabel baru yang sedang dianalisis.
• $C:$C adalah range Jumlah pada tabel data penjualan.

Dengan menggunakan fungsi SMALL dan ROW, kita dapat mencari total penjualan dari setiap produk pada tabel yang tidak terurut.

Fakta 4 : VLOOKUP ternyata mampu mencari data berdasarkan pola atau karakter tertentu

Salah satu kekurangan VLOOKUP adalah ketidakmampuannya untuk mencari data berdasarkan pola atau karakter yang tidak diketahui secara pasti. Namun, dengan menggunakan wildcard seperti asterisk (*) atau tanda tanya (?) dalam kolom kunci, Anda dapat mencari data dengan lebih fleksibel. Misalnya, jika Anda ingin mencari data yang mengandung kata “apple” di dalamnya, Anda dapat menggunakan formula VLOOKUP berikut:

=VLOOKUP(“apple“, A2:B10, 2, FALSE)

Formula ini akan mencari data di kolom A yang mengandung kata “apple” di dalamnya, dan mengembalikan nilai dari kolom B.

Fakta 5 : VLOOKUP mampu mengisi nilai kosong

Jika Anda memiliki data yang tidak lengkap dan ingin mengisi nilai kosong di dalamnya, Anda dapat menggunakan VLOOKUP untuk melakukan tugas ini secara otomatis. Misalnya, jika Anda memiliki data penjualan bulanan yang tidak lengkap, dan ingin mengisi nilai kosong dengan data dari bulan sebelumnya, Anda dapat menggunakan formula VLOOKUP berikut:

=IF(ISBLANK(B2),VLOOKUP(A2,$A$1:$B$10,2,FALSE),B2)

Formula ini akan mengambil nilai dari bulan sebelumnya menggunakan VLOOKUP, jika nilai bulan ini kosong, dan akan menampilkan nilai bulan ini jika tidak kosong. Dengan cara ini, Anda dapat dengan mudah mengisi nilai kosong tanpa harus melakukannya secara manual.

Demikianlah 5 hal gila tentang VLOOKUP Excel yang bisa membantu Anda melakukan perhitungan atau analisis data dengan lebih efektif dan efisien. Jangan takut untuk mencoba trik-trik di atas dan temukan cara baru untuk meningkatkan produktivitas Anda di Excel!