5 Cara Vlookup Data Ke Arah Kiri Dengan Rumus Excel

5 Cara Vlookup Data Ke Arah Kiri Dengan Rumus Excel

5 Cara Vlookup Data Ke Arah Kiri Dengan Rumus Excel

Salah satu kekurangan pencarian atau lookup data dengan 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 yang diberikan.

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

Bagaimana Cara Vlookup ke Arah Kiri?

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

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


**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 dan COLUMN untuk menggantikan fungsi Vlookup ke arah kiri. Sintaks rumus excel yang dipakai dalam rumus ini adalah:

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

Perhatikan conroh gambar berikut:

Cara Vlookup ke Arah Kiri 1

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

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

Sedangkan untuk mendapatkan nama belakang rumus yang digunakan adalah:

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

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

Cara kerja rumus diatas adalah sebagai berikut:

Fungsi MATCH akan mencari posisi nilai yag dicari pada kolom pencarian. Karena sel pertama bernilai 1 sedangkan yang 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. Sedangkan rumus COLUMN dugunakan untuk mengisi posisi kolom pada fungsi ADDRESS.

Alamat sel yang 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 dan 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 dan MATCH Excel.

Sintaks yang dipakai:

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

Cara Vlookup ke Arah Kiri 2

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

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

Sedangkan untuk nama belakang adalah:

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

Yang perlu dicatat jika menggunakan formula excel ini untuk melakkan vlookup yang 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. Sedangkan kolomnya menyesuiakan kolom mana yang akan diambil.

3. RUMUS VLOOKUP KE ARAH KIRI DENGAN FUNGSI INDEX - MATCH


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

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

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

Perhatikan contoh gambar berikut ini:

Cara Vlookup ke Arah Kiri 3

Formula Excel untuk mendapatkan nama depan adalah:

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

Sedangkan 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 dalam tentang gabungan rumus INDEX-MATCH bisa anda pelajari melalui link dibawah ini:
Rumus Excel INDEX MATCH : Rumus Alternatif Pengganti VLOOKUP

4. RUMUS VLOOKUP KE ARAH KIRI DENGAN FUNGSI HLOOKUP - MATCH


Cara lain untuk melakukan Vlookup Data yang bisa menoleh ke kiri adalah dengan menggunakn gabungan fungsi HLOOKUP dan MATCH. Sintaks nya adlah sebagai berikut:

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

Cara Vlookup ke Arah Kiri 4

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

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

Sedangkan untuk nama belakang:

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

Kunci rumus diatas adalah pada fungsi MATCH yang digunakan untuk mengisi nomor kolom pada argumen fungsi HLOOKUP. Jika diperlukan silahkan baca penjelasan lebih detail tentang fungsi HLOOKUP pada link berikut: Fungsi dan 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.

Cara Vlookup ke Arah Kiri 5

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)

Sedangkan untuk nama belakang:

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

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

Untuk yang 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)

dan rumus excel berikut 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 yang bisa menoleh ke kiri dengan berbagai kombinasi fungsi-fungsi excel. Formula excel mana yang anda pilih?

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

Download File

*Jika link mati / tidak dapat diakses silahkan lapor via kontak yang tersedia
LINK DOWNLOAD TERKUNCI.
Silahkan SHARE untuk membuka kunci!


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

0 komentar