Saat bekerja dengan Excel, ada kalanya kita
mengalami kesulitan mengisi data menggunakan fungsi Hlookup dan Vlookup karena
fungsi ini memiliki keterbatasan. Fungsi Vlookup tidak bisa mengambil data yang
berada disebelah kiri kolom pencarian. Sedangkan fungsi Hlookup tidak bisa
mengambil data yang berada disebelah atas kolom pencarian.
Untuk itu anda bisa menggunakan kombinasi fungsi INDEX dan MATCH. Rumus yang menggunakan fungsi ini secara bersamaan, sedikit lebih kompleks dari rumus dengan fungsi HLOOKUP dan VLOOKUP. Kombinasi INDEX dan MATCH bisa menjawab/menyelesaikan apa yang tidak bisa dilakukan oleh fungsi HLOOKUP dan VLOOKUP.
Rumus gabungan INDEX-MATCH:
secara umum, Nomor Baris dan atau Nomor kolom pada
fungsi INDEX, diganti/ disubstitusi olef fungsi MATCH.
·
Rumus Index:
=Indeks ( Array; Row_num; [Column_num])
·
Rumus Match:
=Match (Lookup_value; Lookup_array; [Match_type])
Sehingga rumus gabunganya menjadi:
=INDEX (Array; MATCH (lookup_value, lookup_array, [match_type])
Keterangan:
- Array : merupakan Range (Kumpulan data) yang sesuai dengan informasi baris dan kolom yang ada.
- Lookup_value : Nilai kunci untuk pembacaan tabel.
- Lookup_array : Range (Kumpulan data) tempat nilai kunci berada.
- Match_type : Untuk menentukan ketepatan dalam pembacaan data.
Berikut
ini, penulis memberi beberapa contoh soal agar mempermudah dalam pemahaman
penggunaan fungsi index-match.
CONTOH
1. Penggunaan gabungan Index dan Match
Pada
saat anda akan mengisi kolom alamat (C15), maka anda bisa menggunakan
index+match (lihat rumus diatas).
INDEX (Array; MATCH (lookup_value, lookup_array, [match_type]))
Penjelasan:
- Array (D3:D11), adalah area kolom alamat. Ini sesuai dengan nilai yang akan anda cari. (anda mencari alamat pada C15)
- Lookup_value adalah nilai kunci yang terkait dengan nilai yang anda cari. Nilai kuncinya B15 (Cuplis).
- Lookup_array adalah kolom yang sesuai dengan nilai kunci. Nilai kuncinya B15 (Cuplis), maka kolom array nya adalah kolom nama pegawai (B3:B11).
- Match_type, anda ketikan angka 0 untuk memperoleh nilai yang tepat/mutlak.
Contoh Gabungan Index dan Match diatas, bisa anda lihat video tutorialnya. Klik video dibawah ini:
2.
Gabungan Fungsi Index dan
2 Match
INDEX (Array; MATCH (lookup_value, lookup_array, [match_type]); MATCH (lookup_value, lookup_array, [match_type]))
Penjelasan:
1. Index_array, karena anda mencari nilai Harga (G8) maka Nilai Area yang
sama dengan nilai harga adalah (E23:H25).
2. Lookup_value adalah E8 (TV), merupakan nilai kunci .
3. Lookup_array adalah (D23:D25), kolom yang sesuai dengan nilai kunci
(lookup value)
4. Match_type ketik angka 0 agar memiliki kebenaran nilai mutlak.
5. Lookup_value adalah F8 (Samsung), merupakan nilai kunci.
6. Lookup_array adalah (E22:H22), merupakan area yang sesuai dengan nilai
kuncinya (lookup value).
7. Match_type ketik angka 0 agar memiliki kebenaran nilai mutlak.
Contoh soal diatas, bisa anda lihat video tutorial Index dengan 2 Match di kanal Youtube penulis. Klik video dibawah ini:
3. Gabungan Fungsi Index dan 2 Match
Pada gambar diatas, anda mencari Nilai Sales/Penjualan (G24). Anda bisa menggunakan Fungsi:
INDEX(Array;MATCH(lookup_value,lookup_array[match_type]); MATCH(lookup_value, lookup_array, [match_type]))
Penjelasan:
1.
Index_array (B21:D32), Karena anda mencari Nilai Sales/Penjualan
(G24) maka Nilai Area yang sama dengan nilai yang dicari adalah (B21:D32).
2.
Lookup_value adalah G22 (01 Juli 2014), merupakan nilai kunci .
3.
Lookup_array adalah A21:A32, kolom yang sesuai dengan nilai kunci (lookup
value)
4.
Match_type ketik angka 0 agar memiliki kebenaran nilai mutlak.
5.
Lookup_value adalah G23 (Product C), merupakan nilai kunci.
6.
Lookup_array adalah B20:D20, merupakan area yang sesuai dengan nilai
kuncinya (lookup value) yaitu Product C (G23)
7.
Match_type ketik angka 0 agar memiliki kebenaran nilai mutlak.
Setelah selesai menuliskan rumus, kemudian tekan
CTRL+SHIFT+ENTER, maka rumus anda akan muncul tanda {} diawal dan akhir rumus
(lihat rumus pada gambar).
Coba
anda ganti kolom Month (G22) dan atau kolom Product (G23), amati hasilnya.
Contoh soal diatas, bisa anda lihat video tutorial Gabungan Index dan Match di kanal Youtube penulis. Klik video dibawah ini:
Sekian dari penulis. Salam .... *By Muji Yamianto
LATIHAN SOAL
1. Gabungan Fungsi Index dan Match
Penyelesaian soal Index dan Match diatas, bisa anda lihat pada kanal youtube kami. Klik video dibawah:
2. Gabungan Fungsi Index dan 2 Match
Penyelesaian dari soal
diatas, bisa anda lihat pada kanal youtube. Klik video dibawah ini:
3. Isilah kolom-kolom dibawah ini
Pertanyaan:
1.
Untuk mengisi Nama Barang dan Type/Merk gunakan fungsi Hlookup
atau Vlookup disertai fungsi left/right.
2.
Untuk mengisi Harga gunakan gabungan fungsi Index dan 2 Match.
Kunci jawaban dari soal diatas bisa anda lihat pada kanal youtube kami. Klik video / link dibawah ini:
NB:
Untuk menjawab/mengisi kolom Harga pada soal diatas, anda bisa juga menggunakan Gabungan Rumus H/Vlookup dan Match. Untuk lebih jelasnya silahkan anda pelajari di:
Gabungan rumus Hlookup dan Match. Klik disini!
Penggunaan Fungsi Hlookup danVlookup. Klik disini!
Kami juga menyediakan soal-soal latihan excel untuk anda
Soal meliputi semua materi/ artikel yang saya tulis di Blog
Silahkan anda download di: Google Playstore
Caranya:
Klik: Google Plystore di perangkat/ HP anda
Pilih menu: Apps/ Aplikasi (letaknya layar bawah)
Pada Search Engine ketik:
Informasi lengkap soal-soalnya, and klik link
dibawah ini:
https://mujiyamianto.blogspot.com/2020/03/penjualan-soal-soal-latihan-excel.html
Tidak ada komentar:
Posting Komentar