Home » Belajar Excel Online » Pencarian & Referensi » Fungsi INDEX, MATCH, dan INDEX MATCH untuk Analisis Data

Fungsi INDEX, MATCH, dan INDEX MATCH untuk Analisis Data

posted in: Pencarian & Referensi

Fungsi INDEX MATCH merupakan cara lain dari penggunaan fungsi VLOOKUP dan HLOOKUP. Menggunakan fungsi INDEX MATCH lebih fleksibel dalam melakukan pencarian data.

INDEX dan MATCH merupakan dua fungsi yang berbeda, fungsi INDEX sendiri digunakan untuk mengambil nilai sel dalam tabel berdasarkan kolom dan baris, sedangkan fungsi MATCH untuk mencari posisi sel dalam baris atau kolom.

Untuk mendownload file latihan silakan disimak hingga tuntas

Fungsi INDEX

Fungsi INDEX dibagi menjadi dua cara, yaitu dengan menggunakan Index Array dan menggunakan Index Reference.

INDEX ARRAY

Argumen dalam penggunaan Form INDEX ARRAY adalah :

INDEX(array, row_num, [column_num])
  1. array : merupakan range data (table database) yang akan diambil nilainya. Jika array hanya berisikan satu baris atau satu kolom, maka argumen row_num atau column_num bersifat optional. Namun jika array memiliki lebih dari satu baris atau satu kolom, maka INDEX akan mengembalikan array dari seluruh baris atau kolom ke dalam nilai array tersebut.
  2. row_number : menunjukkan nomor baris dari array yang akan diambil nilai atau referensinya
  3. column_num: menunjukkan nomor kolom dari array yang akan diambil nilai atau referensinya

Contoh 1

Dalam tabel di bawah ini kita akan mendapatkan hasil Feb pada sel D2 dengan menggunakan fungsi INDEX ARRAY.

index match

Dari persoalan dalam tabel di atas (contoh 1), pada sel D2 dapat diketikkan rumusan :

=INDEX(A2:B13,2,2) ~ menghasilkan Feb pada sel D2

Dimana A2:B13 adalah sebagai array-nya, 2 angka pertama sebagai row_num dan 2 angka setelahnya sebagai column_num.

Apabila ingin mendapatkan hasil Jul maka row_num nya dapat diketikkan angka 7 dan pada column-num nya dapat diketikkan angka 2

=INDEX(A2:B13,7,2) ~ menghasilkan Jul pada sel D2

Contoh 2

Contoh lain dalam penggunaan INDEX ARRAY :

index match excel

Pada contoh 2 , INDEX ARRAY juga dapat dilakukan tanpa penggunaan column_num, dengan memakai rumus :

=INDEX(B2:B13,2) ~ menghasilkan Bevis Conference Table pada sel H2

Pada sel H7 dan sel H8 penulisan INDEX ARRAY di row_num nya digantikan dengan angka nol (0), maka penulisannya adalah sebagai berikut :

=INDEX(B2:B13,0,3) ~ menghasilkan 37,093,950 pada sel H7
=INDEX(B2:B13,0,2) ~ menghasilkan Logitech Keyboard pada sel H8

Sedangkan pada sel C15 penulisan INDEX ARRAY pada column_num nya diganti dengan angka nol (0)

=INDEX(B2:B13,3,0) ~ menghasilkan 37,093,950 pada sel C15

CATATAN INDEX ARRAY
Pada INDEX ARRAY, jika row_num dan column_num sama-sama digunakan, maka INDEX akan mengambil nilai pada titik pertemuan (intersection) dari row_num dan column_num. Namun bila column_num tidak digunakan, row_num harus dipilih (di blok) hanya satu kolom saja. Jika tidak, maka akan menghasilkan nilai #REF! error.
Jika column_num diketik angka nol (0), INDEX akan mengembalikan nilai array pada kolom yang sejajar dengan sel hasil
Jika row_num diketik angka nol (0), INDEX akan mengembalikan nilai array pada baris yang sejajar dengan sel hasil. Jika tidak sejajarakan muncul nilai #VALUE!
row_num dan column_num harus merujuk pada sel dalam table array atau tabel database, jika tidak merujuk ke dalamnya, maka akan menghasilkan nilai #REF! error.
Baca Juga : 11 Rumus Excel Error

INDEX REFERENCE

Argumen dalam penggunaan Form INDEX REFERENCE adalah

INDEX(reference, row_num, [column_num], [area_num])
  1. reference : merupakan satu atau beberapa rentang sel dari table data yang akan di index.
  2. row_num : baris pada table yang akan diambil nilainya
  3. column_num : kolom pada table yang akan diambil nilainya
  4. area_num : (opsional) merupakan area sel yang diambil nilainya untuk ditampilkan dalam data sel. Jika dihilangkan maka akan mengembalikan hasil pada rentang yang pertama yang ada dalam referensi

Yuk cek berapa gaji profesi seluruh dunia hanya di: Berapagaji.com

Contoh 3

index match reference

Pada contoh 3 INDEX REFERENCE, terdapat 3 area yang berbeda. Area 1 berisikan Jan-Apr, Area 2 bulan May-Aug , dan Area 3 bulan Sep-Dec. Untuk mendapatkan nilai index pada sel D9 maka kita perlu memasukkan rumus :

=INDEX((A3:C6,E3:G6,I3:K6),2,0,1)) ~ menghasilkan 46,261,500 pada sel D9
index match array
Nilai referensi yang digunakan adalah A3:C6 sebagai Area 1, E3:G6 sebagai Area 2, dan I3:K6 sebagai Area 3. Bila hanya ingin menggunakan Area 1 dan Area 2 sebagai referensinya maka cukup memasukkan A3:C6 dan E3:G6.
index match row num
Angka 2 pada gambar rumus berfungsi sebagai row_num yang akan mengambil baris nomor 2 yakni pada row 4
index match column num
Angka nol (0) berfungsi sebagai kolom yang akan diambil nilainya yang artinya tidak menggunakan column_num pada rumus INDEX REFERENCE ini. Bila angka nol (0) diganti dengan angka 1 maka hasil akan berubah menjadi FEB
index match area num
Angka 1 berfungsi sebagai area yang akan digunakan. Karena dalam contoh 3 menggunakan tiga area. Dengan menggunakan nilai 1, maka area yang diambil adalah Area 1. Jika angkanya dimasukkan 2 maka Area 2 yang akan diambil sebagai area referensinya
CATATAN INDEX REFERENCE
INDEX REFERENCE dapat digunakan pada beberapa rentang. Contoh : jika satu rentang maka ditulis INDEX ( (A3:C6 ),……..) jika beberapa rentang maka ditulis INDEX ( ( A2:C6 , E3:G6, dst ) , …….).
(lihat contoh gambar di atas)
row_num dan column_num harus merujuk pada sel dalam table array atau dalam database, jika tidak merujuk ke dalamnya maka akan menghasilkan nilai #REF! error.
Jika row_num diketik angka nol (0), INDEX akan mengembalikan nilai array pada baris yang sejajar dengan sel hasil
Jika column_num diketik angka nol (0), INDEX akan mengembalikan nilai array pada kolom yang sejajar dengan sel hasil

FUNGSI MATCH

Fungsi MATCH dalam excel adalah untuk mencari nilai posisi pada array.
Argumen dalam penggunaan rumus MATCH adalah :

MATCH( lookup_value, lookup_array, [match_type])
  1. lookup_value : merupakan nilai yang akan dicari, lookup_value dapat berupa angka, teks, nilai logika, atau referensi suatu nilai.
  2. lookup_array : merupakan kumpulan dari nilai pada suatu baris atau kolom yang akan dicari nilainya.
  3. match_type : type yang digunakan dalam rumus match.
    Ada 3 type yang terdapat dalam penentuan rumus MATCH
    • match_type = 1 atau tanpa menggunakan nilai match_type ; diperlukan untuk menemukan nilai terbesar yang lebih kecil atau sama dengan nilai pencarian (lookup_value)
      Dalam penggunan match_type = 1 diperlukan Ascending Order (A-Z) atau nilai terkecil ke nilai terbesar pada urutan nilai arraynya.
    • match type = 0 digunakan untuk mencari nilai pertama dalam array yang sama dengan nilai pencarian (lookup_value)
    • match type = -1 diperlukan untuk menemukan nilai terlkecil yang lebih besar atau sama dengan nilai pencarian (lookup_value)
      Dalam penggunaan match_type = -1 diperlukan Descending Order (Z-A) atau nilai terbesar ke nilai kecil pada urutan nilai arraynya.

Contoh 4

index match 2

Pada contoh 4 penggunaan MATCH dengan rumusan :

=MATCH(G5,B2:B15,1) ~ menghasilkan nilai 5 pada sel G6
Nilai lookup_value diambil pada sel G5 yaitu nilai ID 74
lookup_array sebagai database adalah B2:B15
Angka 1 pada nilai match_type bisa digunakan karena urutan array pada kolom B (ID) sudah berurutan.
CATATAN MATCH
Fungsi MATCH adalah untuk mencari posisi dari pencarian array. Nilai yang ada pada hasil bukan nilai dalam array melainkan posisi pada array itu sendiri
MATCH tidak sensitive terhadap penggunaan huruf besar dan kecil, yang artinya karakter besar dan kecil tidak mempengaruhi nilai hasil MATCH
Jika nilai pencarian tidak terdapat dalam array, maka rumus akan menghasilkan nilai #N/A
(Not Available)
Jika dalam nilai array terdapat dua sel dengan nilai/karakter yang sama, maka yang diambil adalah sel dengan nilai/karakter yang pertama muncul.
Contoh : jika dalam array terdapat kata Jan pada sel A1 dan A3, maka nilai A1 yang akan menjadi hasil dari MATCH

FUNGSI INDEX-MATCH

Setelah memahami masing-masing dari fungsi INDEX dan MATCH, maka kita akan menggabungkan rumusan tersebut menjadi INDEX-MATCH.

Argumen dalam penulisan INDEX MATCH adalah :

INDEX ( array ), MATCH ( lookup_value, lookup_array, [match_type])

Contoh 5

index match

Pada contoh 5, rumus yang dimasukkan adalah :

=INDEX(C2:C13), MATCH(F4, A2:A13, 0)~ menghasilkan nilai 50,839,600 pada sel F5

index match row num
INDEX C2:C13 adalah nilai array atau reference yang akan ditampilkan pada nilai sales di sel F5
MATCH F4 merupakan nilai lookup_value yang akan dicari nilainya
MATCH A2:A13 adalah sebagai nilai array dari lookup_value, yaitu table data dari Jan-Des
Angka nol (0) pada MATCH berfungsi sebagai match_type dengan hasil yang akurat

CATATAN INDEX MATCH
Pada INDEX MATCH, jumlah baris dalam argumen array pada INDEX harus sesuai dengan jumlah argumen lookup_array pada MATCH. karena jika tidak sesuai rumus akan menghasilkan nilai #REF!
Hasil nilai yang akan dicari pada INDEX MATCH dapat berada pada kedua sisi baik di sisi kanan maupun sisi kiri dalam tabel data yang berbentuk vertikal.
Hasil nilai yang akan dicari pada INDEX MATCH dapat berada pada kedua sisi baik di sisi atas maupun sisi bawah dalam tabel data yang berbentuk horizontal.
Jika dalam nilai array terdapat dua sel dengan nilai/karakter yang sama, maka yang diambil adalah sel dengan nilai/karakter yang pertama muncul.
Contoh : jika dalam array terdapat kata Jan pada sel A1 dan A3, maka nilai A1 yang akan menjadi hasil dari MATCH

Leave a Reply

Your email address will not be published.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.