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 yg aktif membaca tutorial excel di blog ini tentunya sdh tahu bahwa fungsi Vlookup pada excel digunakan untuk mencari nilai teks atau angka pada sebuah tabel vertikal kemudian menampilkan data yg sebaris dari hasil pencarian nilai tersebut.
Se&gkan untuk mengambil sebagian teks, excel tlah menyediakan fungsi LEFT, MID & 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 sprti contoh excel brkut:
Pada kolom B anda mempunyai beberapa data Kode Tiket. Kode tersebut disusun dengan ketentuan:
- 2 Digit pertama menunjukkan jenis Maskapai penerbangan
- 1 Digit angka brkutnya menunjukkan Kelas Penerbangan, &
- 3 Digit terakhir dari kode tersebut menunjukkan Lokasi Tujuan
Pada Kolom-kolom yg lain anda juga sdh mempunyai beberapa tabel yg menunjukkan arti dari kode-kode tersebut.
Selanjutnya bagaimanakah rumus Vlookup yg digunakan untuk mengisi sel-sel excel yg masih kosong pada kolom C,D & 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, & RIGHT
Sebelum membahas lebih lanjut mengenai rumus kombinasi Vlookup dengan Rumus Left, Mid, & 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 yg sama dengan hasil pencarian pada tabel data tersebut.
Cara Menggunakan Fungsi Vlookup pada rumus excel adalah sbgai brkut:
VLOOKUP(NilaiYangDiCari; TabelReferensi; NomorKolom; [RangeLookup])Dimana NilaiYangDicari adalah nilai sel akan dicari pada TabelRefrensi sesuai dengan informasi NomorKolom pada tabel tersebut. Se&gkan RangeLookup menunjukkan tipe pencarian pada rumus Vlookup untuk menemukan hasil yg sama persis atau cukup menemukan nilai mendekati apabila NilaiYangDicari tidak ditemukan.
Untuk lebih jelasnya bisa anda pelajari pembahasan khusus mengenai Fungsi Vlookup ini pada halaman brkut: Fungsi VLOOKUP Excel
Rumus LEFT Excel
Fungsi atau Rumus Left adalah salah satu Fungsi Excel yg digunakan untuk mengambil beberapa karakter teks dari arah kiri atau dari awal sebuah teks sesuai dengan informasi yg diberikan.
Cara Menggunakan Fungsi Left pada rumus excel adalah sbgai brkut:
LEFT(Teks; [JumlahKarakter])JumlahKarakter adalah informasi banyaknya karakter yg 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 yg digunakan untuk mengambil sebagian kata dlm 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 sbgai brkut:
MID(Teks; AwalKarakter; JumlahKarakter)AwalKarakter menunjukkan posisi awal pengambilan teks se&gkan JumlahKarakter adalah informasi banyaknya karakter yg akan diambil dari Teks.
Rumus RIGHT Excel
Fungsi atau Rumus Right mirip dengan fungsi Left. Be&ya jika fungsi Left megambil karakter dari awal teks, Se&gkan fungsi Right mengambil sejumlah karakter dari akhir teks atau dari arah kanan ke kiri.
RIGHT(Teks; [JumlahKarakter])JumlahKarakter adalah informasi banyaknya karakter yg akan diambil dari akhir Teks.Teks.
Penjelasan khusus untuk ketiga fungsi pengambilan teks ini bisa anda baca pada halaman tutorial excel brkut: Cara Mengambil Beberapa Karakter Teks pada Microsoft
Menggabungkan Rumus Excel VLOOKUP, LEFT, MID, RIGHT
Setlah kembali mengingat bagaimana cara menggunakan fungsi VLookup, Left, Mid, & Right brkutnya 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 yg digunakan sdh saya diberi nama(Named Range) untuk mempermudah penggunaan pada rumus excel yg 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 brkut: 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 yg 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 sbgai brkut:
LEFT(Kode;2)Teks Hasil dari fungsi Left tadi kemudian kita gunakan sbgai 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 yg dipakai adalah:
VLOOKUP(Kode;TabelKelas;2;FALSE)Pada Kode tersebut Kode Kelas adalah 1 karakter setlah kode Maskapai atau 1 karakter dimulai pada posisi ke-3. Untuk mengambil kode Kelas tersebut tentunya yg 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 terka&g kita juga harus lebih jeli saat menyusun sebuah kode-kode tabel.
Pada tabel tersebut kode Kelas adalah angka 1,2 & 3. Se&gkan hasil dari fungsi MID termasuk juga fungsi LEFT & RIGHT adalah sebuah teks, sehingga hasil rumus MID tadi jika dicari pada tabel kelas menggunakan fungsi VLookup maka dinyatakan tidak ditemukan atau #NA sebab parameter yg 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 brkut:
=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 sdh bisa menyimpulkan bahwa rumus apa yg 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 :
LINK DOWNLOAD TERKUNCI.
Silahkan SHARE untuk membuka kunci!
Bagaimana? mudah bukan. Sekian dulu untuk tutorial cara menggunakan Rumus Vlookup-Left, Rumus Vlookup-right & Vlookup-Mid kali ini.
Berikut beberapa tutorial excel tentang Rumus VLookup yg bisa anda pelajari:
- 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
Apabila masih ada pertanyaan slahkan disampaikan pada kolom komentar yg tersedia & jika anda rasa artikel tutorial ini bermanfaat jangan lupa untuk di share agar lebih banyak lagi yg akan ikut mendapatkan manfaatnya. Salam . Sumber https://www.kelasexcel.id
Belum ada Komentar untuk "Rumus Vlookup Sebagian Kata atau Teks (VLOOKUP - LEFT, MID, RIGHT)"
Posting Komentar