LATIHAN SOAL EXCEL: gabungan vlookup dan match

Ada banyak rumus/formula kala kita menggunakan MS. Excel, rumus tersebut bisa berdiri sendiri maupun digabungkan satu dengan yang lainya, sehingga kita dapat bekerja secara efektif.

Dikesempatan ini, sedikit penulis jelaskan Gabungan Fungsi Vlookup dengan Fungsi Match. Saya tidak akan menjelaskan apa itu fungsi Vlookup dan fungsi Match karena telah saya ulas di artikel saya yang lain.

Dalam artikel ini, saya berikan 2 varian soal yang sering kita jumpai saat kita bekerja menggunakan MS. Excel:

I. Varian soal 1

Perhatikan gambar soal dibawah:

Anda perhatikan Tabel Daftar Harga (gambar diatas). Tabel diatas memiliki 2 variabel, yaitu BARANG ELEKTRONIK (meliputi TV, Smartphone dan Radio/tape) dan MEREK (meliputi Samsung, LG, Phillips, Polytron). Kedua variabel tersebut saling memiliki keterkaitan berupa: harga barang. Jadi untuk mengisi Kolom Harga, anda bisa menggunakan Gabungan Rumus Vlookup dan Match.

Rumus Vlookup:

=Vlookup( Lookup_value; Table_array; Colum_index_num; [Range_lookup])

Rumus Match:

=Match( Lookup_value; Lookup_array; [Match_type])

Kedua rumus/formula diatas digabung menjadi satu, sehingga diperoleh rumus:

=Vlookup( Lookup_value; Table_array; Match( Lookup_value; Lookup_array; [Match_type]); [Range_lookup])

Coba anda perhatikan, dari rumus diatas nampak bahwa: Column_index_num pada Fungsi Vlookup diganti (disubtitusi) oleh Fungsi Match. Maka rumusnya akan nampak seperti gambar dibawah ini:

Penjelasan dari rumus gambar diatas (sel E38):

=VLOOKUP(C38;$B$53:$F$55;MATCH(D38;$B$52:$F$52;0);FALSE)

C38 : merupakan Lookup Value (nilai kunci) dari Vlookup.
$B$53:$F$55 : merupakan Table Array (range) dari Vlookup. Setelah diblok jangan lupa tekan F4 agar alamat selnya absolut.
D38 : merupakan Lookup value (nilai kunci) dari Match.
$B$52:$F$52 : merupakan Table Array (range) dari Match.
0 : merupakan Match Type dari Match.
FALSE : merupakan Range lookup dari Vlookup.

Maka pada sel E38 akan diperoleh nilai/harga Rp 4.000.000 (Harga TV Samsung). Selanjutnya anda copy kebawah. Demikian pembahasan dari varian soal no 1.

Video Tutorial Vlookup dan Match dari contoh diatas bisa anda lihat di kanal Youtube penulis, Klik Link dibawah ini:

https://youtu.be/9XXtrA6wBGs

II. Varian Soal 2

Gambar dibawah, terdapat sebuah tabel berbentuk Vertikal yang terdiri dari 4 kolom yaitu kolom no, nama, kota dan provinsi. Disebelahnya terdapat tabel kecil yang terdiri dari 2 kolom yaitu kolom nama dan kota. Tugas anda adalah memasukan/menginput data pada kolom nama (sel F6), kemudian anda masukan Gabungan Rumus Vlookup dan Match pada kolom kota (sel G6), sehingga kolom kota (sel G6) memiliki kesesuaian data dengan kolom nama (sel F6).

Pada kolom kota (sel G6), masukan rumus:

=VLOOKUP(F6;B3:D8;MATCH(G5;B2:D2;0);FALSE)

Keterangan

Fungsi Vlookup terdiri :

F6 : sel data yang dipakai sebagai nilai kunci
B3:D8 : area atau range pada tabel sumber
FALSE : nilai yang dicari harus presisi

Fungsi Match terdiri:

G5 : sel data yang dipakai sebagai nilai kunci
B2:D2 : area atau range yang memiliki kesamaan dengan nilai kunci (G5) (pada tabel sumber)
0 : agar memiliki nilai yang presisi/match

Coba anda ganti nama Mak Lampir (sel F6) dengan nama lainya, perhatikan perubahan pada kolom kota (sel G6)

Dari penjelasan diatas, sangat mudah bagi kita untuk mengaplikasikan penggunaan kedua rumus diatas. Berdasarkan pengalaman penulis, Gabungan fungsi Vlookup dan Match diatas memiliki kegunaan yang sama dengan Gabungan Fungsi Index dan Match.

Semoga bermanfaat ...... ^{*By Muji Yamianto}

Anda bisa juga melihat video tutorial Gabungan Vlookup dan Match dari contoh soal diatas, klik link dibawah ini:

https://youtu.be/rLLLDGEI91U

Anda juga bisa baca artikel saya yang lain dibawah ini:

GabunganFungsi Hlookup dan Match. Klik disini!

GabunganFungsi Indexdan Match. Klik disini!

Gabungan If dengan Vlookup. Klik disini

PenggunaanDefine Name pada H/Vlookup

PenggunaanAngka Teks di H/Vlookup

KUMPULAN SOAL-SOAL EXCEL

LATIHAN SOAL

1. Isilah kolom tarif kamar dengan menggunakan Gabungan Rumus Vlookup dan Match.

Soal:

a. Tarif Kamar

Besarnya tarif kamar ditentukan oleh kelas kamar dan type kamar seperti yang tertera dalam TABEL HARGA!

b. Total bayar = Tarif kamar x Lama opnam

Cara penyelesaian dari soal Gabungan Vlookup dan Match diatas, bisa anda lihat video tutorialnya pada kanal youtube kami. Klik Link dibawah ini:

https://youtu.be/AOfkmCmvkjQ

2. Isilah kolom-kolom dibawah ini:

          Soal:
   1.        Gunakan rumus Hlookup
   2.        Gunakan rumus Gabungan Vlookup dan Match
   3.        Gunakan rumus Gabungan Vlookup dan Match