5 Cara Vlookup Excel Ke Arah Kiri Dengan Rumus Excel

Vlookup Ke Kiri - Salah satu kekurangan pencarian atau lookup data menggunakan fungsi VLOOKUP adalah tidak bisa mencari atau mengambil data di sebelah kiri kolom pencarian. VLOOKUP tidak bisa menoleh ke kiri sebab hanya bisa mencari data pada kolom paling kiri dari tabel pencarian yg diberikan.

Dalam kasus-kasus tertentu kita membutuhkan cara untuk bisa mengambil data yg sebaris pada kolom pencarian. Namun, data tersebut ada disebelah kiri kolom pencarian. Misalnya sprti contoh kasus di bawah ini.



Dari gambar tersebut jika diketahui nama depan maka kita bisa menggunakan rumus VLOOKUP biasa untuk mendapatkan Nama belakang, Nomor ID atau Kota yg sebaris dengan nama tersebut. Namun, karena yg diketahui atau diinput adalah nomor ID & kita ingin mendapatkan nilai sel yg sebaris pada kolom nama dari nomor ID tersebut misalnya, nama depan & belakang, maka kita tidak bisa menggunakan rumus VLOOKUP dengan cara biasa.

Adakah rumus yg digunakan apabila hendak membaca tabel dari kanan ke kiri?

Ada beberapa gabungan fungsi excel yg bisa kita rangkai untuk menjadi sebuah rumus excel sbgai solusi kasus pencarian data ke arah kiri atau VLOOKUP yg bisa menoleh ke kiri ini.

1. Rumus Vlookup Menoleh Kiri Dengan Fungsi Indirect, Address, Match Dan Column
2. Rumus Vlookup Menoleh Kiri Dengan Fungsi Offset - Match
3. Rumus Vlookup Menoleh Kiri Dengan Fungsi Index - Match
4. Rumus Vlookup Menoleh Kiri Dengan Fungsi Hlookup - Match
5. Rumus Vlookup Menoleh Kiri Dengan Fungsi Vlookup - Choose

**File contoh pembahasan bisa anda download pada bagian akhir pembahasan

1. Rumus VLOOKUP Ke arah Kiri Dengan Fungsi INDIRECT, ADDRESS, MATCH, ROW Dan COLUMN

Kita bisa menggunakan kombinasi fungsi INDIRECT, ADDRESS, MATCH , ROW & COLUMN untuk menggantikan fungsi Vlookup ke arah kiri. Sintaks rumus excel yg dipakai dlm rumus ini adalah:

=INDIRECT(ADDRESS(MATCH(DiCari;KolomPencarian;0)+ROW(SelPertamaKolomPencarian)-1;COLUMN(KolomPengambilan)))

Perhatikan conroh gambar brkut:



Pada gambar diatas rumus yg dipakai untuk mendapatkan Nama depan adalah:

=INDIRECT(ADDRESS(MATCH(H2;D2:D10;0)+ROW(D2)-1;COLUMN(B2:B10)))

Se&gkan untuk mendapatkan nama belakang rumus yg digunakan adalah:

=INDIRECT(ADDRESS(MATCH(H2;D2:D10;0)+ROW(D2)-1;COLUMN(C2:C10)))

Sel H2 adalah sel yg akan kita cari posisinya pada Kolom Atau Range D2:D10. Jika sdh ketemu posisinya maka kita akan ambil data pada range B2:B10 yg sebaris dengan hasil pencarian tersebut.

Cara kerja rumus diatas adalah sbgai brkut:

Fungsi MATCH akan mencari posisi nilai yag dicari pada kolom pencarian. Karena sel pertama bernilai 1 se&gkan yg kita harapkan adalah posisi baris pada data maka hasil match ini perlu ditambah dengan jumlah baris diatas kolom pencarian. jumlah baris tersebut bisa didapat dengan menggunakan rumus excel:

ROW(D2)-1

Rumus diatas bisa juga kita ganti langsung dengan jumlah baris diatas kolom pencarian, Jika mengacu pada contoh maka jumlah barisnya adalah 1.

Hasil dari rumus MATCH digunakan untuk mengisi argumen posisi baris pada fungsi ADDRESS. Se&gkan rumus COLUMN dugunakan untuk mengisi posisi kolom pada fungsi ADDRESS.

Alamat sel yg dihasilkan oleh fungsi ADDRESS tadi diambil datanya menggunakan fungsi INDIRECT.

Untuk penjelasan lebih lanjut tentang fungsi-fungsi excel diatas bisa anda pelajari pada bagian lain weblog ini.

1. Fungsi INDIRECT Excel
2. Fungsi ADDRESS Excel
3. Fungsi MATCH Excel
4. Fungsi ROW & COLUMN Excel

2. Rumus VLOOKUP Ke Arah Kiri Dengan Fungsi OFFSET-MATCH

Cara kedua untuk melakukan Vlookup data ke arah kiri adalah dengan menggunakan gabungan fungsi OFFSET & MATCH Excel.

Sintaks yg dipakai:

=OFFSET(HeaderKolomPengambilan;MATCH(Dicari;KolomPencarian;0);0)

Pada gambar diatas rumus excel yg dipakai untuk mencari nama depan adalah:

=OFFSET(B1;MATCH(H2;D2:D10;0);0)

Se&gkan untuk nama belakang adalah:

=OFFSET(C1;MATCH(H2;D2:D10;0);0)

Yang perlu dicatat jika menggunakan formula excel ini untuk melakkan vlookup yg mnoleh ke kiri, bahwa baris sel acuan OFFSET harus diatas sel pertama kolom pencarian.

Pada contoh diatas sel pertama kolom pencarian adalah H2. Maka acuan offset memakai sel pada baris 1. Se&gkan kolomnya menyesuiakan kolom mana yg akan diambil.

3. Rumus VLOOKUP Ke Arah Kiri Dengan Fungsi INDEX-MATCH

Cara ketiga ini sebenarnya sdh saya ulas pada tutorial excel sebelumnya. Cara ini adalah cara yg sering saya temukan dipakai oleh kebanyakan pengguna excel, yakni mennggunakan fungsi Index & match pada excel.

Untuk menggantikan vlookup yg tidak bisa menoleh ke kiri dengan cara rumus excel ini gunakan sintak brkut:

=INDEX(KolomPengambilan;MATCH(Dicari;KolomPencarian;0))

Perhatikan contoh gambar brkut ini:



Formula Excel untuk mendapatkan nama depan adalah:

=INDEX(B2:B10;MATCH(H2;D2:D10;0))

Se&gkan untuk mendapatkan nama belakang:

=INDEX(C2:C10;MATCH(H2;D2:D10;0))

Cara ini relatif lebih mudah jika dibandingkan dengan cara-cara sebelumnya. Sebab kita tidak butuh memperhitungakn posisi baris dimana tabel data berada. Selain itu kombinasi rumus excel INDEX-MATCH ini juga lebih simpel cara pemakaiannya.

Lebih dlm tentang gabungan rumus INDEX-MATCH bisa anda pelajari melalui link dibawah brkut: Rumus Excel INDEX MATCH : Rumus Alternatif Pengganti VLOOKUP

4. Rumus VLOOKUP Ke Arah Kiri Dengan Fungsi HLOOKUP-MATCH

Cara lain untuk melakukan Vlookup Data yg bisa menoleh ke kiri adalah dengan menggunakn gabungan fungsi HLOOKUP & MATCH. Sintaks nya adlah sbgai brkut:

=HLOOKUP(HeaderKolomPengambilan;TabelData;MATCH(DiCari;KolomPencarian;0);FALSE)

Jika sdh terbiasa menggunakan fungsi Hlookup semestinya memahami rumus ini tidaklah sulit. Pada contoh diatas rumus yg digunakan untuk mendapatkan nama depan dengan cara ini adalah:

=HLOOKUP("Nama Depan";A1:E10;MATCH(H2;D1:D10;0);FALSE)

Se&gkan untuk nama belakang:

=HLOOKUP("Nama Belakang";A1:E10;MATCH(H2;D1:D10;0);FALSE)

Kunci rumus diatas adalah pada fungsi MATCH yg digunakan untuk mengisi nomor kolom pada argumen fungsi HLOOKUP. Jika diperlukan slahkan baca penjelasan lebih detail tentang fungsi HLOOKUP pada link brkut: Fungsi & Rumus HLOOKUP

5. Rumus VLOOKUP Ke Arah Kiri Dengan Fungsi VLOOKUP-CHOOSE

Cara kelima adalah menggunakan fungsi Vlookup Excel. Sebenarnya VLOOKUP sendiri juga bisa menoleh ke kiri lho... Nah caranya adalah dengan menggabungkannya dengan fungsi CHOOSE.

Cara penulisan rumus excel vlookup-Choose begini:

=VLOOKUP(Dicari;CHOOSE({1\2};KolomPencarian;KolomPengambilan);2;FALSE)

Jadi, pada rumus diatas fungsi CHOOSE digunakan untuk membalik posisi kolom pencarian agar berada di sebelah kiri pengambilan. Dengan rumus ini, sebenarnya VLOOKUP tetap mengambil data di sebelah kanan kolom pencarian.



Untuk mendapatkan nama depan dengan cara Vlookup-Choose pada gambar diatas adalah dengan menggunakan rumus excel:

=VLOOKUP(H2;CHOOSE({1\2};D2:D10;B2:B10);2;FALSE)

Se&gkan untuk nama belakang:

=VLOOKUP(H2;CHOOSE({1\2};D2:D10;C2:C10);2;FALSE)

Jika anda menggunakan regional setting english atau sejenisnya rumus yg dipakai tidak sprti diatas. Sebab pada Setting english pemisah argumen & pemisah kolom array menggunakan koma(,) bukan slash (\) sprti rumus diatas.

Untuk yg memakai region english atau sejensinya untuk mendapatkan nama depan pada contoh diatas gunakan rumus ini:

=VLOOKUP(H2,CHOOSE({1,2},D2:D10,B2:B10),2,FALSE)

& rumus excel brkut untuk nama belakangnya:

=VLOOKUP(H2,CHOOSE({1,2},D2:D10,C2:C10),2,FALSE)

Demikian 5 cara untuk melakukan vlookup ke arah kiri atau vlookup yg bisa menoleh ke kiri dengan berbagai kombinasi fungsi-fungsi excel. Formula excel mana yg anda pilih?

File contoh untuk pembahasan kali ini tersedia pada link di bawah ini :

Dropbox
GDrive

*Jika link mati / tidak dapat diakses slahkan lapor via kontak yg tersedia

LINK DOWNLOAD TERKUNCI.
Silahkan SHARE untuk membuka kunci!

Google+ Facebook Twitter

Jika anda menemukan cara lain jangan lupa untuk berbagi di kolom komentar.

Berikut beberapa tutorial excel lain tentang Rumus VLookup yg bisa anda pelajari:

1. Fungsi Vlookup Excel: Contoh Dan Cara Menggunakannya
2. Rumus Vlookup Sebagian Teks (Vlookup, Left, Mid, Right)
3. Rumus Vlookup Ke Arah Kiri
4. Rumus Vlookup Dengan 2 Kriteria/Kondisi
5. Rumus Vlookup Dengan 2 Tabel Data
6. Rumus Vlookup Dengan Banyak Kriteria
7. Rumus Vlookup Dengan 2 Hasil Pencarian atau Lebih
8. Index-Match : Rumus Alternatif Pengganti VLookup
9. Cara Konversi Rentang Nilai Angka ke Huruf Dengan Vlookup Excel
10. Rumus Terbilang Manual Dengan Fungsi Vlookup Excel

Sumber https://www.kelasexcel.id

Bagikan Artikel ini

Berlangganan update artikel terbaru via email: