Rumus Vlookup Sebagian Kata atau Teks (VLOOKUP - LEFT, MID, RIGHT)
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 seolah-olah pola excel berikut:
Pada kolom B anda mempunyai beberapa data kode Tiket. isyarat tersebut disusun dengan ketentuan:
- 2 Digit pertama memperlihatkan jenis Maskapai penerbangan
- 1 Digit angka berikutnya menunjukkan Kelas Penerbangan, dan
- 3 Digit terakhir dari instruksi tersebut menunjukkan Lokasi Tujuan
Pada Kolom-kolom yang lain anda juga sudah mempunyai beberapa tabel yang memperlihatkan arti dari isyarat-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 adonan 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, admin 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, lalu mengambil nilai dari sel mana pun di baris yang sama dengan hasil pencarian pada tabel data tersebut.
Cara memakai Fungsi Vlookup pada rumus excel yaitu sebagai berikut:
VLOOKUP(NilaiYangDiCari; TabelReferensi; NomorKolom; [RangeLookup])Dimana NilaiYangDicari merupakan nilai sel akan dicari pada TabelRefrensi sesuai dengan informasi NomorKolom pada tabel tersebut. Sedangkan RangeLookup menunjukkan tipe pencarian pada rumus Vlookup untuk menemukan hasil yang sama persis atau cukup menemukan nilai mendekati apabila NilaiYangDicari tidak ditemukan.
Untuk lebih jelasnya mampu 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 info yang diberikan.
Cara memakai Fungsi Left pada rumus excel ialah sebagai berikut:
LEFT(Teks; [JumlahKarakter])JumlahKarakter merupakan info 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 menyerupai 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 memakai Fungsi Mid pada rumus excel ialah sebagai berikut:
MID(Teks; AwalKarakter; JumlahKarakter)AwalKarakter menunjukkan posisi awal pengambilan teks sedangkan JumlahKarakter merupakan informasi banyaknya karakter yang akan diambil dari Teks.
Rumus RIGHT Excel
Fungsi atau Rumus Right ibarat dengan fungsi Left. Bedanya bila 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])JumlahKarakter merupakan info banyaknya karakter yang akan diambil dari balasan Teks.Teks.
Penjelasan khusus untuk ketiga fungsi pengambilan teks ini mampu 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 wacana bagaimana memakai rumus gabungan atau kombinasi dari fungsi-fungsi di atas.
Sebelum itu perlu anda ketahui bahwa pada rumus-rumus dibawah ini, tabel-tabel acuan 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 mampu anda pelajari pada halaman berikut: Cara Memberi Nama Range Pada Excel
Rumus Excel Vlookup-Left
Kolom C pada tabel arahan Tiket akan diisi dengan Nama Maskapai. Untuk mencari nama maskapai dari kode tersebut rumus VLookup yang digunakan yaitu:
=VLOOKUP(Kode;TabelMaskapai;2;FALSE)Tentunya jika diterapkan rumus diatas akan menghasilkan pesan error #NA, alasannya adalah TabelMaskapai hanya memuat 2 digit pertama dari isyarat tersebut. Untuk mengambil 2 digit pertama arahan tersebut gunakanlah fungsi Left dengan rumus excel sebagai berikut:
LEFT(Kode;2)Teks Hasil dari fungsi Left tadi lalu 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 adonan rumus Vlookup-Left nya yaitu:
=VLOOKUP(LEFT(B3;2);TabelMaskapai;2;FALSE)
Selanjutnya anda tinggal melaksanakan copy paste rumus tersebut pada sel-sel lain dibawahnya. jikalau anda tidak memakai 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 ialah: Firts Class, Business Class atau Economy Class. Untuk menentukan jenis Kelas dari kode tersebut rumus VLookup yang digunakan ialah:
VLOOKUP(Kode;TabelKelas;2;FALSE)Pada instruksi tersebut kode Kelas merupakan 1 karakter sehabis arahan Maskapai atau 1 karakter dimulai pada posisi ke-3. Untuk mengambil kode Kelas tersebut tentunya yang digunakan yaitu fungsi MID.
MID(Kode;3;1)Jika kedua rumus ini digabungkan maka menjadi:
VLOOKUP(MID(Kode;3;1);TabelKelas;2;FALSE)Dan jikalau 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 ketika menyusun sebuah isyarat-kode tabel.
Pada tabel tersebut kode Kelas merupakan angka 1,2 dan 3. Sedangkan hasil dari fungsi MID termasuk juga fungsi LEFT dan RIGHT ialah sebuah teks, sehingga hasil rumus MID tadi kalau dicari pada tabel kelas menggunakan fungsi VLookup maka dinyatakan tidak ditemukan atau #NA karena parameter yang kita gunakan adalah pencarian persis(FALSE). Supaya tidak muncul error 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 menerima Jenis kelas penerbangan untuk isyarat-kode lainnya.
Bagaimana? simpel bukan? Selanjutnya kita akan mengisi kolom E
Rumus Excel Vlookup-Right
Untuk kolom E atau kolom tujuan di mampukan dari Vlookup 3 digit terakhir terkahir isyarat. Dengan mengetahui kriteria ini tentunya anda sudah mampu menyimpulkan bahwa rumus apa yang akan digunakan.
Jika anda menyimpulkan untuk menggunakan rumus adonan fungsi Vlookup-Right, maka anda benar. Kaprikornus pada kolom E3 rumus excelnya yaitu:
=VLOOKUP(RIGHT(B3;3);TabelTujuan;2;FALSE)Pada kolom E atau untuk mendapatkan Tujuan Penerbangan ini sebetulnya anda mampu juga memakai rumus kombinasi fungsi Vlookup-Mid. Lho mampu ya?
Bisa, bila anda lebih jeli lagi arahan Tujuan bisa juga anda dapatkan dengan mengambil karakter aba-aba dari posisi tengah teks. yaitu dimulai pada karater ke-4 sebanyak 3 karakter teks.
Apabila anda memakai 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 adonan VLookup Dengan Fungsi Left, Mid, Right Excel
File latihan untuk pembahasan kali ini mampu anda mampukan melalui link download di bawah ini :
LINK DOWNLOAD TERKUNCI.
Silahkan SHARE untuk membuka kunci!
Bagaimana? praktis bukan. Sekian dulu untuk tutorial cara menggunakan Rumus Vlookup-Left, Rumus Vlookup-right dan Vlookup-Mid kali ini.
Berikut beberapa tutorial excel tentang Rumus VLookup yang bisa anda pelajari:
- Fungsi Vlookup Excel: teladan Dan Tutorial memakainya
- 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
Apabila masih ada pertanyaan silahkan disampaikan pada kolom komentar yang tersedia dan jika anda rasa artikel tutorial ini bermanfaat jangan lupa untuk di share biar lebih banyak lagi yang akan ikut mendapatkan keuntungannya. Salam Kelas Excel.
Komentar
Posting Komentar