5 Alternative Rumus Vlookup Ke Kiri

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

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

Namun demikian tidak perlu khawatir, sebab excel sudah menyediakan banyak sekali 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 melaksanakan VLOOKUP data di sebelah kiri, namun rumus-rumus tersebut juga sanggup dipakai untuk VLOOKUP data ke sebelah kanan. Penekanan pembahasan cari data ke sebelah kiri sebab memang itulah yang biasanya sering menjadi pertanyaan, terutama dari pengguna excel yang gres mengenal atau memakai rumus VLOOKUP.

Contoh Kasus Rumus VLOOKUP Untuk Cari Data Sebelah Kiri

Anggaplah kita mempunyai 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 yakni bagaimana mencari nama karyawan menurut nomor ID yang sudah diketahui menyerupai yang ditunjukan dalam gambar berikut:

Dari gambar diatas, sanggup 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 sanggup dipakai 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 sanggup mendefinisikan sendiri kolom mana yang akan dianggap sebagai kolom pertama dan ke dua oleh fungsi VLOOKUP.

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

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

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

Rumus tersebut sanggup menghasilkan nama yang dicari menyerupai ditunjukan dalam gambar berikut:

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

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

Kemudian sehabis digabung dengan rumus VLOOKUP, maka VLOOKUP akan mencari ID di kolom pertama dan menunjukkan 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, bahu-membahu kita sanggup melaksanakan pencarian data di sebelah kiri.

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

Rumus MATCH(H1,D:D,0) berguna untuk mendapat 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 menunjukkan isu dari baris 3 pada kolom "Nama". Dan jadinya yakni "Dulah"

Cari Data Ke Kiri Menggunakan Rumus INDEX-MATCH

Rumus ini sepertinya 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 jikalau halaman yang sedang anda baca ini ini sanggup 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 sanggup dituliskan sebagai berikut:

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

Fungsi INDEX dipakai untuk mendapat 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 sanggup ditiadakan menyerupai ini:

=INDEX(range,x)

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

Konsep terakhir inilah yang dipakai 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 mendapat nama karyawan sesuai ID yang diketahui.

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

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

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

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

Cari Data Ke Kiri Menggunakan Rumus OFFSET MATCH

Cara kerja rumus ini yakni dengan memakai posisi relative baris data yang dicari dari sel pola dalam fungsi OFFSET. Perhatikan gambar berikut:

Fungsi MATCH dipakai untuk mendapat 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 dipakai oleh fungsi OFFSET untuk mendapat nilai dari sel yang jaraknya 2 baris ke arah bawah dari dari sel B1.

Dan jadinya yakni "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 memilih alamat sel yang berisi data yang dicari, kemudian mendapat 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 dipakai untuk mendapat alamat sel sesuai index baris dan kolom yang diperoleh dari rumus MATCH dan COLUMN. Sehingga jikalau diringkas menjadi =ADDRESS(3,2) , rumus ini menghasilkan nilai $B$3
Fungsi INDIRECT dipakai untuk mendapat nilai dalam alamat sel yang diketahui. Dan nilai yang terdapat dalam sel $B$3 yakni “Dulah”

Ringkasan:

Ada banyak cara untuk menuntaskan kiprah Excel. Termasuk untuk melaksanakan pencarian data di excel. Dalam kasus VLOOKUP ke kiri, kita sanggup memakai beberapa alternative menyerupai 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 agar bermanfaat.

Salam..

Baca juga tutorial Belajar Excel Lainnya: