VLOOKUP merupakan salah satu fungsi Excel yang berfungsi untuk mencari dan mengambil data dari suatu tabel referensi berdasarkan suatu nilai tertentu secara vertikal. VLOOKUP merupakan penyingkat dari kata vertical lookup yang menggambarkan proses pencarian data yang sesuai baris demi baris. Fungsi VLOOKUP sudah digunakan atau sudah ada saat pertama kali Microsoft excel dibangun sampai saat ini, dan fungsi ini merupakan fungsi yang paling sering dugunakan oleh pengguna Microsoft excel

Pada kesempatan kali ini saya akan membahas tuntas dan habis tentang hal hal yang berkaitan dengan VLookup menggunakan macro atau visual basic for application, anda tentu sudah mengetahui kelebihan macro dibandingkan dengan Formula, singkatnya macro itu lebih ringan dari pada kita memakai formula, sehingga akan sangat berpengaruh terhadap kinerja komputer anda, untuk memahami VLookup siapkan langsung sebuah tabel sebagai data base sumber, contoh tabel data base sumber sebagai berikut

K/B A B C D E
1 NO NAMA LENGKAP ALAMAT KOTA -
2 A2 Jono Parengan Tuban Tuban -
3 A22 Sudarso Parengan Tuban Tuban -
4 A1 Gono Parengan Tuban Tuban -
5 A11 Ji Ulo Parengan Tuban Tuban -
6 A01 Gondo Parengan Tuban Tuban -
7 A6 Bodo Parengan Tuban Tuban -
8 A3 Judas Parengan Tuban Tuban -

Jika anda sudah mempunyai tabel seperti diatas sekarang kita siapkan tabel untuk refrensi, akan tetapi karena kita menggunakan macro maka akan lebih baik kita menggunakan userform

Tambahkan usefrom dengan 5 buah textbox sebagai kolom, coba lihat tampilan design userformnya dibawah ini


Ketikan baris kode macro dibawah ini kedalam userform

Private Sub TextBox1_Change()
Set wsDtbsBrg = Sheets("Sheet1")
Set rgKodeBrg = wsDtbsBrg.Range("A2:A100")
If TextBox1.Value = "" Then
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
Exit Sub
End If
Set c = rgKodeBrg.Find(TextBox1.Value, LookIn:=xlValues, _
MatchCase:=True)
TextBox2.Value = c.Offset(0, 1).Value
TextBox3.Value = c.Offset(0, 2).Value
TextBox4.Value = c.Offset(0, 3).Value
End Sub

Sampai disini sebenarnya anda sudah berhasil membuat Macro Excel Pengganti VLookup, akan tetapi ada masalah dengan baris kode macro Set c = rgKodeBrg.Find(TextBox1.Value, LookIn:=xlValues, MatchCase:=True) kode macro yang saya sebutkan ini tidak mampu membaca karakter secara sensitif, karena kode macro tersebut membaca A2 sebagai A22 dan seterusnya. Untuk mengatasi masalah ini anda bisa menggunakan kode macro dibawah ini sebagai alternatif

Private Sub TextBox1_Change()
Set IpAoNE = Sheets("Sheet1")
Set idRangeA = IpAoNE.Range("A2:A100")
For JOs = 1 To WorksheetFunction.CountA(idRangeA)
If TextBox1 = IpAoNE.Cells(JOs + 1, 1).Value Then
TextBox2.Value = IpAoNE.Cells(JOs + 1, 2).Value
TextBox3.Value = IpAoNE.Cells(JOs + 1, 3).Value
TextBox4.Value = IpAoNE.Cells(JOs + 1, 4).Value
Exit Sub
End If
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
Next JOs
End Sub

Perhatikan perbedaanya pada gambar dibawah ini


Untuk mempermudah memahami teori diatas silahkan download file Macro Excel Pengganti VLookup sampel pada link download dibawah ini, semoga bermanfaat

Macro Excel Pengganti VLookup


Ada kasus lagi, ternyata kode macro yang nomor dua tidak bisa membaca nilai yang terindikasi berformat angka, misalnya angka "021" angka "012", dan seterusnya bagaimana cara mengatasinya ? Silahkan perhatikan sedikit penjelasan dibawah ini

Lihat baris kode macro dibawah ini

If TextBox1 = IpAoNE.Cells(JOs + 1, 1).Value Then

Lalu ganti baris kode macro diatas dengan baris kode macro ini

If Format(TextBox1, "@") = Format(IpAoNE.Cells(JOs + 1, 1).Value, "@") Then

Sehingga menjadi seperti ini

Private Sub TextBox1_Change()
Set IpAoNE = Sheets("Sheet1")
Set idRangeA = IpAoNE.Range("A2:A100")
For JOs = 1 To WorksheetFunction.CountA(idRangeA)
If Format(TextBox1, "@") = Format(IpAoNE.Cells(JOs + 1, 1).Value, "@") then
TextBox2.Value = IpAoNE.Cells(JOs + 1, 2).Value
TextBox3.Value = IpAoNE.Cells(JOs + 1, 3).Value
TextBox4.Value = IpAoNE.Cells(JOs + 1, 4).Value
Exit Sub
End If
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
Next JOs
End Sub

Macro Excel Pengganti VLookup


Vlookup dengan dua atau tiga kriteria atau lebih, Vlookup dengan dua kriteria atau lebih digunakan untuk mendapatkan hasil yang lebih specifik, misalnya ada mempunyai tabel sumber sebagaimana tabel dibawah ini

K/B A B C D E
1 TINGKAT JURUSAN NAMA SISWA ALAMAT KOTA -
2 1 IPAJono Parengan Tuban Tuban
3 1 IPSSudarso Parengan Tuban Tuban
4 1 BAHASAGono Parengan Tuban Tuban
5 2 IPAJi Ulo Parengan Tuban Tuban
6 2 IPSGondo Parengan Tuban Tuban
7 2 BAHASABodo Parengan Tuban Tuban
8 3 BAHASAJudas Parengan Tuban Tuban

Jika anda hanya mengetikan nilai pada kolom A saja misal nilai "1" maka nama siswa yang berada pada tingkat "1" ada tiga "3" siswa, selanjutnya untuk menghasilkan nilai yang lebih specifik tambahkan pula kriteria yang kedua yaitu kriteria "Jurusan" yang ada pada kolom B. Lihat kode macronya dibawah ini

Private Sub TextBox1_Change()
Set IpAoNE = Sheets("Sheet1")
Set idRangeA = IpAoNE.Range("A2:A100")
For JOs = 1 To WorksheetFunction.CountA(idRangeA)
If TextBox1 = IpAoNE.Cells(JOs + 1, 1).Value and _
TextBox2 = IpAoNE.Cells(JOs + 1, 2).Value then
TextBox3.Value = IpAoNE.Cells(JOs + 1, 3).Value
TextBox4.Value = IpAoNE.Cells(JOs + 1, 4).Value
Exit Sub
End If
TextBox3.Value = ""
TextBox4.Value = ""
Next JOs
End Sub

Untuk lebih memahami Vlookup dengan dua atau tiga kriteria atau lebih silahkan langsung saja ambil file sampelnya dibawah ini


Demikianlah pembahasan Macro Excel Pengganti VLookup semoga bermanfaat, salam dan selamat berawal pekan
No comments