5 Alternative Rumus VLOOKUP Ke Kiri

Perlu Rumus excel untuk cari data? VLOOKUP jawabannya. Namun VLOOKUP saja masih belum cukup, terutama jika kita ingin cari data ke sebelah kiri.

Kenapa?... Karena ternyata fungsi ini masih memiliki kelemahan, yaitu hanya dapat mencari data dari kiri ke kanan tabel. Sedangkan dalam prakteknya, struktur atau susunan kolom dalam tabel tidak selalu bisa diprediksi dan tidak selalu cocok dengan kondisi ideal penggunaan formula VLOOKUP.

Namun demikian tidak perlu khawatir, karena excel sudah menyediakan berbagai alternatif solusi pencarian data. Dan atas dasar hal tersebut juga, maka catatan pelajaran excel ini dipersiapkan untuk membahas 5 alternative rumus vlookup data di sebelah kiri.

Adapun kelima alternative rumus cari data tersebut adalah:

1. Rumus VLOOKUP CHOOSE
2. Rumus HLOOKUP MATCH
3. Rumus INDEX MATCH
4. Rumus OFFSET MATCH
5. Rumus INDIRECT ADDRESS MATCH COLUMN

Perlu dicatat, meskipun contoh-contoh rumus yang disampaikan dalam pembahasan ini ditujukan untuk melakukan VLOOKUP data di sebelah kiri, namun rumus-rumus tersebut juga dapat digunakan untuk VLOOKUP data ke sebelah kanan. Penekanan pembahasan cari data ke sebelah kiri karena memang itulah yang biasanya sering menjadi pertanyaan, terutama dari pengguna excel yang baru mengenal atau menggunakan rumus VLOOKUP.

Contoh Kasus Rumus VLOOKUP Untuk Cari Data Sebelah Kiri

Anggaplah kita memiliki sebuah tabel berisi data karyawan pada kolom A s.d E, dengan susunan label kolom dari kiri ke kanan: "No", "Nama", "Umur", "ID", dan "Alamat".

Tugas selanjutnya adalah bagaimana mencari nama karyawan berdasarkan nomor ID yang sudah diketahui seperti yang ditunjukan dalam gambar berikut:

Studi kasus formula vlookup data di kiri

Dari gambar diatas, dapat dilihat bahwa data yang diketahui (ID) terletak di kolom D, sedangkan data yang di diinginkan (Nama) terletak di kolom B. Artinya data yang dicari terletak di sebelah kiri kolom acuan.

Jadi.. rumus apa yang dapat digunakan untuk mencari nama karyawan pada kolom yang terletak di sebelah kiri kolom berisi nomor ID ?

Mari kita bahas satu persatu...

Cari Data Ke Kiri Menggunakan Rumus VLOOKUP-CHOOSE

Dengan fungsi CHOOSE, ternyata kita bisa mendefinisikan sendiri kolom mana yang akan dianggap sebagai kolom pertama dan ke dua oleh fungsi VLOOKUP.

Oleh karenanya kita akan menggunakan fungsi CHOOSE untuk menjadikan kolom "ID" sebagai kolom pertama , dan kolom "Nama" sebagai kolom kedua.

Adapun formula pencarian data di sebeah kiri dapat dituliskan sebagai berikut:

=VLOOKUP(H1,CHOOSE({1,2},D:D,B:B),2,0)

Rumus tersebut dapat menghasilkan nama yang dicari seperti ditunjukan dalam gambar berikut:

Perhatikan kembali fragmen rumus CHOOSE({1,2},D:D,B:B)

Dalam hal ini fungsi CHOOSE digunakan untuk menjadikan kolom D (kolom berisi data ID) sebagai kolom pertama dan kolom B (kolom berisi data Nama) sebagai kolom kedua.

Kemudian setelah digabung dengan rumus VLOOKUP, maka VLOOKUP akan mencari ID di kolom pertama dan memberikan hasil data nama dari kolom ke-2, pada baris yang sama dengan baris sel berisi ID yang diketahui.

Cari Data Ke Kiri Menggunakan Rumus HLOOKUP-MATCH

Dengan membalik orientasi vertikal menjadi horizontal, sebenarnya kita bisa melakukan pencarian data di sebelah kiri.

=HLOOKUP("Nama",A:E,MATCH(H1,D:D,0),0)

Rumus MATCH(H1,D:D,0) berguna untuk mendapatkan angka baris sebuah sel di kolom D yang berisi nilai ID yang diketahui. Rumus ini menghasilkan angka 3 yaitu nomor baris sel yang berisi nilai ID=13043

Kemudian rumus HLOOKUP akan mencari posisi kolom "Nama" pada baris pertama dalam range “A:E”. Selanjutnya memberikan informasi dari baris 3 pada kolom "Nama". Dan hasilnya adalah "Dulah"

Cari Data Ke Kiri Menggunakan Rumus INDEX-MATCH

Rumus ini tampaknya merupakan rumus terpopuler alternative VLOOKUP ke kiri. Terbukti dengan banyaknya tutorial yang membahas hal ini. Coba saja cari kata kunci 'vlookup ke kiri' di Google. Maka kebanyakan link di halaman pertama google akan mengarah ke halaman web/blog yang menjelaskan INDEX MATCH, bukannya menjelaskan VLOOKUP.

Terkecuali jika halaman yang sedang anda baca ini ini bisa muncul di halaman pertama Google (penuh harap… 😇😇 ), maka mungkin akan menambah warna sendiri mengenai cara pencarian data di sebelah kiri….

Baiklah, selanjutnya rumus INDEX MATCH dalam contoh kasus ini dapat dituliskan sebagai berikut:

=INDEX(B:B,MATCH(H1,D:D,0))

Fungsi INDEX digunakan untuk mendapatkan nilai dari Sel sesuai index baris dan kolom dalam range yang ditentukan.

Misalnya:

=INDEX(range,x,y)

Maka rumus tersebut akan menghasilkan nilai dari baris ke-x dan kolom ke-y dalam range. nomor kolom bersifat opsional sehingga dapat ditiadakan seperti ini:

=INDEX(range,x)

Rumus tersebut akan menghasilkan nilai dari baris ke-x ,kolom ke-1 dalam range.

Konsep terakhir inilah yang digunakan dalam fungsi INDEX-MATCH untuk mencari data di sebelah kiri. Sebenarnya tidak mesti di sebelah kiri sich, mencari data ke kanan pun boleh.

Kembali ke contoh rumus INDEX MATCH untuk mendapatkan nama karyawan sesuai ID yang diketahui.

=INDEX(B:B,MATCH(H1,D:D,0))

Fungsi MATCH digunakan untuk mencari posisi baris sel pada kolom D yang berisi nilai ID yang diketahui (sesuai yang diisi pada sel H1). Dan hasilnya adalah angka 3 (nomor baris sel berisi ID=13043)

Kemudian fungsi INDEX menggunakan angka 3 tersebut untuk mengambil data padanannya pada kolom B

Hasilnya adalah "Dulah" yaitu data dalam sel yang terletak pada baris 3 kolom B.

Cari Data Ke Kiri Menggunakan Rumus OFFSET MATCH

Cara kerja rumus ini adalah dengan menggunakan posisi relative baris data yang dicari dari sel acuan dalam fungsi OFFSET. Perhatikan gambar berikut:

Fungsi MATCH digunakan untuk mendapatkan angka baris sel yang berisi nilai ID yang diketahui pada range D:D. Hasil dari rumus MATCH ini dikurangi angka 1 untuk menyesuaikan angka posisi relative dari baris pertama (sel B1). Fungsi MATCH dalam contoh menghasilkan bilangan 3, kemudian 3 dikurangi 1 menjadi 2.

Hasilnya kemudian digunakan oleh fungsi OFFSET untuk mendapatkan nilai dari sel yang jaraknya 2 baris ke arah bawah dari dari sel B1.

Dan hasilnya adalah "Dulah" yaitu data dalam sel yang jaraknya 2 baris ke arah bawah dari sel B1.

Cari Data Ke Kiri Menggunakan Rumus INDIRECT - ADDRESS - MATCH - COLUMN

Nah, kalau rumus ini bekerja dengan cara menentukan alamat sel yang berisi data yang dicari, kemudian mendapatkan nilainya.

Rumus MATCH(H1,D:D,0) menghasilkan angka 3 yaitu posisi baris sel yang berisi ID 13043
Rumus COLUMN(B:B) menghasilkan angka 2 yaitu nomur urut kolom B dalam lembar kerja excel
Fungsi ADDRESS digunakan untuk mendapatkan alamat sel sesuai index baris dan kolom yang diperoleh dari rumus MATCH dan COLUMN. Sehingga jika diringkas menjadi =ADDRESS(3,2) , rumus ini menghasilkan nilai $B$3
Fungsi INDIRECT digunakan untuk mendapatkan nilai dalam alamat sel yang diketahui. Dan nilai yang terdapat dalam sel $B$3 adalah “Dulah”

Ringkasan:

Ada banyak cara untuk menyelesaikan tugas Excel. Termasuk untuk melakukan pencarian data di excel. Dalam kasus VLOOKUP ke kiri, kita bisa menggunakan beberapa alternative seperti 5 contoh rumus yang sudah dijelaskan yaitu: kombinasi VLOOKUP – CHOOSE, kombinasi HLOOKUP-MATCH, kombinasi INDEX-MATCH, kombinasi OFFSET-MATCH dan kombinasi INDIRECT-ADDRESS-MATCH-COLUMN.

Demikian semoga bermanfaat.

Salam..

Baca juga tutorial Belajar Excel Lainnya: