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?
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.
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.
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 |
Tabel
Maskapai |
$G$2:$H$7 |
Tabel
Class |
$J$2:$K$5 |
Tabel
Tujuan |
$M$2:$N$9 |
Tabel Nama Range |
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:
Advertisements
=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 :
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.
Berikut
beberapa tutorial ex