Fungsi INDEX pada Excel

Saat mau ambil data dari suatu tabel atau array berdasarkan nilai kunci tertentu, biasanya kita pakai LOOKUP, HLOOKUP, VLOOKUP, atau XLOOKUP.

Fungsi lain, yaitu fungsi INDEX, menawarkan kemampuan serupa, tapi unik di sisi lain.

Simak artikel ini untuk tahu kemampuan lebih fungsi INDEX pada Excel.

File: fungsi-index-excel.xlsx.

Apakah Fungsi INDEX pada Excel Itu?

Fungsi INDEX pada Excel adalah fungsi yang mengembalikan nilai atau referensi nilai dari suatu range atau array.

Fungsi INDEX terbagi menjadi dua, yaitu:

• Bentuk array. Fungsi INDEX dalam bentuk ini mengembalikan nilai dari suatu cell atau array.
• Bentuk referensi. Fungsi INDEX ini mengembalikan referensi dari satu atau lebih cell.

Rumus INDEX Array

Rumus INDEX dalam bentuk array mengembalikan elemen dari suatu tabel atau array berdasarkan baris dan kolom tertentu.

Sintaks

INDEX(array; row_num; [column_num])

• array (wajib). Range atau array constant.
  • Bila array hanya terdiri dari satu baris atau kolom, maka row_num dan column_num adalah opsional.
  • Bila array terdiri lebih dari satu baris atau kolom dan hanya row_num atau column_num yang digunakan, maka INDEX akan mengembalikan semua nilai dalam baris atau kolom tersebut.
• row_num (wajib). Indeks baris di dalam array dari mana satu atau lebih nilai dikembalikan. row_num opsional bila column_num disediakan.
• column_num (opsional). Indeks kolom dari mana satu atau lebih nilai dikembalikan. Parameter ini wajib bila row_num tidak disertakan.

Mengenai Rumus INDEX Array

• Bila row_num dan column_num disertakan, INDEX akan mengembalikan nilai cell yang menjadi perpotongan antara baris dan kolom tersebut.
• Bila row_num atau column_num bernilai nol atau lebih besar dari jumlah baris atau kolom array, pesan error #REF! tampil.
• Jika row_num bernilai nol, maka INDEX akan mengembalikan seluruh isi kolom yang dinyatakan oleh column_num.
• Sebaliknya, jika column_num bernilai nol, INDEX akan mengembalikan seluruh isi baris array berdasarkan row_num.

Rumus INDEX Referensi

Rumus INDEX referensi mengembalikan satu atau lebih referensi cell yang menjadi perpotongan kolom dan baris.

Sintaks

INDEX(reference; row_num; [column_num]; [area_num])

• reference (wajib). Referensi dari satu atau lebih cell.
  • Bila jumlah range lebih dari satu, pisahkan setiap range dengan pemisah daftar dan tempatkan semuanya di dalam kurung.
  • Bila setiap area cell hanya memiliki satu baris atau kolom, parameter row_num atau column_num adalah opsional.
• row_num (wajib). Indeks baris di dalam reference di mana nilai-nilai kembalian berada.
• column_num (opsional). Indeks kolom di dalam reference di mana nilai-nilai kembalian berada.
• area_num (opsional). Indeks range di dalam reference di mana nilai kembalian berada. Bila tidak dituliskan, area_num menunjuk pada range pertama di dalam reference.

Cara Menggunakan Fungsi INDEX Pada Excel

INDEX Mengembalikan Nilai Pada Kolom dan Baris Tertentu

Untuk mengembalikan nilai pada kolom dan baris tertentu dengan fungsi INDEX, tentukan range, indeks kolom, dan indeks baris range tersebut.

=INDEX(B2:C11; G2; G3)

Anatomi rumus INDEX di atas adalah sebagai berikut:

• B2:C11 adalah array, yaitu range di mana nilai yang diinginkan berada.
• F2, bernilai 6, adalah row_num, yaitu posisi baris nilai yang diinginkan.
• F3, bernilai 2, adalah column_num, yaitu posisi kolom nilai yang diinginkan.

Rumus INDEX akan mengambil cell pada range B2:C11 yang menjadi perpotongan antara baris enam dan kolom tiga. Cell tersebut adalah D7, yang bernilai 2332000.

INDEX Mengembalikan Nilai Pada Baris atau Kolom Tertentu

Selain mengembalikan nilai yang menjadi perpotongan baris dan kolom, fungsi INDEX pada Excel juga mampu mengembalikan nilai-nilai pada baris atau kolom tertentu.

Untuk mengembalikan nilai-nilai pada suatu baris, tentukan baris yang diinginkan dan berikan nilai nol bagi kolom.

=INDEX(B2:C11; F2; 0)

Rumus di atas mencari baris ketujuh dari range B2:C11. Karena column_num bernilai nol, INDEX mengembalikan seluruh nilai pada baris bersangkutan.

Hasil rumus akan berupa array.

Untuk mengambil nilai-nilai dari suatu kolom, tentukan indeks kolom bersangkutan dan berikan nol bagi indeks baris.

=INDEX(B2:C11; 0; E2)

E2 adalah referensi cell bernilai 1.

Dengan row_num bernilai nol, rumus di atas mengambil seluruh nilai dari kolom pertama pada range B2:C11, seperti terlihat pada gambar.

Rumus INDEX Excel Range-range Tidak Berdekatan

Salah satu kelebihan fungsi INDEX pada Excel adalah kemampuan untuk mengolah satu atau lebih range sebagai area evaluasi.

=INDEX((B2:D5; B8:D11); G2; G3; G4)

Bila dipecah, rumus di atas terdiri dari:

• (B2:D5; B8:D11) adalah reference, yaitu daftar range yang dianalisa. Berhubung ada lebih dari satu range, maka semua range tersebut harus ditempatkan di dalam kurung dan masing-masing dipisahkan oleh pemisah daftar.
• G2 adalah row_num, yaitu baris di mana nilai kembalian berada.
• G3 adalah column_num, yaitu kolom di mana nilai kembalian berada.
• G4 adalah area_num, yaitu angka untuk menentukan range mana yang akan diambil dari reference.

Berdasarkan batasan parameter, rumus mengembalikan nilai 3477000, yaitu nilai pada baris ketiga, kolom pertama dari range B8:D11 (range kedua pada daftar).

Rumus INDEX dan MATCH

Perhatikan kembali rumus-rumus di atas. Semuanya agak sulit dibaca karena kita harus selalu melihat di mana kolom atau baris berada.

Fungsi MATCH menyelesaikan masalah ini dengan membuat rumus lebih mudah dibaca.

Dengan fungsi MATCH, rumus =INDEX(B2:C11; F2; F3) pada Gambar 01 menjadi:

=INDEX(B2:C11; MATCH(F2; A2:A11; 0); MATCH(F3; B1:C1; 0))

Kita mengganti 6 (F2) menjadi nama penjual (Jenny) dan 2 (F3) menjadi nama bulan (Februari).

Fungsi MATCH mencari posisi di mana Jenny berada di dalam range A2:A11 (MATCH(F2; A2:A11; 0)) dan Februari di range B1:C1 (MATCH(F3; B1:C1; 0)).

Perhatikan panjang A2:A11 dan lebar B1:C1 sama dengan panjang dan lebar B2:C11.

Rumus jadi lebih panjang. Tapi, secara keseluruhan lebih mudah dibaca, serta lebih rentan perubahan.

Gabungan Rumus INDEX Excel dengan SUM, AVERAGE, MIN, MAX

Pada bagian sebelumnya, kita belajar bahwa fungsi INDEX pada Excel dapat mengembalikan array.

Pada kelanjutannya, nilai array ini bisa diolah kembali menggunakan fungsi-fungsi yang sesuai.

Pada Gambar 03, hasil rumus adalah array, yang merupakan penjualan di bulan Januari.

Dengan menempatkan array tersebut di dalam fungsi-fungsi SUM, AVERAGE, MIN, dan MAX, kita memperoleh nilai-nilai jumlah, rata-rata, terkecil, dan terbesar dari penjualan bulan bersangkutan.

=SUM(INDEX(B5:C14; 0; MATCH(A2; B4:C4; 0)))

=AVERAGE(INDEX(B5:C14; 0; MATCH(A2; B4:C4; 0)))

=MIN(INDEX(B5:C14; 0; MATCH(A2; B4:C4; 0)))

=MAX(INDEX(B5:C14; 0; MATCH(A2; B4:C4; 0)))

Fungsi MATCH pada rumus-rumus di atas mencari posisi kolom bulan Januari di range B4:C4.

Rumus SUMIF INDEX MATCH

Bila hasil INDEX yang berupa array bisa diolah oleh fungsi seperti SUM, bagaimana dengan SUMIF?

Fungsi SUMIF hanya menjumlahkan angka-angka yang sesuai dengan kriteria, alih-alih semuanya.

=SUMIF(INDEX(B2:C11; 0; MATCH(F2; B1:C1; 0)); ">"&F3)

Rumus di atas menjumlahkan penjualan pada bulan Januari yang lebih besar dari pada 3,500,000.

Rumus INDEX Excel Memilih Satu dari Beberapa Range

Memilih salah satu range dalam daftar dilakukan dengan memberikan nilai nol (atau tidak perlu nilai sama sekali) bagi row_num dan column_num dan indeks (di atas nol) range bagi area_num yang mewakili posisi salah satu range.

Rumus di bawah terdiri dari dua range, yaitu B2:B11 dan C2:C11. Dengan row_num dan column_num kosong dan area_num sama dengan 1, rumus mengembalikan range pertama, yaitu B2:B11.

=INDEX((B2:B11; C2:C11); ; ; E2)

Excel mengijinkan range-range dengan dimensi-dimensi yang berbeda. Misalkan, range pertama punya sepuluh baris dan range lainnya delapan baris.