Adonan Fungsi Index Dan Match, Disertai Beberapa Teladan Dan Soal Latihan

Menggunakan INDEX dan MATCH secara bersamaan

Saat bekerja dengan Excel, ada kalanya kita mengalami kesulitan mengisi data memakai fungsi Hlookup dan Vlookup alasannya fungsi ini mempunyai keterbatasan. Fungsi Vlookup tidak dapat mengambil data yang berada disebelah kiri kolom pencarian. Sedangkan fungsi Hlookup tidak dapat mengambil data yang berada disebelah atas kolom pencarian.

Untuk itu anda dapat memakai kombinasi fungsi INDEX dan MATCH. Rumus yang memakai fungsi ini secara bersamaan, sedikit lebih kompleks dari rumus dengan fungsi HLOOKUP dan VLOOKUP. Kombinasi INDEX dan MATCH dapat menjawab/menyelesaikan apa yang tidak dapat dilakukan oleh  fungsi HLOOKUP dan VLOOKUP. 

Penggunaan fungsi Index dan penggunaan fungsi Match, telah saya paparkan pada artikel lain. Bagi anda yang masih belum mengerti silahkan berkunjung ke Penggunaan Fungsi Index dan Fungsi Match. Klik disini!

  Rumus adonan INDEX-MATCH: secara umum, Nomor Baris dan atau Nomor kolom pada fungsi INDEX, diganti/ disubstitusi olef fungsi MATCH. 

Rumus Index:

=Index( 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:

1.      Array: merupakan Range (Kumpulan data) yang sesuai dengan informasi baris         dan kolom yang ada.

2.      Lookup_value: Nilai kunci untuk pembacaan tabel.

3.      Lookup_array: Range (Kumpulan data) daerah nilai kunci berada.

4.      Match_type: Untuk memilih ketepatan dalam pembacaan data.

            Berikut ini, penulis memberi beberapa rujukan soal biar mempermudah dalam pemahaman penggunaan fungsi index-match.

CONTOH  

1.  Penggunaan adonan Index dan Match

                   

                                                                                                                                   

            Pada ketika anda akan mengisi kolom alamat (C15), maka anda dapat memakai index+match (lihat rumus diatas).

INDEX(Array; MATCH(lookup_value, lookup_array, [match_type]))

Penjelasan:                                               

1.      Array (D3:D11), yaitu area kolom alamat. Ini sesuai dengan nilai yang akan anda cari. (anda mencari alamat pada C15)

2.      Lookup_value yaitu nilai kunci yang terkait dengan nilai yang anda cari. Nilai kuncinya B15 (Cuplis).

3.      Lookup_array yaitu kolom yang sesuai dengan nilai kunci. Nilai kuncinya B15 (Cuplis), maka kolom array nya yaitu kolom nama pegawai (B3:B11).

4.      Match_type, anda ketikan angka 0 untuk memperoleh nilai yang tepat/mutlak.

Coba anda ganti nama Cuplis dengan Nama Pegawai yang lain, perhatikan perubahan pada tanggapan anda.

       Contoh diatas, dapat anda lihat video tutorialnya. Klik link dibawah ini:
      https://www.youtube.com/watch?v=WJkL66j4ikQ

CONTOH

1. Gabungan Fungsi Index dan 2 Match

Pada gambar diatas, anda mencari nilai Kolom Harga (G8). Anda dapat memakai Fungsi Gabungan:
INDEX(Array; MATCH(lookup_value, lookup_array, [match_type]); MATCH(lookup_value, lookup_array, [match_type]))
Penjelasan:

1.      Index_array, alasannya anda mencari nilai Harga (G8) maka Nilai Area yang sama dengan nilai harga yaitu (E23:H25).

2.      Lookup_value adalah  E8 (TV), merupakan nilai kunci .

3.      Lookup_array yaitu (D23:D25), kolom yang sesuai dengan nilai kunci (lookup value)

4.      Match_type ketik angka 0 biar mempunyai kebenaran nilai mutlak.

5.      Lookup_value adalah F8 (Samsung), merupakan nilai kunci.

6.      Lookup_array yaitu (E22:H22), merupakan area yang sesuai dengan nilai kuncinya (lookup value).

7.      Match_type ketik angka 0 biar mempunyai kebenaran nilai mutlak.

( Jangan lupa tekan F4 biar alamat sel absolut)

             Contoh soal diatas, dapat anda lihat video tutorialnya. Klik link dibawah ini:
           https://www.youtube.com/watch?v=lI44YoYIKfU

2. Gabungan Fungsi Index dan 2 Match        

                             

            Pada gambar diatas, anda mencari Nilai Sales/Penjualan (G24). Anda dapat memakai 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 yaitu (B21:D32).

2.      Lookup_value yaitu G22 (01 Juli 2014), merupakan nilai kunci .

3.      Lookup_array yaitu A21:A32, kolom yang sesuai dengan nilai kunci (lookup value)

4.      Match_type ketik angka 0 biar mempunyai kebenaran nilai mutlak.

5.      Lookup_value adalah G23 (Product C), merupakan nilai kunci.

6.      Lookup_array yaitu B20:D20, merupakan area yang sesuai dengan nilai kuncinya (lookup value) yaitu Product C (G23)

7.      Match_type ketik angka 0 biar mempunyai kebenaran nilai mutlak.

Setelah selesai menuliskan rumus, lalu tekan CTRL+SHIFT+ENTER, maka rumus anda akan muncul tanda {} diawal dan final rumus (lihat rumus pada gambar).

            Coba anda ganti kolom Month (G22) dan atau kolom Product (G23), amati hasilnya.

Berikut ini penulis berikan beberapa latihan sederhana berkaitan dengan beberapa Fungsi diatas.

LATIHAN

1. Gabungan Fungsi Index dan Match

           
      Penyelesaian soal diatas, dapat anda lihat pada saluran youtube kami. Klik link dibawah:
       https://www.youtube.com/watch?v=TlcpKadjaZE

2. Gabungan Fungsi Index dan 2 Match

        

     Penyelesaian dari soal diatas, dapat anda lihat pada saluran youtube. Klik link dibawah ini:

      https://www.youtube.com/watch?v=owtT0zYAG1Q

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 adonan fungsi Index dan 2 Match.

NB:

Untuk menjawab/mengisi kolom Harga pada soal diatas, anda dapat juga memakai Gabungan Rumus H/Vlookup dan Match. Untuk lebih jelasnya silahkan anda pelajari di:

§  Gabungan rumus Vlookup dan Match. Klik disini!

§  Gabungan rumus Hlookup dan Match. Klik disini!

Penggunaan Fungsi Hlookup danVlookup. Klik disini! 

Untuk lebih memahami materi diatas
Kami menyediakan Latihan Soal-Soal Excel
Soal sebanyak 100 nomor (meliputi semua artikel yang saya tulis di blog)
Harga Rp 20.000 
Info ke E-mail:  mujiyamianto@gmail.com

Sumber http://mujiyamianto.blogspot.com

Share this post