Bagaimanakah rumus excel Vlookup sebagian kata atau rumus Vlookup sebagian isi dari sebuah sel excel?
Bagi setiap pengguna excel, khususnya yang aktif membaca tutorial excel di blog Kelas Excel ini tentunya sudah tahu bahwa fungsi Vlookup pada excel digunakan untuk mencari nilai teks atau angka pada sebuah tabel vertikal kemudian menampilkan data yang sebaris dari hasil pencarian nilai tersebut.
Sedangkan untuk mengambil sebagian teks, excel telah menyediakan fungsi LEFT, MID dan RIGHT.
Sehingga untuk melakukan vlookup sebagian kata atau teks maka anda perlu menggabungkan fungsi Vlookup pada excel dengan Fungsi Left, Mid atau Right pada sebuah rumus excel.
Misalnya seperti contoh excel berikut:
Pada kolom B anda mempunyai beberapa data Kode Tiket. Kode tersebut disusun dengan ketentuan:
- 2 Digit pertama menunjukkan jenis Maskapai penerbangan
- 1 Digit angka berikutnya menunjukkan Kelas Penerbangan, dan
- 3 Digit terakhir dari kode tersebut menunjukkan Lokasi Tujuan
Pada Kolom-kolom yang lain anda juga sudah mempunyai beberapa tabel yang menunjukkan arti dari kode-kode tersebut.
Selanjutnya bagaimanakah rumus Vlookup yang digunakan untuk mengisi sel-sel excel yang masih kosong pada kolom C, D dan E?
Seri tutorial rumus Vlookup kali ini akan mengajak anda untuk belajar menggunakan fungsi atau rumus gabungan Vlookup dengan fungsi Left, Mid atau Right.
Rumus VLOOKUP, LEFT, MID, dan RIGHT
Sebelum membahas lebih lanjut mengenai rumus kombinasi Vlookup dengan Rumus Left, Mid, dan Right untuk menjawab soal excel di atas, saya kira perlu untuk saya ingatkan kembali bagaimana cara menggunakan masing-masing fungsi tersebut.
Rumus VLOOKUP Excel
Fungsi atau Rumus VLOOKUP digunakan untk mencari data pada kolom pertama sebuah tabel, kemudian mengambil nilai dari sel mana pun di baris yang sama dengan hasil pencarian pada tabel data tersebut.
Cara Menggunakan Fungsi Vlookup pada rumus excel adalah sebagai berikut:
VLOOKUP(NilaiYangDiCari; TabelReferensi; NomorKolom; [RangeLookup])Dimana argumen NilaiYangDicari merupakan nilai sel akan dicari pada TabelRefrensi sesuai dengan informasi NomorKolom pada tabel tersebut.
Sedangkan argumen RangeLookup menunjukkan tipe pencarian pada rumus Vlookup untuk menemukan hasil yang sama persis atau cukup menemukan nilai yang mendekati atau terdekat apabila NilaiYangDicari tidak ditemukan.
Untuk lebih jelasnya bisa anda pelajari pembahasan khusus mengenai Fungsi Vlookup ini pada halaman berikut: Fungsi VLOOKUP Excel
Rumus LEFT Excel
Fungsi atau Rumus Left merupakan salah satu Fungsi Excel yang digunakan untuk mengambil beberapa karakter teks dari arah kiri atau dari awal sebuah teks sesuai dengan informasi yang diberikan.
Cara Menggunakan Fungsi Left pada rumus excel adalah sebagai berikut:
LEFT(Teks; [JumlahKarakter])Argumen JumlahKarakter merupakan informasi banyaknya karakter yang akan diambil dari awal Teks.
Rumus MID Excel
Fungsi atau Rumus MID bisa digunakan untuk mengambil beberapa karakter di tengah teks. Fungsi MID Mirip dengan fungsi LEFT yang digunakan untuk mengambil sebagian kata dalam teks dari arah kiri ke kanan, namun posisi awal pengambilan karakter atau kata tersebut bisa kita tentukan sendiri.
Cara Menggunakan Fungsi Mid pada rumus excel adalah sebagai berikut:
MID(Teks; AwalKarakter; JumlahKarakter)Argumen AwalKarakter menunjukkan posisi awal pengambilan teks sedangkan JumlahKarakter merupakan informasi banyaknya karakter yang akan diambil dari Teks.
Rumus RIGHT Excel
Fungsi atau Rumus Right mirip dengan fungsi Left. Bedanya jika fungsi Left megambil karakter dari awal teks, Sedangkan fungsi Right mengambil sejumlah karakter dari akhir teks atau dari arah kanan ke kiri.
RIGHT(Teks; [JumlahKarakter])Argumen JumlahKarakter merupakan informasi banyaknya karakter yang akan diambil dari akhir Teks.
Penjelasan khusus untuk ketiga fungsi pengambilan teks ini bisa anda baca pada halaman tutorial excel berikut: Cara Mengambil Beberapa Karakter Teks pada Microsoft
Menggabungkan Rumus Excel VLOOKUP, LEFT, MID, RIGHT
Setelah kembali mengingat bagaimana cara menggunakan fungsi VLookup, Left, Mid, dan Right berikutnya kita akan kembali ke topik utama tentang bagaimana menggunakan rumus gabungan atau kombinasi dari fungsi-fungsi di atas.
Sebelum itu perlu anda ketahui bahwa pada rumus-rumus dibawah ini, tabel-tabel referensi yang digunakan sudah saya diberi nama (Named Range) untuk mempermudah penggunaan pada rumus excel yang akan digunakan.
| NAMA RANGE | REFERENSI |
|---|---|
| TabelMaskapai | $G$2:$H$7 |
| TabelClass | $J$2:$K$5 |
| TabelTujuan | $M$2:$N$9 |
Untuk panduan menamai Range Excel bisa anda pelajari pada halaman berikut: Cara Memberi Nama Range Pada Excel
Rumus Excel Vlookup-Left
Kolom C pada tabel Kode Tiket akan diisi dengan Nama Maskapai. Untuk mencari nama maskapai dari kode tersebut rumus VLookup yang dipakai adalah:
=VLOOKUP(Kode;TabelMaskapai;2;FALSE)Tentunya jika diterapkan rumus diatas akan menghasilkan pesan error #NA, sebab TabelMaskapai hanya memuat 2 digit pertama dari kode tersebut.
Untuk mengambil 2 digit pertama kode tersebut gunakanlah fungsi Left dengan rumus excel sebagai berikut:
LEFT(Kode;2)Teks Hasil dari fungsi Left tadi kemudian kita gunakan sebagai argumen NilaiYangDiCari pada fungsi VLookup, sehingga rumusnya menjadi:
=VLOOKUP(LEFT(Kode;2);TabelMaskapai;2;FALSE)Jika rumus excel tersebut diterapkan pada tabel KODE TIKET, maka Pada sel C3 gabungan rumus Vlookup-Left nya adalah:
=VLOOKUP(LEFT(B3;2);TabelMaskapai;2;FALSE)
Selanjutnya anda tinggal melakukan copy paste rumus tersebut pada sel-sel lain dibawahnya. Jika anda tidak menggunakan Named Range, rumus pada sel C3 bisa juga diisi dengan:
=VLOOKUP(LEFT(B3;2);$G$2:$H$7;2;FALSE)Rumus Excel Vlookup-Mid
Untuk Kolom D akan diisi dengan salah satu jenis Kelas Penerbangan yakni: Firts Class, Business Class atau Economy Class.
Untuk menentukan jenis Kelas dari kode tersebut rumus VLookup yang dipakai adalah:
VLOOKUP(Kode;TabelKelas;2;FALSE)Pada Kode tersebut Kode Kelas merupakan 1 karakter setelah kode Maskapai atau 1 karakter dimulai pada posisi ke-3.
Untuk mengambil kode Kelas tersebut tentunya yang digunakan adalah fungsi MID.
MID(Kode;3;1)Jika kedua rumus ini digabungkan maka menjadi:
VLOOKUP(MID(Kode;3;1);TabelKelas;2;FALSE)Dan jika diterapkan pada Sel D3 maka rumus excelnya menjadi:
=VLOOKUP(MID(B3;3;1);TabelKelas;2;FALSE)Loh, kog Error #NA?
Ya disinilah terkadang kita juga harus lebih jeli saat menyusun sebuah kode-kode tabel.
Pada tabel tersebut kode Kelas merupakan angka 1,2 dan 3. Sedangkan hasil dari fungsi MID termasuk juga fungsi LEFT dan RIGHT adalah sebuah teks. Sehingga hasil rumus MID tadi jika dicari pada tabel kelas menggunakan fungsi VLookup maka dinyatakan tidak ditemukan atau menghasilla nilai error #NA. Sebab parameter yang kita gunakan adalah pencarian persis(FALSE).
Supaya tidak muncul error #NA maka hasil fungsi MID perlu kita ubah dari nilai teks menjadi nilai numerik menggunakan fungsi VALUE sehingga rumus excelnya menjadi:
=VLOOKUP(VALUE(MID(B3;3;1));TabelKelas;2;FALSE)atau bisa juga anda isi dengan rumus berikut:
=VLOOKUP(VALUE(MID(B3;3;1));$J$2:$K$5;2;FALSE)Selanjutnya anda tinggal Copy paste rumus diatas pada sel-sel lain dibawahnya untuk mendapatkan Jenis kelas penerbangan untuk kode-kode lainnya.
Bagaimana? mudah bukan? Selanjutnya kita akan mengisi kolom E
Rumus Excel Vlookup-Right
Untuk kolom E atau kolom tujuan di dapatkan dari Vlookup 3 digit terakhir terkahir kode. Dengan mengetahui kriteria ini tentunya anda sudah bisa menyimpulkan bahwa rumus apa yang akan digunakan.
Jika anda menyimpulkan untuk menggunakan rumus gabungan fungsi Vlookup-Right, maka anda benar. Jadi pada kolom E3 rumus excelnya adalah:
=VLOOKUP(RIGHT(B3;3);TabelTujuan;2;FALSE)Pada kolom E atau untuk mendapatkan Tujuan Penerbangan ini sebenarnya anda bisa juga menggunakan rumus kombinasi fungsi Vlookup-Mid.
Lho bisa ya?
Bisa, Jika anda lebih jeli lagi kode Tujuan bisa juga anda dapatkan dengan mengambil karakter kode dari posisi tengah teks. Yakni dimulai pada karater ke-4 sebanyak 3 karakter teks.
Apabila anda menggunakan cara ini, maka rumus excel pada Sel E3 bisa juga anda isi dengan rumus:
=VLOOKUP(MID(B3;4;3);TabelTujuan;2;FALSE)Download File Latihan Rumus Gabungan VLookup Dengan Fungsi Left, Mid, Right Excel
File latihan untuk pembahasan kali ini bisa anda dapatkan melalui link download di bawah ini :
Download File *Jika link mati / tidak dapat diakses silahkan lapor via kontak yang tersedia
LINK DOWNLOAD TERKUNCI.
Silahkan SHARE untuk download file!
Bagaimana? mudah bukan. Sekian dulu untuk tutorial cara menggunakan Rumus Vlookup-Left, Rumus Vlookup-right dan Vlookup-Mid kali ini.
Apabila masih ada pertanyaan silahkan disampaikan pada kolom komentar yang tersedia dan jika anda rasa artikel tutorial ini bermanfaat jangan lupa untuk di share agar lebih banyak lagi yang akan ikut mendapatkan manfaatnya. Salam Kelas Excel.
- Fungsi Vlookup Excel: Contoh Dan Cara Menggunakannya
- Rumus Vlookup Sebagian Teks (Vlookup, Left, Mid, Right)
- Rumus Vlookup Ke Arah Kiri
- Rumus Vlookup Dengan 2 Kriteria/Kondisi
- Rumus Vlookup Dengan 2 Tabel Data
- Rumus Vlookup Dengan Banyak Kriteria
- Rumus VLookUp Dengan 2 Hasil Pencarian atau Lebih
- Index-Match : Rumus Alternatif Pengganti VLookup
- Cara Konversi Rentang Nilai Angka ke Huruf Dengan Vlookup Excel
- Rumus Terbilang Manual Dengan Fungsi Vlookup Excel
