Perlu Rumus excel untuk cari data? VLOOKUP jawabannya. Namun VLOOKUP saja masih belum cukup , khususnya kalau kita ingin cari data ke sebelah kiri.
Kenapa?… Karena ternyata fungsi ini masih memiliki kehabisan , yakni cuma sanggup mencari data dari kiri ke kanan tabel. Sedangkan dalam prakteknya , struktur atau susunan kolom dalam tabel tidak senantiasa sanggup diprediksi dan tidak senantiasa cocok dengan keadaan ideal penggunaan formula VLOOKUP.
Namun demikian tidak perlu kalut , alasannya yakni excel sudah menawarkan banyak sekali alternatif penyelesaian penelusuran data. Dan atas dasar hal tersebut juga , maka catatan pelajaran excel ini disediakan 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 , walaupun contoh-contoh rumus yang disampaikan dalam pembahasan ini ditujukan untuk melakukan VLOOKUP data di sebelah kiri , tetapi rumus-rumus tersebut juga sanggup digunakan untuk VLOOKUP data ke sebelah kanan. Penekanan pembahasan cari data ke sebelah kiri alasannya yakni memang itulah yang biasanya sering menjadi pertanyaan , khususnya dari pengguna excel yang gres mengenal atau menggunakan rumus VLOOKUP.
Contoh Kasus Rumus VLOOKUP Untuk Cari Data Sebelah Kiri
Daftar Isi Artikel
Anggaplah kita memiliki suatu 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 berikutnya yakni bagaimana mencari nama karyawan menurut nomor ID yang sudah dikenali seumpama yang ditunjukan dalam gambar berikut:
Dari gambar diatas , sanggup dilihat bahwa data yang dikenali (ID) terletak di kolom D , sedangkan data yang di diharapkan (Nama) terletak di kolom B. Artinya data yang dicari terletak di sebelah kiri kolom acuan.
Jadi.. rumus apa yang sanggup digunakan untuk mencari nama karyawan pada kolom yang terletak di sebelah kiri kolom berisi nomor ID ?
Mari kita diskusikan satu persatu…
Cari Data Ke Kiri Menggunakan Rumus VLOOKUP-CHOOSE
Dengan fungsi CHOOSE , ternyata kita sanggup mendefinisikan sendiri kolom mana yang mau dianggap selaku kolom pertama dan ke dua oleh fungsi VLOOKUP.
Oleh karenanya kita akan menggunakan fungsi CHOOSE untuk memunculkan kolom “ID” selaku kolom pertama , dan kolom “Nama” selaku kolom kedua.
Adapun formula penelusuran data di sebeah kiri sanggup dituliskan selaku berikut:
=VLOOKUP(H1 ,CHOOSE({1 ,2} ,D:D ,B:B) ,2 ,0)
Rumus tersebut sanggup menciptakan nama yang dicari seumpama ditunjukan dalam gambar berikut:
Perhatikan kembali fragmen rumus CHOOSE({1 ,2} ,D:D ,B:B)
- Dalam hal ini fungsi CHOOSE digunakan untuk memunculkan kolom D (kolom berisi data ID) selaku kolom pertama dan kolom B (kolom berisi data Nama) selaku kolom kedua.
- Kemudian setelah digabung dengan rumus VLOOKUP , maka VLOOKUP akan mencari ID di kolom pertama dan menampilkan hasil data nama dari kolom ke-2 , pada baris yang serupa dengan baris sel berisi ID yang diketahui.
Cari Data Ke Kiri Menggunakan Rumus HLOOKUP-MATCH
Dengan membalik orientasi vertikal menjadi horizontal , bergotong-royong kita sanggup melakukan penelusuran 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 suatu sel di kolom D yang berisi nilai ID yang diketahui. Rumus ini menciptakan angka 3 yakni nomor baris sel yang berisi nilai ID=13043
- Kemudian rumus HLOOKUP akan mencari posisi kolom “Nama” pada baris pertama dalam range “A:E”. Selanjutnya menampilkan warta dari baris 3 pada kolom “Nama”. Dan alhasil yakni “Dulah”
Cari Data Ke Kiri Menggunakan Rumus INDEX-MATCH
Rumus ini sepertinya ialah rumus terpopuler alternative VLOOKUP ke kiri. Terbukti dengan banyaknya panduan yang membahas hal ini. Coba saja cari keyword ‘vlookup ke kiri’ di Google. Maka pada biasanya link di halaman pertama google akan mengarah ke halaman web/blog yang menerangkan INDEX MATCH , bukannya menerangkan VLOOKUP.
Terkecuali kalau halaman yang sedang anda baca ini ini sanggup timbul di halaman pertama Google (penuh harap… 😇😇 ) , maka mungkin akan memperbesar warna sendiri tentang cara penelusuran data di sebelah kiri….
Baiklah , berikutnya rumus INDEX MATCH dalam contoh masalah ini sanggup dituliskan selaku berikut:
=INDEX(B:B ,MATCH(H1 ,D:D ,0))
Fungsi INDEX digunakan untuk mendapat nilai dari Sel sesuai index baris dan kolom dalam range yang ditentukan.
Misalnya:
=INDEX(range ,x ,y)
Maka rumus tersebut akan menciptakan nilai dari baris ke-x dan kolom ke-y dalam range. nomor kolom bersifat opsional sehingga sanggup ditiadakan seumpama ini:
=INDEX(range ,x)
Rumus tersebut akan menciptakan 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 harus 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 digunakan untuk mencari posisi baris sel pada kolom D yang berisi nilai ID yang dikenali (sesuai yang diisi pada sel H1). Dan alhasil yakni angka 3 (nomor baris sel berisi ID=13043)
- Kemudian fungsi INDEX menggunakan angka 3 tersebut untuk mengambil data padanannya pada kolom B
- Hasilnya yakni “Dulah” yakni data dalam sel yang terletak pada baris 3 kolom B.
Cari Data Ke Kiri Menggunakan Rumus OFFSET MATCH
Cara kerja rumus ini yakni dengan menggunakan posisi relative baris data yang dicari dari sel pola dalam fungsi OFFSET. Perhatikan gambar berikut:
- Fungsi MATCH digunakan untuk mendapat angka baris sel yang berisi nilai ID yang dikenali 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 menciptakan bilangan 3 , kemudian 3 dikurangi 1 menjadi 2.
- Hasilnya kemudian digunakan oleh fungsi OFFSET untuk mendapat nilai dari sel yang jaraknya 2 baris ke arah bawah dari dari sel B1.
- Dan alhasil yakni “Dulah” yakni 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 melakukan pekerjaan dengan cara menyeleksi alamat sel yang berisi data yang dicari , kemudian mendapat nilainya.
- Rumus MATCH(H1 ,D:D ,0) menciptakan angka 3 yakni posisi baris sel yang berisi ID 13043
- Rumus COLUMN(B:B) menciptakan angka 2 yaitu nomur urut kolom B dalam lembar kerja excel
- Fungsi ADDRESS digunakan untuk mendapat alamat sel sesuai index baris dan kolom yang diperoleh dari rumus MATCH dan COLUMN. Sehingga kalau diringkas menjadi =ADDRESS(3 ,2) , rumus ini menciptakan nilai $B$3
- Fungsi INDIRECT digunakan untuk mendapat nilai dalam alamat sel yang diketahui. Dan nilai yang terdapat dalam sel $B$3 yakni “Dulah”
Ringkasan:
Ada banyak cara untuk mengakhiri kiprah Excel. Termasuk untuk melakukan penelusuran data di excel. Dalam masalah VLOOKUP ke kiri , kita sanggup menggunakan beberapa alternative seumpama 5 contoh rumus yang sudah diterangkan yaitu: variasi VLOOKUP – CHOOSE , variasi HLOOKUP-MATCH , variasi INDEX-MATCH , variasi OFFSET-MATCH dan variasi INDIRECT-ADDRESS-MATCH-COLUMN.
Demikian supaya bermanfaat.
Salam..
Baca juga panduan Belajar Excel Lainnya:
Seorang pengajar dan pembelajar yang mengurus lembaga pelatihan dan kursus komputer bernama LKP Naura