Anda disini: M Jurnal » Rumus Excel » Rumus INDEX Excel: 7 Contoh + Tips Cara Menggunakannya

Rumus INDEX Excel: 7 Contoh + Tips Cara Menggunakannya

Pengertian Rumus INDEX Excel adalah salah satu dari fungsi Lookup & Reference di Excel yang berguna untuk mencari dan mengambil nilai secara horizontal maupun vertical berdasarkan nomor kolom dan nomor baris dari sebuah tabel atau Range Data.

Dengan kata lain, Rumus INDEX akan mengambil nilai pada sebuah Cell dari Tabel / Range Data yang menjadi Sumber Referensi berdasarkan kriteria pencarian.

Penting! Sebelum Kita mulai, pastikan Anda sudah memahami apa itu Range Data pada Excel. Karena dalam menggunakan Fungsi INDEX, Anda akan selalu berurusan dengan Range Data. Jika Anda belum tahu apa itu Range Data, silahkan kunjungi Panduan Range Excel M Jurnal.

Aturan Penulisan Rumus INDEX Excel

Agar tidak salah dalam menggunakan Rumus INDEX, Anda wajib memahami aturan penulisannya. Sama seperti Rumus Excel yang lainnya. Namun bedanya, ada 2 jenis Rumus INDEX yang biasa dikenal dengan INDEX Array dan INDEX Reference. Kedua jenis Rumus tersebut memiliki aturan penulisannya masing-masing. Mari kita pelajari lebih lanjut…

Penting! Sebelum memulai, Perhatikan bahwa Software Excel Saya menggunakan Format English. Operator pemisah argument pada rumus untuk Format English tidak sama dengan format Indonesia. Format English menggunakan tanda koma “,” sedangkan format Indonesia menggunakan tanda titik koma “;”. Jika belum tahu Software Anda menggunakan format apa atau jika ingin mengubah format Indonesia ke English atau sebaliknya, silahkan kunjungi Panduan Format English VS Indonesia

Aturan Penulisan Rumus INDEX Array

Tulis Fungsi / Rumus INDEX Array:
=INDEX(array,row_num,[column_num])

Artinya:
Ambil Data dari array yang terdapat pada row_num dan [column_num] dari Range Data

Terlihat ada 3 argument untuk Fungsi INDEX Array. Ketiga argument tersebut ada yang bersifat wajib dan ada yang bersifat optional.

  1. array (Wajib) adalah sebuah Range Data, bisa berupa 1 baris dan banyak kolom (misal A1:E1) atau banyak baris dan 1 kolom (misal A1:A10) atau banyak baris dan banyak kolom (misal A1:E10).
  2. row_num (Wajib atau Optional) adalah kriteria pencarian berdasarkan baris data. Dengan menggunakan row_num, Anda bisa menentukan akan mengambil nilai pada baris ke berapa dari sumber data.
    • Jika argument array berisi 1 baris dan banya kolom, maka argument row_num bersifat optional.
    • Jika argument array berisi banyak baris dan 1 kolom, maka argument row_num bersifat Wajib.
  3. column_num (Wajib atau Optional) adalah kriteria pencarian berdasarkan kolom data. Dengan menggunakan column_num, Anda bisa menentukan akan mengambil nilai pada kolom ke berapa dari sumber data. Sama seperti argument row_num, Argument column_num bisa bersifat Wajib hanya jika Range Data terdiri lebih dari 1 kolom.

Dengan kata lain, argument row_num dan column_num wajib Anda gunakan tergantung dari seperti apa Array (range data) yang menjadi sumber pencarian.

Note: argument row_num dan column_num harus merujuk ke sebuah cell di dalam array (Range Data). Jika tidak, maka Excel akan memberikan hasil pesan kesalahan #REF!

Aturan Penulisan Rumus INDEX Reference

Tulis Fungsi / Rumus INDEX Reference:
=INDEX(reference,row_num,[column_num],[area_num])

Artinya:
Ambil Data dari salah satu reference yang terdapat pada row_num dan column_num pada reference ke area_num

Terdapat 4 argument fungsi INDEX Reference yang memiliki arti sebagai berikut:

  1. reference (Wajib). Sebelumnya, Array hanya terdiri dari 1 range data. Sedangkan argument reference bisa disebut gabungan dari beberapa range data (gabungan dari beberapa array). Jika Anda ingin mencari data dari 2 atau lebih range data yang berjauhan atau 2 tabel berbeda, maka gunakan Rumus INDEX Reference ini.
    • Jika Anda menggunakan lebih dari 1 range data, maka tulis argument reference didalam kurung dan pisahkan dengan operator pemisah rumus ( , atau ; ). Misalnya =INDEX((A1:B2,E1:F8),row_num,[column_num],[area_num])
    • Jika salah satu range data pada argument reference terdiri dari 1 kolom atau baris, maka argument row_num atau column_num bersifat optional untuk mencari dan mengambil data dari range tersebut.
  2. row_num (Wajib atau Optional). Jika tujuan Anda mencari dan mengambil data dari salah satu Range pada argument reference yang terdiri dari 1 baris, maka row_num bersifat optional.
  3. column_num (Wajib atau Optional) Jika tujuan Anda mencari dan mengambil data dari salah satu Range pada argument reference yang terdiri dari 1 kolom, maka column_num bersifat optional.
  4. area_num (Optional) akan menentukan apakah Excel mengambil data dari Range Pertama atau Range Kedua dst… Argument ini bisa Anda isi dengan angka 1 atau 2 dst.. tergantung banyak Range Data pada argument reference.

Note: Jika area_num tidak diisi (kosong) sedangkan Anda menggunakan lebih dari 1 Range Data (argument reference), maka Excel hanya akan mengambil nilai dari Range Data Pertama dan mengabaikan Range Data kedua dan seterusnya…

Biar semakin paham cara menggunakan Rumus INDEX Excel baik untuk Array maupun Reference, silahkan pahami baik-baik beberapa contoh berikut:

#1 Contoh Cara Menggunakan Rumus INDEX Array

Contoh sederhana ini mengajarkan Anda cara menggunakan dasar-dasar Rumus INDEX Array dengan menggunakan argument row_num dan column_num. Misalnya, Saya memiliki tabel Gaji Pokok dan Tunjangan Karyawan seperti gambar berikut:

Cara menggunakan Rumus / Fungsi INDEX Excel - 7 Contoh lengkap + Tips

Penting! Tabel dalam contoh ini tidak menggunakan Format Tabel Bernama ataupun Named Range. Melainkan hanya menggunakan Border biasa. Apa itu Format Tabel Bernama ? Silahkan Kunjungi Panduan Tabel Bernama Excel. Apa itu Named Range ? Silahkan kunjungi Panduan Named Range Excel. Apa itu Border ? Silahkan kunjungi Panduan Border Excel.

Waktu yang dibutuhkan: 1 menit.

Dalam contoh ini, Saya sengaja meletakkan tabel data mulai dari Cell C3 untuk melihat bagaimana argument Array bekerja. Kemudian, Saya ingin mengambil Gaji Pokok Karyawan yang bernama Budi (Rp 5.000.000 atau Cell D7) dan menampilkannya pada Cell C12. Untuk tujuan tersebut, silahkan ikuti cara menggunakan Rumus INDEX Array berikut:

  1. Masukkan Fungsi INDEX

    Pada Cell C12, masukkan Fungsi INDEX seperti berikut: =INDEX(
    Cara menggunakan Rumus / Fungsi INDEX Excel

  2. Tentukan Argument Array

    Karena Sumber Data hanya ada 1, maka kita menggunakan INDEX Array. Untuk argument array, silahkan pilih (blok) Range Data. Dalam contoh ini, Range C3:E10 kemudian ketik operator pemisah rumus ( , atau ; )
    Cara menggunakan Rumus / Fungsi INDEX Excel

  3. Tentukan Argument row_num

    Perhatikan, argument array dimulai dari Cell C3. Artinya, ROW 3 = Baris pertama dalam Array (Range Data). Selanjutnya tentukan, Anda ingin mengambil data pada baris ke berapa dari Range Data (array). Karena Data Karyawan “Budi” berada pada baris ke lima dalam Range Data, maka ketik 5 sebagai argument row_num. Kemudian ketik operator pemisah rumus ( , atau ; )
    Cara menggunakan Rumus / Fungsi INDEX Excel

  4. Tentukan Argument column_num

    Sama seperti row_num, Column C = Kolom Pertama dalam Array (Range Data). Dalam contoh ini, Gaji Pokok berada pada kolom ke dua. Maka ketik 2 untuk argument column_num, kemudian ketik tanda tutup kurung dan tekan Enter.
    Cara menggunakan Rumus / Fungsi INDEX Excel

Note: Pastikan Anda memahami cara memasukkan Fungsi INDEX Array ini karena tidak akan Saya ulangi pada Contoh terkait berikutnya.

Contoh cara menggunakan Rumus / Fungsi INDEX Array Excel

Secara otomatis Excel mengambil nilai dari cell baris ke 5 dan kolom ke 2 pada Range Data yaitu Cell D7.

Apa Makna Fungsi INDEX tersebut ?

Jadi secara garis besar, berikut Rumus INDEX yang digunakan:

Tulis Fungsi / Rumus INDEX Array:
=INDEX(C3:E10,5,2)

Artinya:
Ambil Data dari Cell C3 sampai Cell E10 yang terdapat pada Baris Ke 5 dan Kolom ke 2 dari Range Data

Dengan kata lain, Rumus INDEX akan mengambil nilai dari Cell C3 sampai E10 yang terdapat pada baris ke 5 dan kolom ke 2 dari Range Data yang mana Row 3 menjadi baris pertama dan Kolom C menjadi kolom Pertama.

Lalu apakah rumus tersebut mengambil data yang benar ? Karena dalam kolom Gaji Pokok, ada 2 cell yang berisi angka Rp 5.000.000 (Cell D5 dan D7). Saya sempat berpikir bisa jadi Rumus INDEX mengambil nilai pada Cell D5.

Untuk itu, coba kita buktikan…

Silahkan ubah nilai pada Cell D7, misalnya 10.000.000. Dan sudah pasti hasil Rumus INDEX Array ikut berubah…

Pembuktian nilai ikut berubah

Tentang Argument Array

Sebelumnya Saya menggunakan Range C3:E10 sebagai argument array yang mana juga termasuk judul kolom pada tabel. Dalam keadaan lain, Anda juga bisa menggunakan Range C4:E10 (tanpa judul kolom).

Jika ini yang Anda lakukan, maka ROW 4 akan menjadi baris pertama. Sehingga untuk mengambil nilai pada Cell D7, Anda perlu menyesuaikan argument row_num menjadi =INDEX(C3:E10,4,2) karena Cell D7 (saat ini) berada pada baris ke 4 dari Range Data.

Argument Array

Benar bukan ? Sejauh ini Saya harap Anda bisa memahami penggunaan paling dasar dari Rumus INDEX Array. Selanjutnya, Saya berikan contoh-contoh yang paling sering ditemui ketika menggunakan Rumus INDEX Excel

#2 Contoh Rumus INDEX Tanpa row_num

Jika Anda tidak ingin menggunakan argument row_num, maka Anda bisa mengosongkan argument tersebut atau mengisinya dengan angka 0. Namun perlu berhati-hati. Excel bisa saja memberikan hasil berupa pesan error #REF! atau #VALUE!. Penggunaan ini akan tergantung seperti apa range data Anda apakah terdiri dari 1 baris atau lebih.

Contoh Untuk Range Data 1 Baris

Misalnya, Saya ingin mengambil data kolom ke 3 dari Range C3:E3. Ingat! Range C3:E3 hanya terdiri dari 1 baris yaitu Row 3. Jika kondisi seperti ini, Anda bisa mengosongkan argument row_num atau bisa mengisinya dengan angka 0 atau angka 1.

Contoh Rumus INDEX Array Excel untuk Range Data 1 baris

Cell C13 =INDEX(C3:E3,,3) dan D13 =INDEX(C3:E3,0,3) dan E13 =INDEX(C3:E3,1,3) memberikan contoh penggunaan yang benar. Anda bisa mengosongkan argument row_num (seperti Cell C13) atau mengisinya dengan angka 0 atau 1 (seperti Cell D13 dan E13)

Note: Ingat ya… Cara ini hanya berlaku jika Range Data terdiri dari 1 baris

Berhubung Range Data pada Contoh ini hanya ada 1 baris. jika Anda mengisi argument row_num dengan angka yang lebih dari 1, maka Excel akan memberikan hasil berupa pesan error #REF!. Hasil ini yang Anda lihat pada Cell F13 dengan Rumus =INDEX(C3:E3,2,3)

Contoh Untuk Range Data Lebih Dari 1 Baris

Seperti Contoh Pertama dalam panduan ini (Baca Lagi), Saya menggunakan Range C3:E10 yang mana terdiri lebih dari 1 baris (Row 3, 4, 5, 6, 7, 8, 9, dan 10). Jika Range Data Anda seperti ini (lebih dari 1 baris) dan tidak ingin menggunakan Argument row_num, ada 2 trik yang bisa Anda gunakan.

Trik Pertama: Jika Hasil ditampilkan pada Baris yang berbeda dari Range Data

Dari semua contoh di atas, Saya menampilkan hasil Rumus INDEX tepat di bawah tabel. Artinya Tidak Satu Baris dengan Range Data (C3:E11). Gunakan trik ini jika kondisi data Anda sama dengan contoh ini. Jika tidak, silahkan skip ke Trik Kedua

Contoh Rumus / Fungsi INDEX Array Excel untuk Range Data Lebih dari 1 baris dan kolom

Seperti yang Anda lihat, ketika Hasil Rumus INDEX ditampilkan pada Cell yang tidak satu baris dengan Range Data, kemudian Saya mengosongkan argument row_num (Cell C14) atau mengisinya dengan angka 0 (Cell C14), maka Excel akan memberikan hasil #VALUE!

Jika Anda mengalami kondisi seperti ini, solusinya setelah memasukkan Rumus =INDEX(C3:E10,,3) atau =INDEX(C3:E10,0,3), Jangan Tekan Enter. Tapi tekan tombol CTRL+SHIFT+ENTER pada keyboard secara bersamaan. Hasil yang benar bisa Anda lihat pada Cell C15 dan D15.

CTRL+SHIFT+ENTER untuk menambahkan kurung kurawal pada Fungsi

Note: Setelah menekan CTRL+SHIFT+ENTER, maka Excel secara otomatis mengapit rumus INDEX dengan kurung kurawal menjadi {=INDEX(C3:E10,,3)} atau {=INDEX(C3:E10,0,3)}. Jangan ketik kurung kurawal {…} secara manual.

Trik Kedua: Jika Hasil ditampilkan pada Baris yang Sama dengan Range Data

Maksud Saya seperti ini… Range Data adalah C3:E10 artinya Row 3 sampai 10 adalah Baris untuk Range Data ini. Ketika Saya menampilkan hasil Rumus INDEX pada kolom yang berada dalam baris tersebut, maka Excel akan mengambil data pada baris yang sama dengan Range Data.

Ditambah lagi, Anda tidak perlu menggunakan CTRL+SHIFT+ENTER untuk mengapit rumus dengan kurung kurawal {…}.

Solusi Rumus / Fungsi INDEX Excel yang Error

Seperti Contoh diatas, Cell H4 =INDEX(C3:E10,,3) dan Cell I4 =INDEX(C3:E10,0,3). Tidak ada penggunaan kurung kurawal, dan Excel mengambil nilai pada baris yang sama (Row 4) dan Kolom ke 3 dari Range Data (lihat argument column_num pada rumusnya). Hasil yang tepat juga tampak pada Cell H6 dan I6.

Mengatasi Error

Namun Excel akan memberikan hasil #VALUE! jika Anda menggunakan Rumus pada cell selain row yang sama dengan Range Data (Lihat hasil pada cell H1, I1, H11 dan I11).

Bagaimana, mudah bukan memahami fungsi INDEX Excel ini ? Selanjutnya kita akan masuk ke contoh tanpa column_num.

#3 Contoh Rumus INDEX Tanpa column_num

Konsepnya hampir sama dengan Contoh Tanpa row_num. Ketika Anda menggunakan argument row_num, maka argument column_num bersifat optional (boleh digunakan / tidak).

Contoh Untuk Range Data 1 Kolom

Misalnya untuk Range C3:C10. Perhatikan! hanya ada 1 kolom yaitu kolom C (Nama Karyawan). Untuk keadaan seperti ini, Anda bebas menampilkan hasil rumus INDEX pada Cell manapun baik itu di bawah tabel ataupun di samping (Sama Seperti tanpa row_num untuk Range Data 1 Baris).

Contoh Rumus / Fungsi INDEX Excel tanpa argument column_num

Cell C16 =INDEX(C3:C10,2,) dan Cell D16 =INDEX(C3:C10,2,0). Dimana pun Anda menampilkan hasil rumus INDEX untuk Range Data 1 Kolom, Excel tetap akan memberikan hasil yang sama.

Contoh Untuk Range Data Lebih Dari 1 Kolom

Sama seperti Contoh tanpa row_num untuk Range Data lebih dari 1 baris. Saya juga punya 2 trik. Anda hanya perlu memperhatikan “Rumus INDEX akan digunakan pada Cell mana ?”. Apakah dalam 1 kolom yang sama atau 1 baris yang sama dengan Range Data.

Berhubung argument ini khusus untuk kolom (column_num), jadi ketika Anda menggunakan =INDEX(C3:E10,2,) atau =INDEX(C3:E10,2,0) di baris manapun pada kolom yang sama dengan Range data (Kolom C, D atau E), Excel akan memberikan hasil dari baris pada kolom yang sama.

Tanpa argument column_num

Namun jika menggunakannya pada kolom yang berbeda, silahkan gunakan kurung kurawal {…} dengan cara tekan CTRL+SHIFT+ENTER.

Rumus / Fungsi INDEX Excel Tanpa argument column_num

Pahami Aturan Penulisan Rumus INDEX terlebih dahulu Jika Tidak Ingin Terjadi Kesalahan

Rolan Mardani

#4 Kesalahan row_num dan column_num yang Sering Terjadi

Beberapa contoh di atas sudah membahas pesan kesalahan apa saja yang akan diberikan Excel jika terjadi kesalahan dalam penulisan rumus.

Secara Default, Excel hanya akan memberikan pesan Error #VALUE! dan #REF! jika terjadi kesalahan penulisan rumus INDEX. Jika Anda menemukan pesan Error lain, kemungkinan besar terjadi karena pada Range Data terdapat Pesan Error tersebut.

Penyebab Rumus / Fungsi INDEX Excel menjadi Error (salah)

Seperti hasil Rumus INDEX pada Cell C12, D12 dan E12. Sebetulnya tidak ada kesalahan pada Rumus INDEX, namun kesalahan terjadi pada Range Data yang mana Cell C5, D5 dan E5 berisi pesan error Excel (Cell Kosong, #DIV/0! dan #NULL!).

Hal ini menunjukkan bahwa Fungsi INDEX betul-betul hanya mengambil nilai yang tertera pada Cell dari sumber data. Jika ini terjadi pada data Anda, silahkan cek Range Data yang menjadi sumber. Apakah terdapat Cell yang berisi pesan Error atau tidak.

Penting! Jika terdapat cell yang berisi pesan error dari Range Data (sumber), silahkan perbaiki terlebih dahulu. Caranya silahkan kunjungi Panduan Cara Mengatasi Error Excel.

#5 Contoh Cara Menggunakan Rumus INDEX Reference

Yang membedakan jenis Fungsi INDEX Array dan Reference adalah argument array vs reference dan argument tambahan khusus INDEX Reference yaitu argument [area_num].

Seperti yang Saya jelaskan pada bagian Aturan Penulisan Rumus INDEX Reference, Anda bisa menggunakan lebih dari 1 range data. Bisa 2, 3, 4 atau 5 Range Data yang tidak berdekatan (Lupa? Klik disini untuk baca lagi).

Kemudian menggunakan argument [area_num] akan menentukan Apakah Excel melakukan pencarian dan mengambil nilai cell pada Range Data 1 atau Range Data 2 dan seterusnya…

Contoh Soal

Sebagai contoh, Saya memiliki 2 tabel berbeda pada sheet yang sama. Dengan Fungsi INDEX Reference, Anda bisa memberikan perintah kepada Excel untuk mencari data dari Tabel pertama atau tabel kedua..

Saya ingin mengambil data pada Baris ke 4 dan Kolom ke 2 pada Kedua tabel. Untuk tabel pertama, hasilnya akan Saya tampilkan pada Cell D13. Dan untuk tabel kedua, hasilnya akan Saya tampilkan pada Cell E13. Untuk tujuan tersebut, silahkan ikuti cara menggunakan Rumus INDEX Reference berikut:

Cara menggunakan Rumus / Fungsi INDEX Reference Excel untuk mencari dan mengambil data
  1. Pertama, Ketik Fungsi INDEX pada Cell D13 seperti berikut =INDEX(
  2. Kedua, berhubung range Data (sumber) ada 2, maka masukkan kedua Range Data didalam tanda kurung sehingga Fungsi menjadi =INDEX((C3:E10,G3:H10)
  3. Ketiga, masukkan argument row_num dan [column_num] sesuai tujuan sehingga rumus menjadi =INDEX((C3:E10,G3:H10),4,2,
  4. Keempat, masukkan angka 1 untuk argument [area_num] pada Cell D13 dan jangan lupa tutup kurung.
  5. Kelima, untuk rumus INDEX pada Cell E13, ulangi masukkan fungsi INDEX Referensi pada tahap 1 sampai 3. Untuk argument [area_num] gunakan angka 2 untuk mengambil data pada Range Data ke 2. Dan lihat hasilnya.
Cara menggunakan Rumus / Fungsi INDEX Reference Excel untuk mencari dan mengambil data

Benar bukan ? argument [area_num] akan menentukan apakah Excel akan mengambil data dari Range Data Pertama atau Kedua (Cell D13 atau E13). Yang terpenting, jangan lupa masukkan argument reference kedalam kurung ( … ) khusus dan tentukan argument [area_num].

Bagaimana Jika Tidak Menggunakan Argument area_num ?

Tanpa argument area num

Lihat pada gambar hasil rumus INDEX Reference di atas, untuk Cell D14, Saya menggunakan Rumus INDEX Reference tanpa argument [area_num] yaitu =INDEX((C3:E10,G3:H10),4,2). Hasilnya sama dengan Cell D13 (Rp 4.250.000). Apa artinya ?

Jadi jika Anda menggunakan lebih dari 1 Range Data pada argument Reference namun tidak menentukan argument [area_num], maka Excel hanya akan mengambil nilai dari range Data Pertama dan mengabaikan Range Data Kedua dan seterusnya.

#6 Contoh Rumus INDEX Jika Menggunakan Format Tabel Bernama dan Named Range

Dalam keadaan tertentu, bisa saja Anda membutuhkan (harus) menggabungkan banyak Fungsi ke dalam rumus termasuk Fungsi INDEX. Namun untuk kesederhanaan Rumus, Saya lebih merekomendasikan untuk menggunakan Format Tabel Bernama. Jika tidak mungkin menggunakan Format Tabel Bernama, cobalah gunakan Named range.

Lalu apa keuntungannya ?

Yang paling utama, Rumus akan terlihat lebih terstruktur dan sederhana (mudah dibaca) apa lagi jika Anda sudah terbiasa. Mungkin Anda pernah melihat argument array atau reference pada Rumus INDEX diisi dengan teks tertentu.

Misalnya rumus =INDEX(data_gaji,6,1) atau =INDEX((data_gaji,data_tunjangan),6,1) atau mungkin pernah menemukannya pada Rumus IF seperti =IF(GAJI>=2000000,”Diatas UMR”,”Dibawah UMR”).

Argument berupa “data_gaji”, “data_tunjangan” dan “GAJI” sebetulnya sudah dibuat menggunakan fitur Tabel Bernama Excel atau Named Range. Selengkapnya tentang fitur Rumus Terstruktur, silahkan kunjungi Panduan Rumus Terstruktur Excel M Jurnal

Apakah ada Perbedaan Penggunaan Rumus INDEX untuk Format Tabel Bernama atau Named Range ?

Sejauh yang Saya coba, untuk Named Range Tidak ada perbedaan. Sementara untuk Format Table Bernama, terdapat perbedaan untuk menentukan baris pertama dari Data Sumber dan selanjutnya akan berdampak pada argument row_num.

Dalam Tabel Bernama, Baris Pertama Dari Tabel Sumber adalah Item Pertama dari Tabel (bukan Judul / Header Kolom). Hanya itu perbedaannya.

Contoh Rumus / Fungsi INDEX Excel untuk format tabel bernama dan named range

Seperti yang Anda lihat, Cell C13 berisi Rumus =INDEX(Gaji,3,1) dan Excel memberikan hasil Rolan yaitu data pada Cell C6 yang mana data tersebut adalah list ke 3 dari Tabel.

Berbeda jika Anda menggunakan C3:C10 atau named Range sebagai argument array atau Reference. Row 3 (judul kolom) akan dianggap sebagai Baris Pertama dari Range Data Sumber. Tentu jika ingin mengambil Nama Karyawan “Rolan”, Rumus akan berubah menjadi =INDEX(Gaji,4,1).

#7 Contoh Cara Menggunakan Rumus INDEX Beda Sheet

Menggunakan Rumus INDEX untuk Beda Sheet mungkin akan gampang-gampang susah. Terutama jika Anda tidak menggunakan Tabel Bernama ataupun Named Range. Namun meskipun Anda menggunakan Format Tabel Bernama ataupun Named Range, Anda hanya bisa menggunakan Rumus INDEX tipe Array. Biar semakin paham, coba perhatikan contoh berikut.

Jika Menggunakan Referensi Biasa

Sebagai contoh, Range Data Sumber berada pada Sheet ‘INDEX Reference’ (ada 2 Range Data). Kemudian, Saya menggunakan Rumus INDEX pada Sheet baru dengan nama ‘Hasil INDEX Beda Sheet’.

cara menggunakan rumus / fungsi INDEX Excel beda sheet

Penting! Untuk memudahkan pekerjaan, Anda bisa membuka banyak Sheet dalam 1 File Excel secara bersamaan. Silahkan kunjungi Panduan Cara Melihat Banyak Sheet Secara Bersamaan

Saya menggunakan Rumus INDEX tipe Array pada Sheet “Hasil INDEX Beda Sheet” – Cell A2 dan B2 dengan Rumus masing-masing =INDEX(‘INDEX Reference’!C3:E10,4,1) dan =INDEX(‘INDEX Reference’!C3:E10,4,2). Dan ternyata, Excel memberikan hasil yang tepat.

Note: Argument array berisi ‘INDEX Reference’!C3:E10 yang artinya Range C3:E10 yang terdapat pada Sheet INDEX Reference. Ini bentuk rumus INDEX Beda Sheet jika Anda tidak menggunakan Tabel Bernama ataupun Named Range. Argument ‘INDEX Reference’!C3:E10 akan berubah sesuai dengan Nama Sheet dan Range Data Anda.

Sementara untuk Cell C2, Saya menggunakan Rumus INDEX tipe Reference dengan Rumus =INDEX((‘INDEX Reference’!C3:E10,‘INDEX Reference’!G3:H10),4,2,2) untuk mengambil nilai pada Range Data kedua.

cara menggunakan rumus / fungsi INDEX Excel beda sheet

Sejauh ini Excel memberikan hasil yang tepat sesuai tujuan Saya. Namun menurut Saya hanya 1 kekurangannya: Rumus Menjadi terlalu Panjang (kurang sederhana) yang terkadang menjadi sulit di ekstrak dalam otak Saya.

Jika Menggunakan Format Tabel Bernama atau Named Range

Note: Dalam contoh ini, Saya sudah membuat 2 Named Range (NR_Gaji dan Status_Perkawinan) serta 2 Tabel Bernama (Gaji dan Tabel_Status)

Kemudian Saya coba menggunakan Rumus INDEX tipe Array untuk beda Sheet. Cell A3 adalah contoh untuk Named Range menggunakan Rumus =INDEX(Status_Perkawinan,4,1).

Sedangkan Cell B3 dan C3 contoh untuk Format Tabel Bernama, masing-masing menggunakan Rumus =INDEX(Gaji,3,2) dan =INDEX(Tabel_Status,3,2).

Cara menggunakan rumus / Fungsi INDEX Excel Beda Sheet untuk tabel bernama dan named Range

Seperti yang Anda lihat, Rumus INDEX Excel memberikan hasil yang tepat sesuai tujuan Saya. Namun Saya menemukan kendala ketika menggunakan Rumus INDEX tipe Reference.

Contohnya, seperti hasil pada Cell A4 dengan Rumus =INDEX((Gaji,Status_Perkawinan),3,1,1) yang menggunakan sumber berupa Tabel Bernama (Gaji) dan Named Range (Status_Perkawinan).

Cell B4 menggunakan Rumus =INDEX(NR_Gaji,Status_Perkawinan,4,2,1) yang kedua sumber data berupa Named Range (NR_Gaji dan Status_Perkawinan).

Sementara Cell C4 menggunakan Rumus =INDEX(Gaji,Tabel_Status,3,2,2) yang kedua sumber data berupa Tabel Bernama (Gaji dan Tabel_Status). Seperti hasil pada gambar berikut:

Rumus / Fungsi INDEX Excel Beda Sheet menjadi Error (salah) #VALUE!

Seperti yang Anda lihat, Excel memberikan hasil berupa pesan error #VALUE! untuk semua Rumus INDEX.

Bagaimana, sejauh ini Saya harap Anda sudah paham seluk-beluk penggunaan Rumus INDEX Excel. Kita hampir masuk ke tahap akhir Panduan ini.

Fakta: Tentang Rumus INDEX Excel

Sesuai judul bagian ini “Fakta”, Saya akan menyampaikan Fakta-Fakta yang Saya temukan dalam menggunakan Fungsi INDEX di Excel sebagai berikut:

  1. Rumus INDEX secara default hanya mengambil nilai saja. Jika Cell dari Range Data Sumber berupa sebuah Rumus, maka Excel hanya mengambil hasilnya saja.
  2. Rumus INDEX juga tidak mengambil Format. Misalnya Cell yang menggunakan Format Cell (Warna, Font, Border dll), Conditional Formatting (misal Data Bars atau Color Scales dll), dan Data Validations (misal, Drop Down List) seperti yang bisa Anda lakukan menggunakan fitur Copy Paste Special
  3. Jika ingin menggunakan Rumus INDEX untuk beda Sheet dengan Sumber Data berupa Named Range atau Tabel Bernama, maka Anda hanya bisa menggunakan Rumus INDEX tipe Array. Sementara untuk Sumber Data berupa Referensi biasa (Misal C3:E10), Anda bisa menggunakan kedua jenis Rumus INDEX (Array dan Reference)
  4. Fungsi INDEX akan lebih maksimal jika digabungkan dengan fungsi lain. Misalnya INDEX + MATCH atau IFERROR + IF + INDEX + MATCH atau terserah, tergantung tujuan Anda. Bahkan gabungan INDEX + MATCH saja bisa menjadi alternatif fungsi VLOOKUP dan HLOOKUP untuk pencarian tingkat lanjut.

Note: Jika permasalahan Anda belum terjawab dari Panduan ini, silahkan lihat Panduan Lainnya dari Pintasan Panduan Lookup & Reference Excel berikut. Atau kunjungi Pusat Panduan Excel M Jurnal.

Pintasan Panduan Lookup & Reference Excel

  1. Lookup & Reference: Dasar-dasar Fungsi Pencarian dan Referensi di Excel (VLOOKUP, HLOOKUP, MATCH, INDEX dan CHOSE)
  2. VLOOKUP: Cara menggunakan Fungsi VLOOKUP (lengkap)
  3. Tax Rates: Contoh kasus menghitung Pajak Penghasilan (PPH) menggunakan Fungsi VLOOKUP.
  4. INDEX: (Anda Disini).
  5. MATCH: Cara menggunakan Fungsi MATCH Excel untuk mencari posisi item tertentu dalam Range Data, apakah berada pada kolom atau baris keberapa dari Range Data. Kriteria pencarian dapat ditentukan sendiri.
  6. INDIRECT: Cara menggunakan Fungsi INDIRECT. Anda pasti menemukan inspirasi dari fungsi ini.
  7. INDEX + MATCH: Gabungan Fungsi INDEX dan MATCH Excel sebagai alternatif fungsi VLOOKUP. Ikuti panduan ini jika Anda ingin fungsi pencarian tingkat lanjut diatas VLOOKUP
  8. Two-way Lookup: Mencari data tertentu berdasarkan 2 atau lebih kriteria. Seperti membuat form pencarian dengan 2 atau lebih kriteria.
  9. OFFSET: Cara menggunakan Fungsi OFFSET untuk mencari data berdasarkan lokasi (cell / sheet dll)
  10. Case-sensitive Lookup: Cara menggunakan Fungsi INDEX, MATCH, dan EXACT untuk mencari data (case-sensitive)
  11. Left Lookup: Cara mencari data ke arah kiri dari sumber. Contoh ini tidak bisa dilakukan menggunakan Fungsi VLOOKUP.
  12. Locate Maximum Value: Cara mengetahui nilai maximum pada data yang banyak. Anda juga bisa mengetahui pada cell mana nilai maximum tersebut berada.
  13. Two-column Lookup: Cara mencari data berdasarkan 2 kriteria berbeda.
  14. Closest Match: Kombinasi Fungsi INDEX, MACTH, MIN dan ABS untuk mencari data yang paling mendekati kriteria Anda.
  15. Compare Two Columns: Membandingkan 2 kolom. Dapat digunakan untuk menemukan data duplikat dan unik.
  16. XLOOKUP: Jika Anda pengguna Office 365, Lebih baik menggunakan Fungsi XLOOKUP daripada VLOOKUP

Komentar Paling Lambat di Balas Pukul 23:59.